Button pushing queries

[Soulbait]

Ok I have a query set up to run from a button, but thats just not good enough...

What they want me to do is when you push the button for the query a popup box comes up with a filter option for the query

It would have the option of No filter or choosing one of 10 states this report is for. Then an ok and a cancel button

Unfortunately after about an hour of trying to figure it out I've come to the sad realization that I have no idea how to get it to do this.

If anyone could give me a hand that'd be great if not, they'll just have to live with it

Thanks
Soulbait
"Despite the cost of living, its still quite popular"
Disclaimer: Im not that good at programming anything besides HTML

 

[jbehrne]

Hi,

You could try having the button open a form that gives the user the option of choosing what they want. Then based on that decision run a query that fits the needs of the user.

Ex:

We have a table with a field called "ReportType" - use the lookup wizard and select "I will type in the values I want" the enter the 10 states and "None"

Create a form based on the table. Set the form's border style to "None". Deselect record selectors and navigation buttons. So you should have a new form that has one field that lets the user select a state or no states. Based on the value that the user picks run the query:
- Set the code on the dropbox's "After Update" event
Select Case Me!ReportType
Case State1
DoCmd.RunQuery "QueryState1"
Case State2
DoCmd.RunQuery "QueryState2"
...
Case None
DoCmd.RunQuery "NoStates"
End Select

On your other form's button that runs the query make it open the new form.

Of course, this may be to convilutated for some people

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Soulbait]

Thats what I started to work upon, but alas quiting time came along and so I went home...

I'll try your suggestion in the morning.

 

[Soulbait]

Well I must be doing something wrong, i get an error message.

here's the code for that form that popups to give me the option of which state to choise

Option Compare Database
Option Explicit


Private Sub Combo10_AfterUpdate()

Select Case "Me!StateFilter"
Case "CA"
DoCmd.RunQuery "QueryCA"
Case "Lower FL"
DoCmd.RunQuery "QueryLFL"
Case "Upper FL"
DoCmd.RunQuery "QueryUFL"
Case "IL"
DoCmd.RunQuery "QueryIL"
Case "MA"
DoCmd.RunQuery "QueryMA"
Case "Lower NY"
DoCmd.RunQuery "QueryLNY"
Case "Upper NY"
DoCmd.RunQuery "QueryUNY"
Case "PA"
DoCmd.RunQuery "QueryPA"
Case "TX"
DoCmd.RunQuery "QueryTX"
Case "VA"
DoCmd.RunQuery "QueryVA"
Case "None"
DoCmd.RunQuery "Patients_Crosstab"
End Select

End Sub

Private Sub RunQuery_Click()
On Error GoTo Err_RunQuery_Click

Dim stDocName As String

stDocName = "Patients_Crosstab"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_RunQuery_Click:
Exit Sub

Err_RunQuery_Click:
MsgBox Err.Description
Resume Exit_RunQuery_Click

End Sub

 

[Soulbait]

There I go hitting the submit button before I'm done typing

Anyways, its not apply my options.

It brings up just the plain no states query.

So apparently I'm not putting in the right options for the on click command for the button.

Thanks

 

[jbehrne]

Hi,

Try this:

Select Case Me!StateFilter
Case CA
DoCmd.RunQuery "QueryCA"
Case Lower_FL
DoCmd.RunQuery "QueryLFL"
Case Upper_FL
DoCmd.RunQuery "QueryUFL"
Case IL
DoCmd.RunQuery "QueryIL"
Case MA
DoCmd.RunQuery "QueryMA"
Case Lower_NY
DoCmd.RunQuery "QueryLNY"
Case Upper_NY
DoCmd.RunQuery "QueryUNY"
Case PA
DoCmd.RunQuery "QueryPA"
Case TX
DoCmd.RunQuery "QueryTX"
Case VA
DoCmd.RunQuery "QueryVA"
Case None
DoCmd.RunQuery "Patients_Crosstab"
End Select

You may have problems with the Names:
Lower_FL
Upper_FL
Lower_NY
Upper_NY

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Soulbait]

Ok its still just displaying a non filtered query, I think the code for the on click button is not right.

let me know what ya think.

Thanks
Private Sub RunQuery_Click()
On Error GoTo Err_RunQuery_Click

Dim stDocName As String

stDocName = "Patients_Crosstab"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_RunQuery_Click:
Exit Sub

Err_RunQuery_Click:
MsgBox Err.Description
Resume Exit_RunQuery_Click

End Sub

 

[jbehrne]

Hi,

In my example above, each DoCmd.RunQuery is calling for a different query (the code doesn't apply any kind of filter - you could have it do that but you will have to create the code on your own...).

For instance: QueryCA would display the records pretaining to "CA" - in the query design view drag the state name field to the list of fields being displayed in the query - the set the Criteria for that field by pasting in this: "CA". That way the query is hard coded to pull just the records that are for the state CA.

HTH,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Soulbait]

Ok thats how I have it set is 10 different queries, but when I click the button its not choosing the query i have specified in the list. I know there is something incorrect with the code in the On_Click function of the button that is not right. I'm missing a small piece of code that says to get the DocName from the Selection...

Not quite sure how to tell it to get the DocName from the selection either

 

[jbehrne]

Hi,

I am a little lost as to what this bit of code does:

Dim stDocName As String

stDocName = "Patients_Crosstab"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Is it running a crosstab query? If so what does the crosstab query do?

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[Soulbait]

the crosstab query was the original query i set up that did not filter states.

Like I said, I am most likely putting the code into the wrong spot

that chunk of code is all associated to the OK button, while the Select Case code is all associated with the combo box, I've got it pretty well fubar'd i think. I can send the database to you later tonight if you still want to see what I'm tryin to do.

 

[jbehrne]

Hi Soulbait,

I would be happy to check out your db. Just send a zipped copy (with any sensitive records removed) to me at:

jbehrne@hotmail.com

- In the e-mail just give me an explanation of what you want and what it is doing (referencing the form names). I will be glad to check it out and fix it,

jbehrne If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations