Full Version: newbie-Pass parameters from a form to a query?
UtterAccess Forums > Microsoft® Access > Access Forms
StevenA
Hello all, I'm trying to automate some of my engineering tasks that I've got hUge excel spreadsheets for. Basically I'm emulating what should be done in a database, so now I figure I'll try and replace it with a database.
The end result of my work will hopefully be
1) a few forms that let me add/edit my equipment information table and any other supporting tables as well
2) a few forms that let me choose some parameters to then create reports that will replace the spreadsheets in excel I was using.
I've slowly gotten to the point where I created the tables I needed and designed some queries that give me the type of information I was obtaining in excel. I even created a report layout that similarly mimicked my excel "reports".
So now I've gotten to a point where I'm stuck. My first questions involves passing options chosen by the user on a form to a query/report. I'd like to create a form with some drop down boxes that when options are chosen pass the options on to one of the queries I've designed. I'm hoping to then pass this information on to create a Report.
How would I go about creating such a beast?
thanks!!
kbrewster
In query you would need to place the form's paramters in the column's criteria. The criteria looks like..
[Forms]![Form Name]![Control Name]
hange [Form Name] to the name of the form, and change [Control Name] to the name of the control that holds the criteria.
HTH.
StevenA
ok, and how do I tell the command button to open that query while passing this value?
StevenA
ok, I think I figured this out, I used:
DoCmd.OpenQuery "Load Calculation Location", acViewNormal, acReadOnly
hich worked.
Another question, how do I use a listbox and tell it to use a specific field of a table?
more so, I've got a table of "Locations", with one field being the full name and another field being the abbreviated name. I want to use the full name for some things and the abbreviated name for others. I have them in this table so I can link them throughout the database and choose which to use where.
I'd like to use the full name for the listbox but have it pass the abbreviated name to the query.
thanks!
hopper20
Hello
First, you would want to create a report and set the report's record source to the query that you have saved. Don't worry, you can pass whatever parameters to it later. To set the record source of the report, you can either do this on the properties box, or you can do it in code on the Form_Open event. To do this in the Form_Open event, simply add this line of code:
me.recordsource = "[Queryname]"
Make sure to set your controls on the report to whatever fields you want and that are in the query. For example, you could create a textbox named Year, (I would recommend adding a prefix to any control, such as txtYear) and then set the ControlSource to the name of the field "Year". Now about the form and the parameters. Depending on if you want to view the report and then print, or just plain print, that will decide which command to use. But basically, on the button's On_Click event, you would add this code:
docmd.openreport "[ReportName]",acviewnormal,,"* Parameters "
The last part of this line is your where criteria. This simply filters your query based on what criteria you add. For example, your code would look like:
"Year = " & me.combobox
Hope this helps!
KL
StevenA
thanks for the help KL, completely confused me though...
I've got the report created and its set to use a query I have made. I used Kristen's suggestion to have it pass the field I want to the query to generate the report.
so I don't know where to put the me.recordsource, I tried it in the form's "On Open" line and it gives me an error. I also tried using docmd.openreport with the "* Parameters " which also gives me an error. Where would I put "Year = " & me.combobox?
Also, I'm trying to get more than one filtering option setup, but if I setup more than one listbox then a value must be selected in each one. What if I only want it to filter by one of the boxes? (ie. how do I get a list box to default to "All"). What if I want to select more than one option in the list box?
thanks!
hopper20
Sorry for the confusion. Instead of the Form's On_Open event, just look in the Properties window and find the property that says "record source". Here, enter the name of your query, or click the down arrow on the right and select your query. As for the filtering, at the end of the "DoCmd" line, after the 2 commas, this is where you would enter your "where" criteria for your report. You must know the names of your combo boxes and the values in them. If you don't understand the rowsource of your combo box, let me know. An example of a "where" criteria would be "Year = " & me.combobox The combo box must contain a "Year" value. So, the entire line of the "DoCmd" code would look like:
oCmd.OpenReport "QueryName", acViewNormal,,"Year = " & me.combobox
If you wanted to add more criteria to it, it would look like so:
DoCmd.OpenReport "QueryName", acViewNormal,,"Year = " & me.combobox & " And PartNo = " & me.combobox2
This would be the concept for this.
HAs for the default values, you could create them in a table, or you could make a union query.
Hope this helps you!
KL
StevenA
Great, so I've got:
Private Sub cmdOpenQuery_Click()
DoCmd.OpenReport "HP Rated Equipment By Location", acViewPreview, , "[FLA and PF Calculation].[Location] = " & Me.lbxLocation & " AND [FLA and PF Calculation].[Load Units] = " & Me.lbxLoadUnits
End Sub
where "HP Rated Equipment By Location" is the name of the report, and lbxLocation and lbxLoadUnits are the two list boxes in my form.
Oalso removed the [Forms]![Form Name]![Control Name] commands in the query, though I'm not sure I needed to do so.
I get an error though,
"Run-time error '3615';
Type mismatch in expression."
also, is there a way to have it so if no option is selected in the list box that it will not use that as a query option? If so, how can I get it so a selection in a listbox can be removed?
Thanks!
Jack Cowley
I assume that Location is Text and not a number and that LoadUnits is a number:
CODE
DoCmd.OpenReport "HP Rated Equipment By Location", acViewPreview, , "[Location] = '" & Me.lbxLocation & "' And [Load Units] = " & Me.lbxLoadUnits

