Combo Box - Find Record - Multiple Field Key Problem

[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?

 

[Ray]

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) & "'"

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?