Syntax to have a query call a form

[KelMcc]

Is it possible to have a query call a form as output? NOT create a form, but SELECT a form that already exists and populate it with data predicated from the WHERE clause?

Basically, I'm trying to work around some of the limitations of the command button wizard that only let me whittle down my criteria by 1 variable. So, I'm trying to write a query that lets me use 2 criteria then calls the appropriate form w/ the requested data.

Does this make sense?

 

[cogent1]

What you are describing is equivalent to opening a form based on a query, isn't it?

Provided that the recordsource of your form contains the name of your query,you can alter it at will by using parameters which are called when you open the form.

Is this what you want?

 

[David R]

You can specify more than one variable in the WHERE clause

"[IDField] = " & Me.IdControl & " AND [State] = '" & Me.StateControl & "'"

for example. Look up the use of the logical operators AND and OR.

 

[KelMcc]

OK, this is where my abject lack of coding skills and my only recently acquired extremely fundamental understanding of SQL are making me pay dearly!

Cogent1, you're saying to go to the properties of the form I want to call and then list the query as the record source? I don't think that will work. Basically, the query is run on a "click button" of a prior form, call it Form1. You press this button, on Form1, and I want the query to run and output the results into a form it calls, Form2. I hope that makes more sense?

David, that looks like vb code for the code builder, not query language. Or are they one in the same, they just look different depending on where they're created? I will try and use this thought w/ the code builder and see where I get.... Probably not far, LOL.

Thanks both of you. I realize I leech a lot of help off of this forum, but I am learning a lot.

 

[cogent1]

Yes, it will work. I don't exactly know what you want to do, but as David R says, you can have as many criteria as you like. Your query can get its criteria predefined from a form field (or fields), a combox (or boxes) or you can call them as parameters at run time.

If you call them as parameters, before your form opens dialog boxes will prompt you for the criteria. You enter them manually and the form filters the records accordingly.

If you predefine them, your criteria will refer to the control(s)that hold them eg Forms! Form1!MyCombo.

Yes, it will work!

 

[KelMcc]

lol, I'm sure you're right. Honestly, I think I've gotten a bit too ambitious w/ this thing.

So, Cogent, have Form1 call Form2, but in the "OnOpen", have Form2 run the query? You probably want to kick me in the teeth for not quite getting what you are saying... But I'm trying and I'm still learning what the various terms all mean and such.

Bah... now that I think about it, I'm not sure this will work for me either. I know, I know, it can work, I guess I'm saying I can't figure it out (yet). Plus, I've taken in so many different ideas from a couple different threads, that I'm confuzzling myself.

Let me mull it over over lunch and repost and try and ask a clearer question. Sorry.

 

[cogent1]

No, No, No! You're getting your knickers in a twist as we say over here!

On form 1, you have 2 combo boxes (cbo1 & cbo2). You select "Alabama" from cbo1 and "Hominy grits" from cbo2.

You then click a command button WHICH CALLS FORM2(from its On_Click event), not a query. Form 1 remains open.

Form 2 HAS THE QUERY AS ITS RECORDSOURCE.

The query has two criteria which it gets from the still-open Form1

Forms!form1!cbo1
Forms!Form1!cbo2

Your form2 opens to show all restaurants in Alabama that serve hominy grits.

Yes, it does work like that!!!!!

You've earned yourself a mess of grits if realization has dawned. Enjoy.

 

[David R]

No sweat...

We all started somewhere. Don't worry about sounding foolish; the silliest question is the one not asked.

Anyway, as cogent says, you can run a query by basing a form on it. When the form is opened, it forces the query to run in order to supply it with information. If you look at the Record Source property of a form you may see three things:
1) a table name. This is the simplest, least flexible way to use a form, but it's where most people start out.
2) a string of SQL starting with "SELECT ....". This is typically what you get when you go in and modify a Record Source already in place. Access doesn't save it as a 'Query' (called a stored querydef sometimes) unless you tell it to. However they will run faster if you go into File>Save As and do so, because saved queries are optimized by Access to use the most efficient methods.
3) a query name. This is what you'll see after the last step in 2), and queries are, for record source purposes, functionally equivalent to tables as far as Access is concerned. So this is generally the most advanced route to take, and the most flexible/efficient.

Now for flexible criteria. Again you have several choices (probably more than three, but we'll keep it simple):
1) A [Parameter]. You're probably familiar with these; you run the query and a box pops up saying "Enter Parameter Value" and gives you a box for entering text/numbers. You get one of these for each parameter you include in the query.
2) A reference to a form field. A common technique for making 'parameters' that are more user friendly is to make a small popup form, not bound to any record source at all, with several textboxes/combo boxes/checkboxes/ etc on it for specifying data to use when opening the query/form/report. If the button to run it is on this form, you can use the Me.[ControlName] terminology. If it's on a different form, say the main form that opened this popup form, then Forms!FormName.ControlName will work. An obvious benefit of a popup form is you can set 7 or 17 different parameters all one one form and click 'Run', rather than answering 7 or 17 separate questions and trying to remember the first value you put in.
3) Code, in the form of functions, looking up or calculating the value somewhere else. Dlookup functions can be used here, mathematical calculations or date manipulation, you name it. This is obviously the most technically complicated way to pass information.

