Report/Parameter Query started from a Form w/o Input Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to have my users click a button to generate a report using 2 combobox values to query a table. I would really like them not to have type the values the selected on the form into the Parameter Input Boxes for the query. The Input box display's the name of the control it is supposed to be reading. I am hoping I am close and there is just some syntax that is keeping the query from understanding what I want. The query and report work fine if I type in the values.
 
please post the syntax you're using in the query to reference the combo
boxes. do a copy/paste from query design view directly into your post, so we
can all see exactly what Access is "seeing" when it attempts to run the
query.


JimL said:
I would like to have my users click a button to generate a report using 2
Click to expand...
combobox values to query a table. I would really like them not to have type
the values the selected on the form into the Parameter Input Boxes for the
query. The Input box display's the name of the control it is supposed to be
reading. I am hoping I am close and there is just some syntax that is
keeping the query from understanding what I want. The query and report work
fine if I type in the values.
 
'Dim strWhere As Strin
'strWhere = "(((tblKnowledge.MachineType)=[frmKnowledge]![cmbMachineType].[value]) AND ((tblKnowledge.SubAssembly)=[frmKnowledge]![cmbSubAssembly].[value]))

strQryMachType = cmbMachineType.Valu
strQryAssy = cmbSubAssy.Valu

Dim strWhere As Strin
strWhere = "(((tblKnowledge.MachineType)=strqrymachtype) AND ((tblKnowledge.SubAssembly)=strqryassy))

DoCmd.OpenReport "rptknowledge", acViewPreview, "qryknowledge", strWher
Debug.Print strQryMachTyp
Debug.Print strQryAss

These are in the click event for a button on the form. You can see I have tried a couple of different versions. One uses public variables. I have also tried running it through macros.
 
JimL said:
'Dim strWhere As String
'strWhere =
Click to expand...
"(((tblKnowledge.MachineType)=[frmKnowledge]![cmbMachineType].[value]) AND
((tblKnowledge.SubAssembly)=[frmKnowledge]![cmbSubAssembly].[value]))"
strQryMachType = cmbMachineType.Value
strQryAssy = cmbSubAssy.Value


Dim strWhere As String
strWhere = "(((tblKnowledge.MachineType)=strqrymachtype) AND ((tblKnowledge.SubAssembly)=strqryassy))"

DoCmd.OpenReport "rptknowledge", acViewPreview, "qryknowledge", strWhere
Debug.Print strQryMachType
Debug.Print strQryAssy

These are in the click event for a button on the form. You can see I have
Click to expand...
tried a couple of different versions. One uses public variables. I have also
tried running it through macros.

The proper way to reference a form control is...

Forms!NameOfForm!NameOfControl

You also cannot refer directly to variables in a where clause of OpenReport or
in a query. You would need a function that returned the value of the variable.
In both cases you could delimit your statement so that the where clause ends up
with the *value* of the form reference or variable rather then the *name* of the
form reference or variable.

simple example with a numeric value:

Instead of...
strWhere = "[SomeField]=Forms!NameOfForm!NameOfControl"
use
strWhere = "[SomeField]=" & Forms!NameOfForm!NameOfControl
 
Back
Top