Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Post Unanswered: filter a text box on a form

    I have a form which is used to locate and enter customer records. When you enter the phone number if the customer is in the table the form is populated with all the fields ( Name, address, etc. ). However if there is a duplicate phone number it stops at the first one depending on the sort. I would like to see a list of all (i.e. 1 0f 4) and have the option to accept the right record. Below is the code from the text box properties which I basically copied and posted off the web and changed field names. I am only a beginner with code.

    Function Find_BeforeUpdate(F As Form)
    Dim RS As Recordset, C As Control
    Set C = Screen.ActiveControl
    Set RS = F.RecordsetClone

    On Error GoTo Err_Find_BeforeUpdate

    ' Try to find a record with a matching value.
    Select Case RS.Fields(C.ControlSource).Type
    ' Find using Numeric data type key value?
    Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
    DB_DOUBLE, DB_BYTE
    RS.FindFirst "[" & C.ControlSource & "]=" & C
    ' Find using Date data type key value?
    Case DB_DATE
    RS.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
    ' Find using Text data type key value?
    Case DB_TEXT
    RS.FindFirst "[" & C.ControlSource & "] = """ & C & """"
    Case Else
    MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
    DoCmd.CancelEvent
    Exit Function
    End Select

    ' If a record is found, save the found record's bookmark.
    If RS.NoMatch Then
    Found = Null
    Else
    Found = RS.Bookmark
    End If

    ' If the record is found...
    ' ...cancel the BeforeUpdate event
    ' ...undo changes made to the current record
    ' ...and TAB to the next control to trigger the OnExit routine.
    If Not IsNull(Found) Then
    DoCmd.CancelEvent
    SendKeys "{ESC 2}{TAB}", False
    End If

    Exit Function

    Err_Find_BeforeUpdate:
    MsgBox "ERROR: Err " & Err & ": " & Error$, 48
    DoCmd.CancelEvent
    Exit Function

    End Function

    Function Find_OnExit()
    ' If the record is found, cancel the OnExit routine to stay
    ' in the control and go find the record.
    If Not IsNull(Found) And Len(Found) <> 0 Then
    DoCmd.CancelEvent

    ' Synchronize the form record with the found record.
    Screen.ActiveForm.Bookmark = Found

    Found = Null
    End If
    End Function


    Please help and thanks

    Steve

  2. #2
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: filter a text box on a form

    I'm a beginner also,
    If I had to do it I would firs count the number of matches (using a query), if its more than one then I would creat a new pop-up list box (on a form) that shows a compact sumary of each number and the customer attributes, then you can select the appropriate record and populate your fields.[




    QUOTE]Originally posted by steverog
    I have a form which is used to locate and enter customer records. When you enter the phone number if the customer is in the table the form is populated with all the fields ( Name, address, etc. ). However if there is a duplicate phone number it stops at the first one depending on the sort. I would like to see a list of all (i.e. 1 0f 4) and have the option to accept the right record. Below is the code from the text box properties which I basically copied and posted off the web and changed field names. I am only a beginner with code.

    Function Find_BeforeUpdate(F As Form)
    Dim RS As Recordset, C As Control
    Set C = Screen.ActiveControl
    Set RS = F.RecordsetClone

    On Error GoTo Err_Find_BeforeUpdate

    ' Try to find a record with a matching value.
    Select Case RS.Fields(C.ControlSource).Type
    ' Find using Numeric data type key value?
    Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
    DB_DOUBLE, DB_BYTE
    RS.FindFirst "[" & C.ControlSource & "]=" & C
    ' Find using Date data type key value?
    Case DB_DATE
    RS.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
    ' Find using Text data type key value?
    Case DB_TEXT
    RS.FindFirst "[" & C.ControlSource & "] = """ & C & """"
    Case Else
    MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
    DoCmd.CancelEvent
    Exit Function
    End Select

    ' If a record is found, save the found record's bookmark.
    If RS.NoMatch Then
    Found = Null
    Else
    Found = RS.Bookmark
    End If

    ' If the record is found...
    ' ...cancel the BeforeUpdate event
    ' ...undo changes made to the current record
    ' ...and TAB to the next control to trigger the OnExit routine.
    If Not IsNull(Found) Then
    DoCmd.CancelEvent
    SendKeys "{ESC 2}{TAB}", False
    End If

    Exit Function

    Err_Find_BeforeUpdate:
    MsgBox "ERROR: Err " & Err & ": " & Error$, 48
    DoCmd.CancelEvent
    Exit Function

    End Function

    Function Find_OnExit()
    ' If the record is found, cancel the OnExit routine to stay
    ' in the control and go find the record.
    If Not IsNull(Found) And Len(Found) <> 0 Then
    DoCmd.CancelEvent

    ' Synchronize the form record with the found record.
    Screen.ActiveForm.Bookmark = Found

    Found = Null
    End If
    End Function


    Please help and thanks

    Steve
    [/QUOTE]

Posting Permissions

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