using text box filter in single form?

[siamandm]

Hello ,
there is an access template called contacts, inside this database we have contact details ... which is a single form, and have a combo box on the top which allows going to another contact details!

so how you do a single form which shows details of a user, with a text box when you enter the user code it shows that user details.

[HansV]

Take a look at the After Update event of the combo box.

[siamandm]

thank you for the reply,
they did it using Macro, but i would like to know how to do it in VBA, + instead of combo box i want to use text box
also I converted it to VBA but i gives too many lines of code which i don't understand it fully

regards

[HansV]

I'll get back to you later today.

[HansV]

I would keep the combo box, but if you want to use a text box, change the combo box to a text box and name it txtGoToContact.
Create an On Change event procedure for the text box:

Code: Select all

Private Sub txtGoToContact_Change()
    If Me.txtGoToContact.Text = "" Then Exit Sub
    On Error Resume Next
    If Me.Dirty Then Me.Dirty = False
    DoCmd.SearchForRecord Record:=acFirst, _
        WhereCondition:="[Contact Name] Like '" & Me.txtGoToContact.Text & "*'"
    With Me.txtGoToContact
        .SetFocus
        .SelStart = Len(.Text)
    End With
End Sub

[siamandm]

thanks a lot, that's what exactly what i wanted....


can we add more functionality and make it dynamic as i type or at leas when i clear the text box it reset the filter and go back to the first record ?
thanks a lot again...

regards

[HansV]

The code should search as you type. You could omit the line:

Code: Select all

    If Me.txtGoToContact.Text = "" Then Exit Sub

[siamandm]

thanks for the reply, i don't know in which line i should write this :(

[HansV]

You don't have to write a line, I suggested that you could remove (delete) a line. Look at the code that I posted. The line

Code: Select all

    If Me.txtGoToContact.Text = "" Then Exit Sub

is the second line of the code, immediately below the Private Sub ... line. Delete the second line.

[siamandm]

thanks a lot, this working now!!
can we add another functionality ? let say we are looking for xyz , and it's not in the field we are looking for, it's possible to show msg inside a label saying " no result"?

or showing a blank form with a big label inside saying " No Results "?


regards

[HansV]

One option is to do the following:

Create a label lblNoMatch with Caption set to "No Result". Set its Visible property to No.

Change the code as follows (it replaces the existing code completely):

Code: Select all

Private Sub txtGoToContact_Change()
    Dim rst As DAO.Recordset
    On Error Resume Next
    Set rst = Me.RecordsetClone
    rst.FindFirst "[Contact Name] Like '" & Me.txtGoToContact.Text & "*'"
    If rst.NoMatch Then
       Me.lblNoMatch.Visible = True
    Else
        Me.lblNoMatch.Visible = False
        If Me.Dirty Then Me.Dirty = False
        Me.Bookmark = rst.Bookmark
    End If
    With Me.txtGoToContact
        .SetFocus
        .SelStart = Len(.Text)
    End With
End Sub

[siamandm]

thanks for your quick reply,

what i did, i create a label and set the background to white, and resized to completely hide all the controls, so when I look for something and it's not there, it hides all the controls inside the single form and shows msg: no result.
i have to questions please:
1- do you know a better way or more professional way, or this way is fine and good for production environments?

2- when i clear the text box i would like to hide the label again, how I do this, please

many thanks

[HansV]

Here is a slightly modified version:

Code: Select all

Private Sub txtGoToContact_Change()
    Dim rst As DAO.Recordset
    On Error Resume Next
    Set rst = Me.RecordsetClone
    If Me.txtGoToContact.Text = "" Then
        RunCommand acCmdRecordsGoToFirst
        Me.lblNoMatch.Visible = False
    Else
        rst.FindFirst "[strLastName] Like '" & Me.txtGoToContact.Text & "*'"
        If rst.NoMatch Then
           Me.lblNoMatch.Visible = True
        Else
            Me.lblNoMatch.Visible = False
            If Me.Dirty Then Me.Dirty = False
            Me.Bookmark = rst.Bookmark
        End If
    End If
    With Me.txtGoToContact
        .SetFocus
        .SelStart = Len(.Text)
    End With
End Sub

[siamandm]

that's awesome thanks a lot