Combo Box - Find Record - Multiple Field Key Problem

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

Ok, so my table has a three field primary key (PARTNO, MATERIAL, TOOL) - so
that the user cannot save the same combination of PARTNO, MATERIAL, TOOL.
Works great.

My problem is using the access wizard to create a combo box to choose the
correct record. It automatically creates the field using only the first
field PARTNO eve though I chose all three fields to be present in the combo
box, as demonstrated by the code below:

Private Sub Combo187_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNo] = '" & Me![Combo187] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This will not work as I need it to find that particular record that has the
combination of PART/MATERIAL/TOOL - It will just automatically grab the
first PARTNO record it finds.

It tried to modify the rs.FindFirst statement as follows:

rs.FindFirst "[PartNo] = '" & Me![Combo518] & "'" And "[Material] = '" &
Me![Combo518].Column(1) & "'" And "[tool] = '" & Me![Combo518].Column(2)

but that gives me errors and will not work. Any advice?
 
It would help if you indicated what error you are getting. I'm guessing that
the problem is with your quoting. Try this:

rs.FindFirst "[PartNo] = '" & Me![Combo518] & "' And [Material] = '" &
Me![Combo518].Column(1) & "' And [tool] = '" & Me![Combo518].Column(2) & "'"
Click to expand...


Joe Williams said:
Ok, so my table has a three field primary key (PARTNO, MATERIAL, TOOL) - so
that the user cannot save the same combination of PARTNO, MATERIAL, TOOL.
Works great.

My problem is using the access wizard to create a combo box to choose the
correct record. It automatically creates the field using only the first
field PARTNO eve though I chose all three fields to be present in the combo
box, as demonstrated by the code below:

Private Sub Combo187_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PartNo] = '" & Me![Combo187] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This will not work as I need it to find that particular record that has the
combination of PART/MATERIAL/TOOL - It will just automatically grab the
first PARTNO record it finds.

It tried to modify the rs.FindFirst statement as follows:

rs.FindFirst "[PartNo] = '" & Me![Combo518] & "'" And "[Material] = '" &
Me![Combo518].Column(1) & "'" And "[tool] = '" & Me![Combo518].Column(2)

but that gives me errors and will not work. Any advice?
Click to expand...
 
Back
Top