Solved Search with two primary keys

[Pop_Access]

I have table (tbl_Date) contains two primary keys (Date & shiftID), I designed a search function and the results will be display in combo box, but if i selected one of them the form will not go to the selected record.

the error in the following code:-

Private Sub cmbSearchList_AfterUpdate()
 DoCmd.SearchForRecord acDataForm, "frm_bed", acFirst, "[txtdate] = " & Me!cmbSearchList.Column(0) & "[txtshftID] = " & Me!cmbSearchList.Column(1)
End Sub

Note:-

As you know each day contains more than one shift (A,B, and C).

Thank you

 

[jdraw]

You do NOT have 2 primary keys on your table. You may have a compound/composite primary key.
A composite key is also a primary key, but the difference is that it is made by the combination of more than one column to identify the particular row in the table.

Perhaps you could show us a few records --some data and what you want to find.
Good luck.

 

[arnelgp]

maybe add some delimiter to the date:

Private Sub cmbSearchList_AfterUpdate()
 DoCmd.SearchForRecord acDataForm, "frm_bed", acFirst, "[txtdate] = #" & Format$(Me!cmbSearchList, "mm/dd/yyyy") & "# And [txtshftID] = " & Me!cmbSearchList.Column(1)
End Sub

 

[The_Doc_Man]

I would have to see a what was actually returned by Me!cmbSearchList.Column(0) but at first glance, you are missing a space between that and the next thing in the line. That is, & "[txtshftID] = " - which should probably be & " [txtshftID] = " (note space before the bracket.)

There is a problem with domain aggregate error messages. They are not always helpful. In fact, sometimes they can be totally absent and will just return nothing (or, worse, null).

Depending on exactly what is returned from the search list combo, Arnel's suggestion is also probably good. Note that his answer also included proper spacing.

 

[bastanu]

Based on your first post if shift IDs are A,B,C you want to wrap the variable in single quotes:

Private Sub cmbSearchList_AfterUpdate()
 DoCmd.SearchForRecord acDataForm, "frm_bed", acFirst, "[txtdate] = #" & Format$(Me!cmbSearchList, "mm/dd/yyyy") & "# And [txtshftID] = '" & Me!cmbSearchList.Column(1) & "'"
End Sub

Cheers,

 

[sonic8]

I designed a search function and the results will be display in combo box, but if i selected one of them the form will not go to the selected record.

Combo- and Listboxes need unique values in the BoundColumn to function properly. Concatenate Date and ShiftId in an addition column and use that as bound column of your combobox.

 

[Pop_Access]

Thanks for all, the problem was that i mentioned the text name (in the form) instead of the field in the table between [ ], so the correct code as below with minimal changes:-

 DoCmd.SearchForRecord acDataForm, "frm_bed", acFirst, "[Shiftdat] = #" & Me!cmbSearchList.Column(0) & "#" & " and [ShiftId] = " & Me!cmbSearchList.Column(1)