search for existing record

[Guest]

I had originally put code in a subform that allowed the user to type in a
tool number and then, on tabbing out of that field (or somehow losing focus)
it would check to see if the tool number already existed. if it did, it
would populate the rest of the fields with the information from that record.
The subform also displayed the existing record if it was already attached to
that job number (main form). Long story short, the subform apparently had
too much work to do and was throwing fits. So now, i'm trying to do that
same function on the main form with an "Add Tool Info" button (using SQL to
to get it into the correct table) if that record does not already exist. I
can't get the search functionality to work on the main form anymore. I think
it has something to do with lack of connection to the tool table? Or is this
line (DLookup("[TOOLID]", "tblTool2", stDup) enough to do that?


Private Sub txtTNumEnter_BeforeUpdate(Cancel As Integer)
' set variables
Dim TNum As Variant
Dim stDup As String
Dim ToolID As Integer
Dim rsc As DAO.Recordset

' get copy of recordset from record source to use for search
Set rsc = Me.Form.RecordsetClone

'get value of field
TNum = Me.txtTNumEnter.Value
' use to get records where toolnum in table is equal to variable
stDup = "ToolNum = " & "'" & TNum & "'"

' check to see if num of records in tblTool2
' is greater than 0, signifying tool number already exists.
If DCount("ToolNum", "tblTool2", stDup) > 0 Then
' if record is found, send msg to user alerting that tool number was
found
MsgBox ("corresponding tool number was found")
' set form to existing matching record
ToolID = Nz(DLookup("[TOOLID]", "tblTool2", stDup))

Me.ToolID = ToolID
Me.Form.Requery

End If
End Sub

 

[strive4peace]

Hi Seren,

try this:

'~~~~~~~~~~~~
Private Sub txtTNumEnter_BeforeUpdate(Cancel As Integer)

if isnull(Me.txtTNumEnter) then
'nothing was entered
exit sub
'is this what you want to do?
'or is it required?
end if

' dimension variables
Dim stFind As String

stFind = "ToolNum = '" & Me.txtTNumEnter & "'"

if not me.newrecord then
stFind = strFind _
& " AND TOOLID <> " & me.ToolID
end if

' check to see if ToolNum already exists
'in another record
Me.RecordsetClone.FindFirst stFind

If Not Me.RecordsetClone.NoMatch Then

MsgBox "corresponding tool number was found",, _
"Press ENTER to go to record"

'undo changes to current record
CANCEL = true
if me.dirty then me.undo

' set form to existing matching record
Me.Bookmark = Me.RecordsetClone.Bookmark

End If

End Sub
'~~~~~~~~~~~~

where ToolID is the autonumber field in tblTool2


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*