Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006

    Unanswered: Search for Existing Record

    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))

    Me.ToolID = ToolID

    End If
    End Sub

  2. #2
    Join Date
    Jul 2004
    Southampton, UK

    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.


  3. #3
    Join Date
    May 2006
    Actually, I must have been working on it quite a bit after I posted this yesterday. This is what my code looks like at this point:

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

    'Set rsc = Me.RecordsetClone
    Set rsc = CurrentDb.OpenRecordset("tblTool2", dbOpenDynaset)
    TNum = Me.txtTNumEnter.Value
    stDup = "ToolNum = '" & TNum & "'"

    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
    rsc.FindFirst stDup
    End If
    Set rsc = Nothing
    End Sub

    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?
    Last edited by serendee; 06-09-06 at 08:45.

  4. #4
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    As Howey suggesed, I think you should do away with the rsc recordset, use DLookup to confirm record exists, and use this

    DoCmd.FindRecord txtTNumEnter

    in place of

    rsc.FindFirst stDup

    This works when txtTNumEnter control has the focus (and is a bound control ?)

    If is is not a bound control, then I think you should use a the set rsc = me.RecordsetClone as you seem to have used previously(!), and add

    Me.Bookmark = rsc.Bookmark

    after rsc.FindFirst stDup

    The recordsetclone will have the same bookmarks as the form, but I don't think a separatly created recordset does (??)

    Does that help ?


    ps. do you need to 'Cancel' the update if the record exists ??

  5. #5
    Join Date
    May 2006
    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.

  6. #6
    Join Date
    May 2006
    I think my problem right now is that I'm trying to reference a field in a different table using the unbound fields. This line is throwing an error: stFind = "ToolNum = '" & Me.txtTNumEnter & "'"

    It says it doesn't recognize ToolNum as a valid field name. It's in the tblTool table. the form is bound to the tblJob table. Is there any way I can access the tblTool table in code to get that?

    I'd been playing around with queries too... should I set the form to a query that includes all fields from both tables? (Job and Tool)
    Last edited by serendee; 06-09-06 at 10:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts