Access 2007, VBA code for combo box

I'm assuming that the table behind your form has four fields: Name, DateIn, DateOut, and CardNumber. The question is: what is the relationship between that table and tbl_AccessCards. Either the link is directly on a readable field (the card number itself), or on a hidden ID identity column. This determines what you want to write into your first table.

A combo box can be bound to a field from the table; in that case no coding is required. The combo box mechanism will write the content of the “bound column” into the bound field, even if that column isn't exposed to the user, and even if the combo box displays additional information from tbl_AccessCards.

For more detailed instructions, please show us the structure of both tables involved, and indicate how their relationship has been set up.

(°v°)

I am totally going to get back to this. I have to work on another issue at the moment, but after that I will be returning to this question.

Okay. I am back!

Addressing the question of the relationship:  Right now I am using a combo box that is populated by items in a query field. The query is run on a table titled "tbl_AccessCards" and the query is used to pull out only the ones with the type field listed as "visitor."

From the query,  I create a second table titled "tbl_Checkout" which sees the most action. In this table I set up the combo box that is populated by items in the qry field "EmployeeID."

So, in short, the process goes tbl_AccessCards > Qry_Visitor_Cards > tbl_Checkout > Form > tbl_Checkout_archive (which we haven't discussed yet, but just fyi).

I would very much like to just populate that combo box using vba code, but I don't know how to do it.

Also, I don't know what to do when the tbl_checkout is empty. When there is nothing in that table, the form reports an error about the null fields. I get this, but I don't understand how to work around it, or if it is possible to work around it.

Anyway, just in case you need it, here is the code for the form I am working on now:

Option Compare Database
Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ConfirmCancel As Integer



Private Sub FillData()
 On Error GoTo ErrorHandler
   
    
    txtName.SetFocus
    txtName.Text = rs.Fields.Item("VisitorName")
    

    
    txtDateOut.SetFocus
    txtDateOut.Text = rs.Fields.Item("ckDateOut")




    cmbVNumber.SetFocus
    cmbVNumber.Text = rs.Fields.Item("ckCardNum")



    txtInitials.SetFocus
    txtInitials.Text = rs.Fields.Item("Initials")




    txtNotes.SetFocus
    txtNotes.Text = rs.Fields.Item("Notes")



    
    
    Exit Sub
    
ErrorHandler:
    MsgBox "Please contact Megin Garrett with the following information" & vbCrLf & "Error Description:" & Err.Description & vbCrLf & "Error Number:" & Err.Number, vbCritical, "Error"
    Resume Next
     
    
    
End Sub

Private Sub btnCancel_Click()
    ConfirmCancel = MsgBox("Are you sure you want to cancel?", vbYesNo)
    If ConfirmCancel = 6 Then
    
DoCmd.CancelEvent
    
    btnNew.Visible = True
    btnNext.Visible = True
    btnPrevious.Visible = True
'    btnCancel.Visible = False
    btnInsert.Visible = False
    btnClose.Visible = True
    btnCheckIn.Visible = True
    DoCmd.CancelEvent
    
    Else
        MsgBox "Continue"
    End If
    
    
    
    
End Sub

Private Sub btnClose_Click()
    DoCmd.Close
End Sub

Private Sub btnInsert_Click()
    txtName.SetFocus
    btnPrevious.Visible = True
    btnNext.Visible = True
    btnNew.Visible = True
    btnInsert.Visible = False
    btnCancel.Visible = False
    btnClose.Visible = True
    btnCheckIn.Visible = True
    
    
    rs.AddNew
    UpdateRecords
    
    
    
End Sub

Private Sub btnNew_Click()
    Dim today
    today = Now
    
    Dim ctl As Control
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Then
            ctl.SetFocus
            ctl.Text = ""
        End If
    Next ctl
    cmbVNumber.SetFocus
    
        
    
'    txtName.SetFocus
'    txtName.Text = ""
    txtDateOut.SetFocus
    txtDateOut.Text = today
'    cmbVNumber.SetFocus
'    cmbVNumber.Text = ""
'    txtInitials.SetFocus
'    txtInitials.Text = ""
'    txtNotes.SetFocus
'    txtNotes.Text = "Enter Notes Here"
'    cmbVNumber.SetFocus
    

    
    btnPrevious.Visible = False
    btnNext.Visible = False
    btnInsert.Visible = True
    btnNew.Visible = False
    btnCancel.Visible = True
    btnClose.Visible = False
    btnCheckIn.Visible = False
    
    
    
    
        
End Sub

Private Sub btnNext_Click()
    UpdateRecords
    rs.MoveNext
    If rs.EOF Then
        rs.MoveLast
    End If
    
FillData

End Sub

Private Sub btnPrevious_Click()
    UpdateRecords
    rs.MovePrevious
        If rs.BOF Then
            rs.MoveFirst
        End If
    
    FillData
        
End Sub

Private Sub Form_Load()
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    rs.Open "tbl_Checkout", cn, adOpenDynamic, adLockOptimistic, adCmdTable
    
    FillData
    
End Sub

Private Sub UpdateRecords()
    txtName.SetFocus
    rs.Fields.Item("VisitorName") = txtName.Text
    txtDateOut.SetFocus
    rs.Fields.Item("ckDateOut") = txtDateOut

    cmbVNumber.SetFocus
    rs.Fields.Item("ckCardNum") = cmbVNumber
    txtInitials.SetFocus
    rs.Fields.Item("Initials") = txtInitials
    txtNotes.SetFocus
    rs.Fields.Item("Notes") = txtNotes
    
    
    
    rs.Update
    
    
    
    
    
End Sub

Select allOpen in new window

The type of form you are building is called an “unbound edit form” or just “unbound form”. It has no record source, so navigation, reading, updating, inserting, and deleting need to be programmed from scratch. In the code, there is no clue as to why you would need an unbound form, but you must have your reasons.

If I understand, cmbVNumber is a combo box, but I'm not sure what your issue is. If you want it to display the list of visitor cards, assuming the fields CardN° and Visitor (yes/no), you can use a query similar to this as row source:

    ' In FillData:
    cmbVNumber = rs("ckCardNum")
    ' In UpdateRecords
    rs("ckCardNum") = cmbVNumber

Select allOpen in new window

How far did you get, and where did you get stuck?
(°v°)

The type of form you are building is called an “unbound edit form” or just “unbound form”. It has no record source, so navigation, reading, updating, inserting, and deleting need to be programmed from scratch. In the code, there is no clue as to why you would need an unbound form, but you must have your reasons.

The only reason I have for this is that this is how I was taught by the video tutorial I just took online. I am trying to learn how to write vba code, so I wanted to create this form using vba code and by not relying on Access's shortcuts as much. So, I guess I did want to do it all from scratch, but I am not aware of other ways to do this.

I just tried the code you advised and it worked, but only for the first record. So, my combo box only ends up with one item in it, rather than all of the items in the field. I have not added in the query yet. I am just trying to get the combo box to populate with more than one item from the field.

Does that make sense? I am pretty new to this.

Ah, so this is basically an exercise. If if weren't I would suggest you use a simple bound form, even one created by an assistant, instead of trying to reinvent the wheel. It makes sense, though, so you see all the operations involved in a simple data entry form.

I'm not sure I understand the “more than one item from the field” bit. Are you saying this is a multi-value field? If so, I'd advise against those. If you are “new to this”, you will not be able to control and use them properly; once you know more you'll realise there are better ways to link multiple record to a master record..

Can you tell me more about the field, its type, its typical content, and what the source of the correlated combo box should be?

(°v°)

Maybe this will help:

I have a table called "AccessCards". There are several different fields, one of which is called "EmployeeID". "EmployeeID" is a text field. Lets say that the following items are in the table, under the field "EmployeeID": V1, V2, V3, V4, and V5.

In my form, I have a combo box. When I click on the little arrow, I want the items that show up in the combo box (the little drop down list) to be: V1, V2, V3, V4, and V5.

There are some other things I want to do after that, but I think that it is best to just start here. I am sure it is something simple. I just don't know it.

Thank you!!!!

It will be for display only, then. Try something like this to see if we are on the right track.

1) change the combo box “row source type” to “value list” and then:

    ' In FillData:
    cmbVNumber.RowSource = rs("ckCardNum")

Select allOpen in new window

(°v°)

Now it is letting me add items to the combo box, but still not adding  the numbers from the list.

I am just going to attach the item here, since there is no personal information information in it.

The forms I am wanting to load with these numbers, via vba code, are the frm_CheckOutLog and frm_CheckIn.

I hope this makes more sense seeing the actual item.
Access-Cards1.accdb

Okay! I figured out my answer, but it just led to another question.

The code answer was this:

Dim strsql As String
    
    strsql = "SELECT AccessCards.EmployeeID FROM AccessCards WHERE (AccessCards.Type)='Visitor';"
    
    
   
    
    cmbVNumber.SetFocus
    cmbVNumber.RowSource = strsql

Select allOpen in new window

Which looks pretty much like what  jpaulinoPosted on 2012-07-05 at 15:47:16ID: 38157990 gave me.

I don't know why I couldn't make it work before. I did have to take off the Me! part of the thing.

Anyway, I am going to note that as the solution, but this just leads me to another question. I will post it separatly, but it is how make whatever is chosen in the combo box pull up the appropriate record.

Thank you, everyone, for all the help!!!!

I don't know why this didn't work for me when I originally tried it. I had to take off the Me part of the code, but it is now doing what I wanted.