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
MsgBox ("corresponding tool number was found")
' set form to existing matching record
ToolID = Nz(DLookup("[TOOLID]", "tblTool2", stDup))
All your code does is confirms that a record exists and then puts the ToolID in the ToolID field on your form for a new record (or even worse - over a current record). It doesn't pull up the required record into the form.
I think you can use the FindRecord action (docmd.findrecord ....) to populate your form with the required record. Check out the help as to how it works but it's basically the same as if you were using the Find icon (but without the graphics).
Also, if I'm following what you are doing correctly, ToolID = Nz(DLookup("[TOOLID]", "tblTool2", stDup)) is redundant. You've already proven that the tool number you are searching for exists and you know what it is - TNum. I can't see why TNum will be any different from the ToolID that you lookup (?). So use TNum in the FindRecord criteria.
If DCount("txtTNumEnter", "tblTool2", stDup) > 0 Then
'undo duplicate entry
' alert user of existing record
MsgBox "Tool Number found. You will be redirected to that record."
' go to existing tool number
Set rsc = Nothing
And it still isn't working. Before, I thought that if I reset the ToolID then requeried it, the subform would fill in with the info... it was an attempt at a work-around and it failed! Oh well...
so is the "rsc.findfirst" the same as what you were talking about?
Well, I've been having major issues with getting this function to work for quite a while now and I've tried several different approaches (i'm teaching myself VBA as I go- which is not so easy for me... )
anyway, the way it's set up right now... the main form is connected to a table that contains Job info. At the top, there are all of the fields that come from that table. when the user enters a job number, there is a search. if there is a record found in tblJob containing that job number, the rest of those fields are filled in, along with a subform containing the tool information if it was previously filled in. If there is no tool number attached to the job, the subform is invisible and the 2nd set of fields (unbound) are made visible. (It should also be this way on new records.) I'm trying to get the same search function to work on the unbound field, txtTNumEnter. After the user enters a tool number, there is an underlying search. if the tool number is found in tblTool2, then I would like to set the record source for the subform to that record and make it visible, and make the unbound forms invisble again. If the tool number is not found, the user should be allowed to continue entering information for that tool number, hit "Add Tool" cmd button, which would refresh the subform and hide the unbound fields.