lookup record on two fields

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

 

[Van T. Dinh]

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.

 

[SAC]

Thanks! Makes sense.

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)

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.