Queries and VBA

[stuartkeith]

I am still having issues with getting a qbf working correctly! I have been told to use VBA to pass the criteria to the query? so far i have been trying this

Dim strCrit As String
  If IsNull(Me.Server) Then
     ' proceed
  Else
     strCrit = strCrit & " And [Server type] = '" & Me.Server & "'"
  End If
  If IsNull(Me.Ram) Then
     ' proceed
  Else
     strCrit = strCrit & " And [Ram] = " & Me.Ram
  End If
... etc
 
  Select Case Me.OutputType
      Case 1    ' report
            DoCmd.OpenReport "YourReport", , , strCrit
      Case 2    ' form
            DoCmd.OpenForm "YourForm", , , strCrit
  End Select

But how do I pass this into a query. I tried doing this.

creating a module and global defining each varible that I want to use eg.

Global criServertype as string

when the run query button is pressed, it then passes the value of the text boxes to each global value (eg server type is dell1850, so module1.criServertype now = dell1850)
fine.

I have then defined a function for eachtext box as well eg

function funServertype()
funservertype=module1.criservertype

the in the query critera cell enter

=funServertype()

However it either runs but no results are displayed.
Get no function defined (then change to what form its on)
Get out of stack space.

I was ment to finish this last friday and this is all im stuck on!

 

[Bat17]

Unless I am missing something, you don't need to pas anything to the query, the strcriteria passes the filter to the report and it does the filtering for you.
I suspect that you are not ending up with the strCrit you are expecting.

Add a debug.print strCrit statement after the last option to see what you are passing to the report.

Unless you are trimming it you clause will start with 'And' which will probably break the SQL

Peter