Drop down lists in a combo box

[trenny]

This is my first database. I am trying to set up a very simple database that would allow multiple users to track incoming shipments from established customers. I have placed the customers in a single table with all pertinent information. I would like the users to be able to go to a combo box and either drop down or begin to enter an id number to pull up the customer's record. Every time I set up a combo box, I don't have any records in the drop down list and entering data in the box just replaces the information already there.
Thanks for any help.

 

[vwhite]

Hi Trenny,

Does your Combo Box have a row source?
e.g.SELECT Customer_tbl.CustomerId, Customer_tbl.CustomerName
FROM Customer_tbl

You can use the Toolbox wizard when you create the Combo Box and it will insert this for you.

This will give you data in the combo box.

Then to go to the record you want, ensure that this combo box is not bound to a field i.e. the control source is empty. Then in the After Update event of the Combo box enter

Private Sub Combo6_AfterUpdate()

Dim rst As Recordset
Dim strCriteria As String
strCriteria = "[CustomerId] = " & me.Combo6

Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
Me.Bookmark = rst.Bookmark

End Sub

This is one way to do it anyway.....

You should be able to find examples in the Northwind database.

Cheers
....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama

 

[trenny]

Hey, thanks vwhite-
I used the toolbox wizard and now have my combo box. I tried to paste your text into the After Update screen that came up when I clicked the browse button, but it didn't work. Right now it reads:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
Not a big deal, because it still works ok like this. I have another question, if you have the time. I would also like to be able to mark each received record with a flag or something that would indicate one of three situations (ie early, late, on time). I tried the option buttons, but not sure how to use them.
Thanks ahead of time for any reply.

 

[vwhite]

What error message do you get when you try to use the After Update code?

To use the Option boxes, insert an Option Group first and set the Control Source to be the field in your table that you are interested in. Then add Option Buttons to the Option Group.

However the Options can only have numeric values. (see the Option Value Property on the Option Button Properties). Thus you will need to create a lookup table and set early, late and on-time to numeric values.
e.g. early = 1, late =2 and on-time = 3.
Then create relationship between this table and the parent table.

Hope this helps.... ....vwhite

"Benefit others. If you cannot benefit others then do them no harm"
- His Holiness Tenzin Gyatso, the 14th Dalai Lama