Also, spaces in object names are NOT a good idea and cause you a great deal of grief...
hth,
Jack
StevenA
they are both text.
I've changed everything to not have spaces, and of course now the queries that worked fine no longer do...
Jack Cowley
I don't think you need the query since appear to be opening the report with this code. Here is the code with both items having Text criteria:
CODE
DoCmd.OpenReport "HP Rated Equipment By Location", acViewPreview, , "[Location] = '" & Me.lbxLocation & "' And [Load Units] = '" & Me.lbxLoadUnits & "'"

Removing spaces can be dangerous too if you do not have some software to update all instances of the strings. If you have removed the space you willl need to be sure they have been removed from tables, forms, queries, reports, etc. My apologies if you felt that you needed to remove then now as I just wanted to indicate that spaces in object names are not a good idea...
Jack
StevenA
well I got the queries working but I still get that error.
Jack Cowley
Where are you getting the Type Mismatch error? In the Query? In the code to open the report?
StevenA
in the code
Jack Cowley
If the error is in the code I gave you then either 'Location' or 'Load Units' is text and should be a number or is a number and should be text. You will have to tell me the Data Type (Text or Number) for 'Location' and 'Load Units'. I assume that neither is a Date Data Type...
ack
StevenA
in the Table both Location and LoadUnits are Text.
don't think I can change them in queries?
Jack Cowley
You do not need to change anything in the query... This code:
CODE
DoCmd.OpenReport "HP Rated Equipment By Location", acViewPreview, , "[Location] = '" & Me.lbxLocation & "' And [Load Units] = '" & Me.lbxLoadUnits & "'"

Should open a report named "HP Rated Equipment By Location" and show only records where 'Location' is equal the the text typed in a Text Control named 'lbxLocation' and where 'Load Unit' is equal to the text typed in a Text Control named 'lbxLoadUnits'.
Oalso assume that you report is based on a query that has a 'Location' field and a 'Load Unit' field.
Jack
StevenA
ok, apparently having "FLAandPFcalculation.*" for Field along with specific fields for FLAandPFcalculation doesn't work...
How though, it pops up two "Enter Parameter Value" boxes that as for values for the options I've chosen...
StevenA
is there a book or some other resource that discusses how to use VBA with Access? I've got two books on Access (one which is 700pages and has basically the same content as my 300page book...) but neither discuss what I consider useful usage of Access, it would be nice if I had some sort of resource for all these VBA commands, how they're used, etc.
Thanks!
Jack Cowley
I don't understand your first statement...
o you have a query involved with what you are trying to do or are you only using the code to open a report? If the report is based on a query it should NOT be a parameter query and that may be your problem.
Jack
Jack Cowley
I would suggest you go to your local bookseller and look at ALL the books on Access and Access Code. What might work for me may not be the book for you so look at all of them, find the one that you feel will carry you forward in your quest then get that one. Be sure and look at all of them as they are not cheap and you don't want to get one that will be inadequate in a very short period of time.
ack
StevenA
I have a report that is based on a query. I want to call that report and pass parameters for it to give to the query so that I don't need a parameter query.
Jack Cowley
Reports do not pass parameters to queries. If a query 'gets' parameters it is a parameter query. I think what you want is to open the report to a specific record and you can do that without any parameters for the query so the query will not have any 'criteria'. Code like this will open a report to a specific record:
oCmd.OpenReport "ReportName", , ,"[PrimaryKeyName] = " & Me.ControlOnFormWithPrimaryKey
hth,
Jack
StevenA
No, I want a report that collects data from a query and displays it.
think I may be getting in over my head in this. I also don't think Access's reports can display what I want. I'll need to find a way to dump a query's results to an excel worksheet following a specific format, that I think would be the best end result for what I'm trying to produce. Either that or do this all via a web interface and use tables.
I think I may just put together some php pages to access and SQL database, I know HTML and I've dabbled with PHP before...
Jack Cowley
No problem with a report based on a query. Just create your query and use it to create you report. You can 'filter' the query to show selected data or you can just let it do its thing. Also, the report does not 'collect the data from a query', but simply puts the data from a query into a format of your choosing. Spliting hairs on terminology, but you should know that the query does the data collection and the report displays it.
don't know if you are in over your head or not, but be aware that Access is not Excel and does not display data as Excel does.
Good luck with this and if you decided to use Access and have questions you know where to come for answers...
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.