Hopefully that helps a bit. Don't be afraid to post back; the new forum organization seems to lead to even better and quicker responses than before.

 

[KelMcc]

Yes, thank you both. LOL, so much so that I think I have those hominy grits on my face.

David, I've already printed your information out and have it taped up next to my monitor.

The problem is I wasn't explaining the problem thoroughly. Sorry for that. Here goes... And I'll substitute Cogent's examples for my own.

----

Form 1 has 2 boxes, a combo and a list.
Combo is all 50 states as well as a blank (choose all)
List is menu items...

You select the state you want, then the menu item, press enter.

At this point, in Cogent's example, Form 1 calls Form 2 and Form 2 runs a query to select your results. That I understand.

Now the part that I was poorly articulating is that I need whatever process occurs after you hit the "Run Query" from form 1 to ALSO pick the appropriate form (2 choices).

So, if you select Alabama and Hominy Grits, not only will it query the correct record, but (and this is where I get lost) something will also say, "oh, grits are in the grains group, so show this information in the grains form".

Had I selected California and strawberry, I'd of gotten the queried info AND the fruit form.

That's why I was saying "having a query call a form", cuz' I was conceptualizing the query not only getting the results but saying "and put them in this form IF they match this criteria"...

That's why I had that statement a couple posts up about realizing that I was maybe trying to do to much.

There are simpler ways to do this and I might just revert to those ideas. My goal was to make a form that was, while much more work for me, easy for my users.

I've been trying to write a query that did this.... I've been trying to create this out of vb code, which was really cut/pasting ideas that I get from the forums, etc.... That's where I started to get confuzzled. So, I think I need to rethink this design a little and just make it w/ a specific button for each form I want.

A couple questions:
1. What exactly does "Me." mean?
2. Those examples you gave, Dave, you use that exact verbage/syntax when making your SQL statement?

*exhales*

 

[KelMcc]

FYI, I'm re-working it. I'm gonna just have to buttons, each calling a specific form.

One of the forms called has a query as its record. That's working fine.

It does have a criteria choice of "BA" (employee), so you have on the main form, 2 buttons. You select the BA you want and click the button for the form. If you press the 2nd button it loads a form which runs a query to pull in the appropriate data.

Question, Form2 (the one that runs the query) has about 15 different fields. Do I need to include those in my query? And I do no to include an "AND" in the query to make sure I catch the BA filter, right?

 

[David R]

What synchronicity...

Actually my girlfriend just called me up asking about something extremely similar to your question. I see what you're saying but I will try to explain my perspective on it a bit more:

Form1 has a combo (showing States) and a listbox (showing Foods). Is the listbox single-select? If so we can do this with a bare minimum of code.

Assumptions: Presumably you have a table somewhere that shows that "Hominy" belongs in "Grains", "Strawberries" belong in "Fruits", etc. If not, make one! You'll thank yourself later.

Have you made a command button before that opens a form with specific criteria? If not, here's what it looks like, in the "generic":

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormName"
    stLinkCriteria = "[SomeField]=" & "'" & SomeCriteriaValueFromSomewhere & "'"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

Now we're going to tweak it a little bit.

First of all, make a hidden column on your listbox of width 0". That column should contain the name of the form, or at the least the name of the Classification ("Fruits","Grains",etc) for that entry.

And we've got to add something to make the " " (space) open all the records. Assuming that this new column contains actual form names, it'll look something like this:

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

   If IsNull(Me.ComboName) Then 'no state selected
        MsgBox "You must select one of the states from the list."
        Exit Sub
    End If

   If IsNull(Me.ListBoxName) Then 'no product selected
        MsgBox "You must select one of the products from the list."
        Exit Sub
    End If
    
    stDocName = Me.ListBoxName.Column(2)
                   'remember they count from 0 -
                   'this should be the form name column
    stLinkCriteria = "[StateField]= " & Me.ComboName 
                   'this assumes states are stored as numbers by the combo.

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

If you're not sure you've counted columns right, try this: Open your popup form in design view and open the code module associated with it. Then without closing the code module, change to regular view and pick a state and a food. Flip back to the code module and in the Immediate Window at the bottom, type ? Me.ComboName - see what comes up. Same thing for the ListBox.Column(2) - make sure you counted right. If it doesn't give the column you expected, change the column count and try again.

Now you've told it which form to open (the one that corresponds to the selected Food), and what records in that form (only those that match the state selected). If the States are stored as text, follow the first example with the single quotes around the value.

Hopefully that wasn't too confusing. I'm on my way out the door but I'll check back Monday if you're still perplexed.

 

[cogent1]

Well, you can't "call a form from a query" as such, but you can certainly call more than one form from a command button.

Now, in the table where your grits are, you may well have a column that defines grits as grain, strawberries as fruit and hamburgers as an invention of the Devil. This info can also be included in your combo, either hidden or visible in another column.. When you click to call form 2 using "grits", you can also use "grain" from the combo's other column as a criterion for another query underlying Form3. Don't let's go into the exact syntax just yet. The code (VBA program instructions) which runs when you click the button, is capable of opening the two forms sequentially, or you might even be able to display one form inside another if your data is structured to allow that.

It would be laughably easy if it weren't so complicated.