Stumped: Query asking for info twice?

[Heatshiver]

I have been redesigning an old 1997 DB, which is actually very good.

The Setup: There is a form with a drop-down box filled with names that come from a table. After selecting a name you click a button which provides a graph (report). The graph is created from a query that also includes the same names as the drop-down box.

The Issue: I have added to the existing code so that when the button is clicked, it not only opens the graph, but now closes the form and views the report instead of previewing it. I did this so that I could place buttons to navigate between the reports and forms. But now I am asked to re-insert the drop-down name in order to populate the graph, which defeats the purpose of the drop-down box.

Here is the original code:

On Error GoTo Err_comcostbblgraph_Click

Dim stDocName As String
Dim cboboxtxt As String

stDocName = "Cost per bbl graph"
cboboxtxt = Me.cbofieldname.Value

If cboboxtxt = "" Then
Beep
MsgBox "Please select a field from the drop down list", vbOKOnly, "Select field"
Exit Sub
End If
DoCmd.OpenReport stDocName, acPreview

Exit_comcostbblgraph_Click:
Exit Sub

Err_comcostbblgraph_Click:
MsgBox Err.Description
Resume Exit_comcostbblgraph_Click

Here it is with my slight additions:

On Error GoTo Err_comcostbblgraph_Click

Dim stDocName As String
Dim cboboxtxt As String
Dim WellFilter As String

stDocName = "Cost per bbl graph"
cboboxtxt = Me.cbofieldname.Value
WellFilter = "frmwellfilter"

If cboboxtxt = "" Then
Beep
MsgBox "Please select a field from the drop down list", vbOKOnly, "Select field"
Exit Sub
End If

DoCmd.OpenReport stDocName, acViewReport
DoCmd.Close acForm, WellFilter

Exit_comcostbblgraph_Click:
Exit Sub

Err_comcostbblgraph_Click:
MsgBox Err.Description
Resume Exit_comcostbblgraph_Click

Once I change: DoCmd.OpenReport stDocName, acPreview
To: DoCmd.OpenReport stDocName, acViewReport

And I click the button on the form, I get this message: "Forms!frmwellfilter!cbofieldname"
Moreorless it's re-asking for the field name from the drop-down list. If it is re-put in, there is no problem (that I can tell). If I click OK or Cancel I get a blank table.

I know the message comes from the Criteria in the Field Name property of the query table it uses to populate the graph, but I can't figure out how to get it back to the way it was with my coding additions...

Any help would be greatly appreciated. Thank you.

 

[PeterF]

Because you close the form that contains the criterea you wil get the message for the missing filter.
Simply comment out the "DoCmd.Close acForm, WellFilter" line and you're back to the original behaviour.

 

[vbaInet]

I haven't completely read your entire thread but if your query is feeding from a control in the form, the form needs to remain open. The form can be invisble but it must remain loaded.

 

[Heatshiver]

Thank you for the reply, I didn't think of placing the command at the end. I have been teaching myself how to read the code, but obviously I don't have it down-pat.

It works great now! Thanks again.

 

[vbaInet]

I didn't even notice PeterF's post. It wasn't showing up before I posted.

Glad you got it sorted.

 

[Heatshiver]

Oddly enough, it worked for this situation. And I have the EXACT situation for another button on the very same form - it just opens a different graph (form).

For some reason adding the close command to the bottom does nothing. Any ideas why?

 

[vbaInet]

I don't know what exact solution you applied. Did you go with hiding the form and keeping it loaded or you are still closing the form?

 

[Heatshiver]

I was still closing the form. I changed the code to look like this and got it to work for the first button:

On Error GoTo Err_comcostbblgraph_Click

Dim stDocName As String
Dim cboboxtxt As String
Dim WellFilter As String

stDocName = "Cost per bbl graph"
cboboxtxt = Me.cbofieldname.Value
WellFilter = "frmwellfilter"

If cboboxtxt = "" Then
Beep
MsgBox "Please select a field from the drop down list", vbOKOnly, "Select field"
Exit Sub
End If

DoCmd.OpenReport stDocName, acViewReport

Exit_comcostbblgraph_Click:
Exit Sub

Err_comcostbblgraph_Click:
MsgBox Err.Description
Resume Exit_comcostbblgraph_Click

DoCmd.Close acForm, WellFilter


But doing so for the second button did nothing...

I also tried DoCmd.OpenForm WellFilter, acHidden in both areas of where I placed the code before.

 

[Heatshiver]

I also just realized that the was I put it for the first button isn't actually working, it's just because I have Pop-up and Modal set to Yes and it was covering up the form...

 

[Heatshiver]

[SOLVED] Stumped: Query asking for info twice?

Thanks for all the help. I was able to figure out the hidden command and it works fine now.

Thanks again.

 

[vbaInet]

Re: [SOLVED] Stumped: Query asking for info twice?

Thanks for all the help. I was able to figure out the hidden command and it works fine now.

Thanks again.

Good job!