Solved Search with two primary keys (1 Viewer)

Pop_Access

Member
Local time
Today, 02:27
Joined
Aug 19, 2019
Messages
66
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:-

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

Super Moderator
Staff member
Local time
Today, 05:27
Joined
Jan 23, 2006
Messages
15,364
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

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,173
maybe add some delimiter to the date:
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 28, 2001
Messages
27,001
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

AWF VIP
Local time
Today, 02:27
Joined
Apr 13, 2010
Messages
1,401
Based on your first post if shift IDs are A,B,C you want to wrap the variable in single quotes:
Code:
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

AWF VIP
Local time
Today, 10:27
Joined
Oct 27, 2015
Messages
998
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

Member
Local time
Today, 02:27
Joined
Aug 19, 2019
Messages
66
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:-
Code:
 DoCmd.SearchForRecord acDataForm, "frm_bed", acFirst, "[Shiftdat] = #" & Me!cmbSearchList.Column(0) & "#" & " and [ShiftId] = " & Me!cmbSearchList.Column(1)
 

Users who are viewing this thread

Top Bottom