Help on report to open form to open query...

[saseymour]

OK, this is what I want to do:

I have a table of data. One of the fields is a combobox that populates itself from another table. I want to be able to create a report that prompts the user to select the data from combobox list, then display the query results.

I've read on the interwebs that in order to do this, I have to create a form that consists solely of the combobox, then get the query to run when the combobox is updated.


Here is the code that I've got in the form.

Private Sub Form_AfterUpdate()
stDocName = "qryCoater1MaintenanceSub"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

So, is it possible to have the report call the form which then calls the query?

Does that make any sense?

[pbaldy]

I normally open the report from the form, but if you want the report to open the form, you can do it from the report's open event, and you have to open the form in dialog mode. Then in the form you'd hide the form after the user makes a selection, so the form is still available for the query. Presuming the report is based on the query, you don't have to open the query.

[saseymour]

Paul - thanks for the information. Now, you mention to hide the form after the user makes a selection, which event would that be? On activate? And do you mean on the report, or on the form itself?

OK, so when I open the report, the form pops up. I make my selection from the combobox and have to hit enter (is there a way to have it activate upon just clicking on the selection in the combobox instead of having to hit enter?), then the results of the query show up, and the report is closed.

Any idea why the report would close and the query results would be displayed in table view?

Would I be able to hide the form by updating my code in the after update event in the form itself? Something like DoCmd.<insert stuff here> acHidden?

Private Sub Form_AfterUpdate()
stDocName = "qryCoater1MaintenanceSub"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub

[pbaldy]

You can use the after update event of the combo so they don't have to hit enter. You hide the form by setting its Visible property to False. The query results are displayed because you open the query.

[saseymour]

OK, so I've got the form and query both hiding after being selected (many thanks!). My only issue now is that ALL of the records on the table show up, its like the combobox input doesn't matter.

Any suggestions?

[pbaldy]

Does the report's source query use the combo as a criteria? Do you open the form in dialog mode so the report doesn't open until a selection is made? Can you post the db here?

[saseymour]

Paul - turns out I was pulling a query that didn't have the criteria set up for what I was wanting to do - now that I have modified that, it works like a charm! Thanks!

[pbaldy]

Happy to help!

[saseymour]

Uh oh, actually, I still have a problem. My combobox is not working. It shows all of the records on the table. I checked my query, and should I have any criteria on the field that is being used for the combobox?

The problem is in the form - which is only the combobox - it calls out the query, which returns all of the values in the table. Its like the query isn't getting any information from the combobox.

[pbaldy]

It sounds like you want a criteria on the row source of the combo maybe? If not, I'm not clear on what the problem is.

[saseymour]

OK, I have the form working. Its the report that doesn't work. The report is supposed to call the form, which then calls the query, then the report displays the results of the query. All the form has on it is the combobox. To fix the form, I added a 'requery event' after calling the query.

I can use the form by itself and it works. But when I use the report, the form doesn't open.

I've got the on open event set to open form - with my form name and in dialog window mode. But I don't see it.... It just goes to report view, so no chance to select an item from the combobox.

I'd post the database front end here, but I think I'd be in trouble with my companies IP policy.

[saseymour]

OK, I finally figured it out, along with a couple of other issues. Not sure what the core issue was, but I put in the report an on close code that closed the form and query used to generate it, now works much better.

It also helps when you call the correct query in a form.

[pbaldy]

Glad you sorted it out. You realize that if the report is based on the query, you don't have to open the query first?

[saseymour]

I'm creating a similar report to my first ones -- the first two queries / reports were for 7 days and 30 days worth of data.

Now, when I create a report that prompts the user for a date range to enter, the form works fine - just prompts the user for the combobox input and the date range afterwards.

When the report is opened, it prompts for the combobox input (from the form that is opened using the On Open event), then prompts for the date range. The query results table shows up, but then it asks for the date range again.

Not sure why the report does this, whereas the form does not. There are no requery activities in the form, so not sure why

Any idea? I'm thinking that the fixed date ranges might do this too in the background, but since they are fixed and not prompts for user input, it doesn't appear to be an issue.

Just not sure why the report prompts twice for the date range (once after combobox is selected, once after the query results window shows up, and you have to enter the dates again to get the report results generated....

Update: when I move the "After Update" macro that opened the query, and set the query table to hidden from the combobox properties back into the form's after update event, the user is prompted to enter the start and end dates after the combobox is updated, but then also when the report is closed. The On Close event on the report is a code that just closes the query and form, as they are hidden. I cannot figure out what is causing the additional prompt for the date range when I close the report.

This is the only thing in On Close event for the report:

Private Sub Report_Close()
DoCmd.Close acForm, "frmCoater1MaintenanceSubStartEnd"
DoCmd.Close acQuery, "qryCoater1MaintenanceSubStartEnd"
End Sub


Update: I think I know why it is prompting for extra date/times - wouldn't the close command be considered an 'update'? So, that would trigger the On Update event on the form and query?

Update 2: Got it working - I changed the from using the On Update event for the form to On Click. Now works as I want it to.