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?

 

[Linq Adams via AccessMonster.com]

If not already doing so, base your form on a query, even if only one table is
involved.

In that query, create a calculated field; call it RetrievalField

Enter this

RetrievalField:[PARTNO] & " " & [MATERIAL] & " " & [TOOL]

Now go back into Design View for your form

Change the record Source for your form from the table (if it was originally
based on a table) to the query

Delete your current combobox

Create a new combobx, using the Wizard and the third option

When asked for a field, use the newly created field, RetrievalField

 

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