use a drop-down list for a parameter query

[mgonzales]

So what I have is an input form called Leadership Reporting that contains a form and subform. They enter the report period in the Leadership Reporting form which is linked to tblLeadershipTeam, then they complete the subform which is linked to tblActivity. I need the report to pull information from the Leadership Reporting form or the tblLeadership(b/c it contains all the info).

 

[boblarson]

I'm thinking you need to have a couple extra fields in your reporting period table which would be start month and start day and then end month and end day and then you can use the DateSerial function to piece together the actual date for the criteria in the start date and end date of the tblActivity when you use your query. You add the reporting period table to the query and join on the reporting period and then use the pieces to build the function in the criteria of the start and end dates.

Frankly I have to say that this set up is a bit difficult to use. I'm not sure what I would do but your data doesn't have things to link them together very well.

 

[mgonzales]

what I was trying to do was create a form that had buttons that would allow you to select something from a prepopulated dropdown and then generate the associated report with that parameter selected. I tried to simulate what you did with the simple sample you sent, but the back end piece wasn't working. The reporting period is a standard timeframe, it does not need to be separated into month and days. We use terms like week 1, week 16, etc, but just to make it easier I gave them date ranges.What I am trying to replicate would be what you did for your company name button.

 

[mgonzales]

You do that with a form reference to a control. See this sample for an example.

How are you relating the drop down menu to the button command to pull the report?

 

[boblarson]

How are you relating the drop down menu to the button command to pull the report?

Something of the nature:

DoCmd.OpenReport "ReportNameHere", acViewPreview, , "[FieldName]=" & Me.ComboBoxNameHere

 

[boblarson]

You should look at the code behind each OPEN button.

 

[mgonzales]

I am using your example as a template but it is not working properly. The form that these buttons are in, is it based off of a table? How do you get a field list to choose from?

You should look at the code behind each OPEN button.

 

[boblarson]

I am using your example as a template but it is not working properly. The form that these buttons are in, is it based off of a table? How do you get a field list to choose from?

It is based on the recordset of the report.

 

[mgonzales]

I guess I dont understand what a recordset means. When I look at the forms properties, the record source is blank. And the field list button is gray.

It is based on the recordset of the report.

 

[mgonzales]

I figured it out, I had entered in an extra letter somewhere. Thankyou!

How do you modify this code for multiple Yes/No fields?
' this opens the report in preview mode and you select the customer using the single quotes because Me.cboCompanyName
' returns text and text needs to be surrounded by quotes.
DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'"
End Sub

It is based on the recordset of the report.

 

[boblarson]

Something like this should work:

DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'" & " AND [YesNoField1Name]=True AND [YesNoFieldName2]=False"

Or if based on controls (checkboxes):

DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'" & " AND [YesNoField1Name]=" & Me.CheckBoxNameHere &  " AND [YesNoFieldName2]=" & Me.CheckBox2NameHere

 

[mgonzales]

I would like to use this just for check boxes. Would this code work?
DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'" & " AND [YesNoField1Name]=" & Me.CheckBoxNameHere & " AND [YesNoFieldName2]=" & Me.CheckBox2NameHere

So would I still need this portion of the code?
"[ReportPeriod]='" & Me.cboReportPeriod & "

Something like this should work:

DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'" & " AND [YesNoField1Name]=True AND [YesNoFieldName2]=False"

Or if based on controls (checkboxes):

DoCmd.OpenReport "FinalREPORT", acViewPreview, , "[ReportPeriod]='" & Me.cboReportPeriod & "'" & " AND [YesNoField1Name]=" & Me.CheckBoxNameHere &  " AND [YesNoFieldName2]=" & Me.CheckBox2NameHere

 

[boblarson]

If you aren't going to use the combo for reporting period then you would remove that portion, yes.

 

[mgonzales]

I have not been able to try this code because the checkbox selections are not working. It gives me an error that "Control can't be edited because its boud to an unknown field". What could be the problem? I have the field created, the only area that the properties box has for entering in a field name is one called "control Source" I have the field name in there. Is there something else I should be doing?

If you aren't going to use the combo for reporting period then you would remove that portion, yes.