auto complete form

[josros60]

I have this form, how can make it when start typing auto complete name:


I attached a picture of it, thanks.

 

[sneuberg]

Start typing where? If you are typing in a text box you could change the row source of the list box each time to type a letter in the On Change event.

 

[josros60]

if you see the picture will see check box right now have to scroll down or up and select.

i want to be able start typing first letter and select then type again and select next one so on.

is there any way to do it with after change code ?

the code that i have righ now on click is:

Private Sub txtName_Click()
' If user clicks on the name, check or clear
' the checkbox

    On Error Resume Next
    Me!ckPrintFlag = Not Me!ckPrintFlag
    
End Sub

thanks

 

[josros60]

Can anybody give me a help on this, please.

Just want to be able to type first letter and go to vendor, instead scrolling all the way down or up, thanks.

 

[sneuberg]

Take a look at the attached database. The record set of the form is filtered based on what the user types in the text box. The salient code from the on change event of the text box does this and is shown below. Maybe you can do something like this.

Private Sub Text41_Change()

Dim strTextBox As String
strTextBox = Me.Text41.Text
Me.Filter = "video_name like '*" & ESQ(strTextBox) & "*'"
Me.FilterOn = True
Me.Text41 = strTextBox
Me.Text41.SelStart = Len(strTextBox)

End Sub

'function to escape single quotes
Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

 

[josros60]

Thank you.

I tried when type nothing happen.

Here it's the code i copied from your example:

Option Compare Database


Private Sub Text41_Change()

Dim strTextBox As String
strTextBox = Me.Text41.Text
Me.Filter = "[Vendor Name] like '*" & ESQ(strTextBox) & "*'"
Me.FilterOn = True
Me.Text41 = strTextBox
Me.Text41.SelStart = Len(strTextBox)

End Sub

'function to escape single quotes
Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

Thanks again

hope you can point me why doesn't work.

 

[sneuberg]

This was just an example that you would have to modify to fit your system and needs. If you want me to do that for you then upload your database and I'll see what I can do.

 

[josros60]

I attachec a zip file, because exceeded the 2 mb limits.

Thank you.

 

[sneuberg]

The zip file is not attached. Please try again.

 

[josros60]

Attched now.

Thanks.

 

[sneuberg]

Just want to let you know that I'm work on this. I've run into a problem. The code that works in the example database doesn't work in yours. The difference is that the PrintLabelsSub form doesn't allow additions, nor should it. Nonetheless this cause the search text box to loose focus when there are no records found which causes an error, I working on a fix. I'll get you something as soon as I can.

 

[josros60]

Thank you very much.

 

[sneuberg]

The attached zip file contains instructions for installing the code I've come up with and your database with this installed so that you can see it work. A text box txtSearch has been added to the PrintLabelSub form. When you type into this text box the list below is filtered by the content of the text box. So if you type sp for example you will see the three vendors whose names start with sp. If you type k for example nothing is display as there are no vendors whose names start with k.

You can see in the code below that this is done by hiding the detail section of the form.when there are no matching records in the record set. I did it this way because if the filter returns an empty record set the focus for the txtSearch box disappears. The code relies on the focus being on the strTextBox to work.

Dim strTextBox As String
Dim rst As DAO.Recordset
strTextBox = Me.txtSearch.Text
Set rst = Me.RecordsetClone
rst.FindFirst "[Vendor Name] like '" & ESQ(strTextBox) & "*'"
If rst.NoMatch Then
    Me.Detail.Visible = False
    Exit Sub
Else
    Me.Detail.Visible = True
End If
Me.Filter = "[Vendor Name] like '" & ESQ(strTextBox) & "*'"
Me.FilterOn = True
Me.txtSearch = strTextBox
Me.txtSearch.SelStart = Len(strTextBox)

If you have any problems getting this to work let me know.

 

[josros60]

Thank you very much for you kind help, worked perfectly.