Placing a value from a function in a Query

[aziz rasul]

If I use an Inputbox function in a piece of code, how do I transfer the result of the Inputbox value into the criteria row of a select query?

I'm not a VBA person, so any code produced must be clear. Many thanks in advance.

 

[tc3of4]

Since you know how to write a bit of code...(i.e. creating input boxes) then this should be all you need...

1) Create a label or text box to hold the value entered by the user... (in this case lets use a text box called txt_TextBox)
Now within the sub procedure you in (in the code section)...

Private Sub SubYourIn()

'/** Enter the following code
dim response as variant

response = InputBox("Enter a Value")
txt_TextBox.Value = response

Exit Sub

Now that you've assigned the reponse to the text box you can use the value from the text as your criteria

Now within your Query that you are using, in the criteria for the field that you want to filter by type in

Like Forms![aFormName]![txt_TextBox].Value & "*"

This should be all you need....

tc3of4

The easiest way to ask for the user's input is in the design mode of your select query

Enter the following in the criteria box

Like [Enter A Criteria] & "*"

You need to keep the square brackets, all you need to do is Replace the Prompt -- "Enter a Criteria" -- to what ever you want,

If the user does not enter anything, then it will return all data, hence the "*" part.

Hope this wasn't too confusing...

tc3of4



[This message has been edited by tc3of4 (edited 01-18-2001).]

 

[aziz rasul]

tc3of4,

Thanks for your input. I understood your code. However what I really wanted was to place the value of "response" in the criteria of the query. How do I do this?

 

[Fornatian]

Aziz,

Why not transfer the data out of the input box into a hidden field on your form and then reference that value instead of trying to transfer it direct? Like this:

Dim strResponse as string

strResponse = Inputbox("Question Here","Title Here")

If strResponse = "" then
'user pressed cancel and a "" was
'returned by the inputbox
goto out
else
Me.unboundtextbox = strResponse
end if

out:
Exit sub


You should ow have a temporary store for the value.
Reference the unbound text field in the query and everything should be fine and dandy.

Ian

 

[aziz rasul]

Thanks Fornatian. It didn't occur to me to use a hidden field. I will try this and come back if I still have a problem.

 

[corfs1]

Is there a way to modify this script so that strResponse provides a list box of values from a table in the database? (I'm new to VBA)

Thanks