Wildcard Criteria based on field from form (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 02:06
Joined
Jun 8, 2019
Messages
179
Hi

Is it possible to use wildcard criteria based on a form field?

I am trying to ensure that users do not enter a new item into a table by entering a similar but slightly different description. For example, "Large Beef & Potato Pie" may already be in the table but I want to at least alert the user if they were to try and enter, say, '"Beef & Potato Pie Large" that a similar item already exists.

Ideally, I would like to display a list of similar items as the user enters the data with the list reducing as each character is entered. For example, if the user types "Lar" the list will display all items with those characters contained in items in the table but as the user types further, say, "Large" it will only display those items from the table that contain the word "Large", etc.

It is important that the criteria searches in all parts of the current table field, not just starting with certain letters.

I hope I have explained this clearly. I know what I am trying to achieve but am struggling to put that in writing!

Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,169
do you intent to add a Listbox?
make sure to set the listbox Row Source Type to: Table/Query.

you can add code to the Textbox's Change event to populate the listbox, eg:

Code:
Private sub textbox1_Change()
Dim sText As String
sText = Trim(Me.textbox1.text & "")
If sText = "" then
Me.listbox1.RowSource = "select fieldname from your table where (1=0);"
else
Me.listbox1.RowSource= "select fieldname from table where fieldname like '*" & sText & "*'"
end If
End Sub
 
Last edited by a moderator:

Drand

Registered User.
Local time
Tomorrow, 02:06
Joined
Jun 8, 2019
Messages
179
do you intent to add a Listbox?
make sure to set the listbox Row Source Type to: Table/Query.

you can add code to the Textbox's Change event to populate the listbox, eg:

Private sub textbox1_Change()
Dim sText As String
sText = Trim(Me.textbox1.text & "")
If sText = "" then
Me.listbox1.RowSource = "select fieldname from your table where (1=0);"
else
Me.listbox1.RowSource= "select fieldname from table where fieldname like '*" & sText & "*'"
end If
End Sub

Thank you so much - that is brilliant and works exactly how I needed it to!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,169
you're welcome!
 

Drand

Registered User.
Local time
Tomorrow, 02:06
Joined
Jun 8, 2019
Messages
179
Sorry to trouble you again,

Further to above post, I have added to the change event which makes the list box visible immediately under the text box as soon as the user starts entering data.

I need to set the visible property to false if the user continues to type and the listbox is empty, i.e, the item does not exist in the table and can therefore be legitimately added.

I have looked at notinlist but this seems to apply to combo boxes only.

So what i am trying to do is hide the list box once the entered item no longer qualifies for the list box and the list box is empty.

Hope this makes sense.
Many thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,169
Code:
Private sub textbox1_Change()
Dim sText As String
sText = Trim(Me.textbox1.text & "")
If sText = "" then
    Me.listbox1.Visible = False
    Me.listbox1.RowSource = "select fieldname from your table where (1=0);"
else
    Me.listbox1.RowSource= "select fieldname from table where fieldname like '*" & sText & "*'"
    Me.listbox1.Visible = (Me.listbox.ListCount > 0)
end If
End Sub
 

Drand

Registered User.
Local time
Tomorrow, 02:06
Joined
Jun 8, 2019
Messages
179
Thank you again. Just what I needed:)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,169
you're welcome!
 

Users who are viewing this thread

Top Bottom