lookup record on two fields

  • Thread starter Thread starter SAC
  • Start date Start date
S

SAC

I have a form. I can lookup a record with a combobox's afterupdate event
using code similar to this:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Key] = " & Str(Me![Combo14])
Me.Bookmark = rs.Bookmark

I'm trying to place similar code in a second combo box and lookup a record
from a table which has an index composed of these two fields and is unique.

Right now it looks up the record but it's adding a new record also. Here's
the code I'm using:

Dim rst As Recordset
Dim strSearchName As String
Dim strSearch As String

Set rst = Me.RecordsetClone
strSearchName = Me!txtEmpKey
strSearch = Me!cboJobKey
rst.FindFirst "Empkey = " & strSearchName & " AND JobKey = " & strSearch

'Me!txtEmpKey & "' AND JobKey = '" & Me!cboJobKey & "'"
If rst.NoMatch Then
MsgBox( "There's no record for this.")
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Any ideas on how to make it lookup a record without adding a new one?

Thanks.
 
If you meant the code finds the required Record but then also adds a new
Record then the problem is NOT in the code you posted. You will have to
check further along with your code or your Form.
 
Thanks! Makes sense.

Van T. Dinh said:
If you meant the code finds the required Record but then also adds a new
Record then the problem is NOT in the code you posted. You will have to
check further along with your code or your Form.

--
HTH
Van T. Dinh
MVP (Access)


SAC said:
I have a form. I can lookup a record with a combobox's afterupdate event
using code similar to this:

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Key] = " & Str(Me![Combo14])
Me.Bookmark = rs.Bookmark

I'm trying to place similar code in a second combo box and lookup a record
from a table which has an index composed of these two fields and is unique.

Right now it looks up the record but it's adding a new record also. Here's
the code I'm using:

Dim rst As Recordset
Dim strSearchName As String
Dim strSearch As String

Set rst = Me.RecordsetClone
strSearchName = Me!txtEmpKey
strSearch = Me!cboJobKey
rst.FindFirst "Empkey = " & strSearchName & " AND JobKey = " & strSearch

'Me!txtEmpKey & "' AND JobKey = '" & Me!cboJobKey & "'"
If rst.NoMatch Then
MsgBox( "There's no record for this.")
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Any ideas on how to make it lookup a record without adding a new one?

Thanks.
Click to expand...
Click to expand...
 
Back
Top