Use textbox to adjust criteria in query

[tonesbones]

Dear Access Gods,

I'm working on a way to change the criteria of a query using input from a textbox on a form. i.e. An ID number is typed into the textbox and I press a button and the text is automatically updated to the criteria in the query.

Even more details:

Table:
ID SCORE
1 100
2 90

User inputs "2" in textbook and the button changes the query "ID" filter to "2".

I know there is an easier way to do this without textbox/form/vb, but that is why I'm here asking for some help.

Regards, Tony

 

[John Big Booty]

Welcome to the forum.

You can use the following schema to pick up information from a text box on a form as criteria for your query

[Forms]![[B]YourFormName[/B]]![[B]YourTextBoxName[/B]]

Change the two bolded areas to reflect the reality of your DB.

 

[tonesbones]

Thank you so much!! That was such a quick reply. I added a small change to handle empty textbox.

IIF(isnull(Forms![Form1]![YourTextboxName]),[NameofField],Forms![Form1]![YourTextboxName])

 

[John Big Booty]

You could also do that using the Nz() function

 

[tonesbones]

Hmm, what if I want to add like a giant textbox with multiple rows i.e.

Text1:
1
2
3
4

and I want that to be updated to the criteria... is it possible?

I know it's just easier to have multitextboxes and then link it together with "or" statement. But I want to make it as flexible as possible.

 

[John Big Booty]

It is certainly possible to do what you are after.

What you would need to do, is set a delimiter (like a comma) to separate your search terms. You would then need to construct your query as a SQL statement using VBA, and replace your delimiter with an OR argument.

If I get a chance I try and put a small sample together latter in the day.

 

[John Big Booty]

Here's a sample that demonstrates how this might be done.

 

[tonesbones]

YESS, thank you so much!! This is going to be very helpful.

I tried to take the VB codes apart to understand how it works and if i make adjustments to the code where it replaces the "comma" with a "new line"

Me.Text2 = Replace(Me.Text2,",", "')) OR (((TBL_MemberDtl.MemNum)='")

to

Me.Text2 = Replace(Me.Text2, Chr(13) + Chr(10), "')) OR (((TBL_MemberDtl.MemNum)='")

This will let me adopt the same idea? I tried checking to see if i need to adjust anything else, but everything seems right. Thanks for all your help, appreciate it.