One Form, Two Queries?

[uncletreetrunk]

Hi all,

I am relatively new to access and I was wondering if I could run two queries off of one form. i.e. based on user selected fields run query 1 or query 2

I am running two queries because query 2 has a table relationship, but not all the values have partners so the maximum number of results is far less running query 2.

Or should I just run two separate forms?

Thanks!

[JoeM]

You could create an IF statement in VBA that will run Query1 or Query2, depending on your selection, i.e.

If Me.strSelection=1 Then
   DoCmd.OpenQuery "Query1", acViewNormal, acEdit
Else
   DoCmd.OpenQuery "Query2", acViewNormal, acEdit
End If

[RayMilhon]

The short answer is yes. However, it really depends on exactly what you're trying to do. For example I have a Reporting Database that links to SQL Server Data. There are about a dozen Reports in this Database. I have a form that uses a combo box to list all of the reports and then depending on which report is selected the criteria elements are displayed within the form the user enters their criteria and there are 2 command buttons 1 to display the report in print preview and 1 to send the report directly to the printer without displaying on the screen.

[uncletreetrunk]

Hey JoeM,

I have a button with VBA command similar to that but when I run it I get Error 3071. "too complex to be evaluated" Is there a limit to how many fields you can pass?

[uncletreetrunk]

Hey RayMilhon,

Good to hear! haha I want to have a user run report and if they check a box or enter a specification number run a different query

[RayMilhon]

so what you're looking for is a single report that could use either of 2 queries. Correct?

[uncletreetrunk]

yes that is correct! but it seems when I run the second query it's "too complicated" is there a way to get around it or a limit to how many fields I can pass to a query?

[RayMilhon]

A method I've used in the past.

In the load Event of the report First check to be sure that the Form is loaded.

   

If not isformopen("yourformname") Then
         MsgBox "The Form Your Form Name is not open please open the form and re-run the report",vbokonly,"Missing Form Error"        
         cancel = true
   Else
        If isnull(forms!yourformname!criteriafield)  Then
              me.recordsouce = "Query1"
        Else
              me.recordsource = "query2"
        End if
   End if

In a module unclude the following function.

Function isformopen(strfname as string) as integer
   'This function returns true if a form is open:
   'This function from the Access 2007 Bible

   dim i as integer
   'Assume False;
   isformopen = false
   For i = 0 to forms.count -1
        If Forms(i).name = strfname Then
           isformopen = true
           exit function
        end if
  next

End function

[RayMilhon]

Left off 1 end if in the first code section.

[uncletreetrunk]

mmm I think you're okay on your "end if"s
I'll give the code a shot and let you know thanks so much!

[uncletreetrunk]

Whoops what I really wanted was 1 form that could run either query, but the next question would be to print 1 report from both queries. So thank you for your esp haha

[RayMilhon]

Same principle applies the diffenece being that you would have a command button on the form and put very similar code in the on click event of the command button.

[uncletreetrunk]

Hm... alright I'll give it a shot! haha thanks for all the help Ray!

[RayMilhon]

Anytime. My way of passing on all the help I've gotten over the years.