Passing Parameter From Form to Query (1 Viewer)

[GrexP]

I’m moving from VB to Access for creating front ends. One of the things I would commonly do in VB is build a SQL string based on controls. I would then create a records set. Simple enough.

Now in Access I want to pass parameters to a query from a form, run the query, and then produce a report based on the query. Calling a single control’s value is pretty straight forward, but what if I want to loop through a series of controls and then pass the values? How do I do that?

For instance, I have a half dozen unbounded check boxes and for each one that is checked I want to query on it. The string might end up looking like this

((Table.Status)="2" Or (Table.Status)="3" Or (Table.Status)="5")

I can build that string in code no problem. If it’s stored in a variable, how do I pass it to the query? In this case do I just build the query string, create a DAO record set, and then set the reports RecordSource to the record set?

Thanks in advance.

 

[Tim L]

I’m moving from VB to Access for creating front ends.

Woaah! Welcome to the forum and I hope that you get many useful responses. Note, however, there are many people who would probably say you're taking a backward step by doing this!

I want to pass parameters to a query from a form, run the query, and then produce a report based on the query.

I know nothing about DAO, but if you want to retrieve the value from a control, and the code is in the same form as the control, you would use something like:

<destination> = Me.ControlName.Value

Changing ControlName accordingly.

what if I want to loop through a series of controls and then pass the values? How do I do that?

Have a search through this and the VBA forum for threads with the text 'For Each ctl' in them, they may help.

Hope that this is of some help. Once again, welcome and enjoy.

Tim

 

[neileg]

You can use the control in the WHERE clause of a query, whether this is a stored query or one built in text. The syntax for refering to a control in this way is Forms!MyFormName!MyControlName

 

[GrexP]

Thanks for the replies. Perhaps I didn't explain the problem I'm having well enough. Accessing a control's value is not an issue. As I said, looping through the check boxes and building the query string is easy. Also, accessing a single controls value from the query window is also easy, as I stated.

The issue is multiple controls for a single field. I have 6 checkboxes and some are checked and some are not. All of these relate to a possible value in a column - we'll call it Status. If the user checks three of them I want to query where the value of Status is one of those 3 values.

((Table.Status)="2" Or (Table.Status)="3" Or (Table.Status)="5")

Doing this in code is not an issue. However, I'm trying to get accustomed to working in Access instead of pure VB. Is there a way, from the query window, to build the query based on which of the 6 check boxes are checked?

 

[zyxwvu44]

I had a similar problem where i used checkboxes to tell which multiselect listbox to use for parameters. It may help, http://www.access-programmers.co.uk/forums/showthread.php?t=132491

 

[GrexP]

Thanks, but no, that's not the problem at all. In fact, it's the exact opposite of my problem. You're building the entire SQL string in code which I could do in my sleep. I'm talking about passing values drawn from multiple controls to query a single field in THE QUERY DESIGN WINDOW.

The Access query design window is the key to my problem. On the criteria line in the query design window I can access a controls value with a line like this...

[Forms]![Orders]![OrderID]

OrderID is a textbox on the form "Orders". What ever value is in OrderID.Text will be queried when the query runs. Now, what if I want to query on several values from several text boxes and concatenate them with Or? I would assume I could call multiple text boxes on multiple criteria lines. In this case, though, they are check boxes that don't have a text property I can read.

I beginning to think what I want to do can't be done. It's no big deal.

 

[GrexP]

For anyone interested, the solution was to build the query in the query design window with no criteria. Then use that query to build the report. When I call the report from code (DoCmd.OpenReport) I pass the WHERE clause as a parameter to the OpenReport method. It does pretty much what I want to do. I can build a report using a query and then pass parameters in code.

Maybe there's another way, though. I am new to Access. Any ideas?

 

[J Shurte]

I faced the same problem in my last project. I created a dynamic tabel for each user as thay opened Access and added the SQL arguments to the table along wiht the form name when I needed to run a filter I would read in the table arguments for that form and filter based on the rebuilt SQL argument string.

 

[neileg]

Mmm.. This thread is over a year old.