I have a table whose data need to be viewed through the form. There are no primary key fields. The database is built to record the transactions of same 10 customers on a daily basis.So I have 10 customers IDs, for each day I can have those 10 customers ID only once. So When I choose date and customer ID through a combo box, the form should bring up all the fields for that customer Id on that day.

Any help one this.

Thanks

Can you lay out the table structure where you are doing the look up? And are you displaying results in a subform, or a form?

There should always be primary key fields. You can add one at any time. Just add a PK field, give it an autonumber, and then designate it the primary key. This is essential for indexing your data.

In any event, the problem with a combo box look up without a primary key, and correct me if I'm wrong, but you can only bind the combo box to one column of data. That column will either be the Customer, or the Date, but not both. This is a problem if you are relying solely on a superkey for look ups. Superkeys are a bad idea that dates back to pre-relational database days of big, flat databases. Typically the combo box binds on the PK, which you don't have. So I don't see how you are going to get this to work.

mmcdonal

Really, it wouldn't be any trouble to add a primary key and use it as your bound column on the combo box. That is the solution to this problem.

mmcdonal

Something else to keep in mind, one can retrieve more than one selected value from a combo or list box, provided that the RowSource contains 2 or more columns of data, regardless of the Control Source.

One of the other properties in a combo box control is the .Column property. For example, if you create a 2-column combo box (it can be bound or unbound as well), you can retrieve 2 columns of data from a selected row within the AfterUpdate property once the user has made a selection. A sample SELECT query used for the combo box's rowsource could be:

"SELECT DATE, CUSTOMER FROM CUSTOMER_ORDERS ORDER BY DATE, CUSTOMER"

Here, you have a 2-column combo box (ColumnCount = 2). You can use the AfterUpdate() property from the combo box to retrieve the two values from the user and display the selected row in a bound form like this:

Private Sub cboDateCust_AfterUpdate()
   Dim rs As DAO.Recordset

   Dim strSearchString As String
   Dim varDate As Variant
   Dim varCustomer As Variant

   varDate = Me.cboDateCust.Column(0)
   varCustomer = Me.cboDateCust.Column(1)

   ' to go to the selected row, assuming customer is numeric (no quotes)
   strSearchString = "DATE = #" & varDate & "# AND CUSTOMER = " & varCustomer

   Set rs = Me.RecordsetClone
   rs.FindFirst

   Me.Bookmark = rs.Bookmark
   rs.Close

End Sub

When using the .Column property in a combo or list box, enumeration starts with 0, which means the first column is Column(0), the second column is Column(1), etc.

Grant you, I'm a little bit of a dinosaur from the v1.1 days of MS Access using DAO here, but I haven't found a way to do bookmarking with ADO (open to suggestions, however).

If you DO use the "Dim rs As DAO.Recordset" as I have used in this example, be sure to check "Microsoft DAO 3.6 Object Library" (or "2.5/3.51", whichever is in your list) from Tools | References in your VBA editor.