Run SQL query in VBA and store user input in variable (1 Viewer)

tonylomax

Registered User.
Local time
Today, 16:19
Joined
Oct 19, 2018
Messages
16
I have a query that allows the user to input the parameter and returns records on that basis.

This works fine and displays the results on a form. What I want is to display the user input on that form so if the user has multiple iterations of the form open they know which results set corresponds to which search.

I've looked into running the SQL in VBA and storing the input as a variable which is printed to the form but I don't know if this is the best way.

Can this be done in SQL alone or does it need VBA and/or a Macro?

Appreciate any suggestions.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:19
Joined
Oct 29, 2018
Messages
21,358
Hi,

You should be able to do it with SQL alone. Just add the parameter as a column too.
 

plog

Banishment Pending
Local time
Today, 11:19
Joined
May 11, 2011
Messages
11,611
so if the user has multiple iterations of the form open

That's not possible in Access. A form can only be opened to one instance--its not like a web browser where you can have 10 different windows opened all pointing to different urls. You can't have the same form opened twice at a time.

Is the overall goal to allow a user to filter what they see on a form? If so, you might check out the Form.Filter - https://docs.microsoft.com/en-us/office/vba/api/access.form.filter(property) - method.
 

Ranman256

Well-known member
Local time
Today, 12:19
Joined
Apr 9, 2015
Messages
4,339
if you entered the value into a box on a form, then you have the variable.
its in the box.
 

tonylomax

Registered User.
Local time
Today, 16:19
Joined
Oct 19, 2018
Messages
16
@theDBguy Would that not require adding a parameter to the table each time a user runs the search?

@plog apologies I probably wasn't clear - when I say different iterations I mean they are technically different queries/forms, but the SQL/form looks identical so the user can have functionally the same form open a few different times.

My query already filters the form results. This is just to remind the user what they searched for in the first place. Which on paper feels like it should be quite easy.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:19
Joined
Oct 29, 2018
Messages
21,358
Hi,

@theDBguy Would that not require adding a parameter to the table each time a user runs the search?

I’m sorry but I don’t understand your question because I can’t see your form or query. If you try my suggestion, you can tell us how it didn’t work or what went wrong with it.

Cheers!
 

plog

Banishment Pending
Local time
Today, 11:19
Joined
May 11, 2011
Messages
11,611
Ranman has the best solution. The user should open a search form, they then input their criteria somewhere, they click a button, the button runs code that does a DoCmd.OpenForm (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform) passing it the criteria it needs to open to just the user filtered data, that code also sets the text value of a label on that newly opened form displaying the criteria the user has selected from the prior form.
 

Cronk

Registered User.
Local time
Tomorrow, 03:19
Joined
Jul 4, 2013
Messages
2,770
I've had need in several past applications to have multiple instances of the same form open.


A typical case is processing application forms where the user is in the middle of entering a new record, and a phone request required access to another application already entered.


To distinguish between the forms, I'd modify the form's caption appropriately, in this case, with the applicant's name.
 

tonylomax

Registered User.
Local time
Today, 16:19
Joined
Oct 19, 2018
Messages
16
Thanks for the replies. in the end I went with something similar to what @Ranman256 suggested. I was trying to avoid this and it meant I had to create new queries, forms etc but in the end it was the easiest option!

Thanks again
 

Users who are viewing this thread

Top Bottom