FindFirst to Find outside of subform

[Stu Dongel]

Hello all I have a form based on the table tblAccountManager with the
textbox AccountManager that groups the subform sfrmae2 that hasa combo
box called Client linked to a field Client in the Table tblClentList.

Essentially the form show all the accounts in a subform listed to a
given manager from the main form.

I am using this code to check for duplicates, then jump to a record...

Private Sub Client_AfterUpdate()
Dim varTemp As String

RecordsetClone.FindFirst "Client = '" & Client.Text & "'"
If RecordsetClone.NoMatch Then ' No duplicate
If Not IsNull(Client.OldValue) Then ' Are we on an existing
record?
varTemp = Client.Text ' Save the new ID value
Undo ' Undo the change to the existing field
DoCmd.RunCommand acCmdRecordsGoToNew ' Go to a new record
Client = varTemp ' Fill in the ID value
End If ' If there's no duplicate and we're on a new record
already do nothing
Else ' There's a duplicate
Beep
Undo ' Undo the change to the existing field
If MsgBox("Record already exists. Would you like to jump to
it?", vbYesNo, "Confirm") = vbYes Then
Me.Bookmark = RecordsetClone.Bookmark
End If
End If

End Sub

It works great execpt for it only looks for records in the current
subforms view, but i need it to check for all the records in
tblClientlList and then jump to acordingly...
does this make sence?

thanks in advance!
Stu

 

[Allen Browne]

Instead of searching the form's RecordsetClone, perform a DLookup() directly
on the form's table. DLookup() returns Null if there is no match.

More info:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html