Viewing the results of a query in a form

[BajanPOET]

I want to run queries that I have saved in the Queries tab of my *.mdb file by affixing the queries to the .RecordSource of a View all form so that the same form can be used to view different sets of data. The user is to be presented with a form with option buttons where they can choose an option. I ran a Select Case statement that selects the correct code when the corresponding option button is selected - but how do I get the code to run? If I do DoCmd.OpenQuery how can I attach the result to my form? I currently have this

With Forms("View all Requisitions")
    Select Case mChosenValue
        Case 1
           .RecordSource ' = ?????
        Case 2
            .RecordSource
        Case 3
            .RecordSource
        Case 4
        Case 5
        Case 6
    End Select
  End With

What do I assign to the RecordSource of my form?

What? Who? ME????

 

[PHV]

Have you tried to simply assign the name of the query ?
With Forms("View all Requisitions")
Select Case mChosenValue
Case 1
.RecordSource = "qryReq1"
Case 2
.RecordSource = "qryReq2"
Case 3
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

I tried that, but I'm getting an error saying that the database "can't find the form 'View All Requisitions' referred to in a macro expression or Visual Basic code."

What? Who? ME????

 

[PHV]

The form must be open for your code to work ...

 

[BajanPOET]

ok, so how do I open a form using code, then? I'm trying to get a search form working, where the user can execute one of several queries by choosing the relevant option button from a group box. The queries select different sets of records that I want to be shown in another form for display.

Is there a better way to be doing this?

What? Who? ME????

 

[PHV]

Have a look at the DoCmd.OpenForm method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Oh yeah... silly me...

What? Who? ME????

 

[BajanPOET]

Right, that worked...

The last thing I need to do is to pass a parameter to a query from a form. One of my queries searches for a requisition using the Requisition number. What I want to do is have the user type the number into a textbox and then send it to my query, which I want to write in such a way that it expects an integer as a parameter. I have the query that works on its own, like when I click it in the Queries window it asks me to fill in the parameter and then shows the data

 SELECT * FROM Requisition, ITEMS WHERE ReqNo = ?

but I want to be able to fill the parameter in using my Search form, that has a txtReqNo field.

Any ideas?





What? Who? ME????

 

[PHV]

WHERE ReqNo = Forms![name of search form]!txtReqNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

thanks!

What? Who? ME????

 

[IGPCS]

why cant you create the query in sql builder and then create a form based on a query instead of a table?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[BajanPOET]

I was creating a search form that I wanted to use to run several queries that I've already developed using SQL builder... so the queries have been built and are being used elsewhere in the application. I just wanted a "central form" that would be used to fire these queries as necessary.



What? Who? ME????

 

[IGPCS]

do the queries have simular fields?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[BajanPOET]

Yeah they are diffent queries on the same two tables - a Requisitions table and the corresponding Items table that holds the details for each requisition.

What? Who? ME????

 

[IGPCS]

well you can use the same queries for a couple of forms at one time it wont conflict with each other

i beleave there is a leak about this in ms access XP but in othere versions it should work

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY