I have a problem w/ the 'DoCmd.OpenReport cmd. 3

[jwa6]

this is pretty straight forward.
I want to open a report based on the same qry as the form. I am using the form filter values ( which work) to open a report / the same record set - which isnt working.

this is the command.

DoCmd.OpenReport "rptqCBoxes_w_Years_exp2", acViewPreview, "", "\OffTypeDesc]=\Forms]!\fCBox_Search2]!\cboOffType] And \FileTypeDesc]=\Forms]!\fCBox_Search2]!\cboFileType]", acNormal

any suggections out there in the ether?
seems that having 2 data fields in the DoCmd.OpenReport cmd. is the issue as i have heard other users having this same issue

thanks

jim

 

[jrbarnett]

Not sure if this is accidental in typing the data into this posting, but the \ characters should be an open square bracket ([). Change that and try again, post your results.

John

 

[jwa6]

I noticed that as well

its something w/ this editor that put those in
but thanks for noticing

jim

 

[TheAceMan1]

How are ya jwa6 . . .

Thats some editor ya got there!

Try this:

[blue]   Dim frm As Form, Criteria As String
   
   Set frm = Forms!fCBox_Search2
   
   Criteria = "([OffTypeDesc] = '" & frm!cboOffType & "') And " & _
              "([FileTypeDesc] = '" & frm!cboFileType & "')"

   DoCmd.OpenReport "rptqCBoxes_w_Years_exp2", acViewPreview, , Criteria, acNormal

   Set frm = Nothing
[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jwa6]

good suggestion


There are now 3 records on the form( filtered) I have copied your code in - fired off the report - but only 2 of the 3 records show. Same results as the code I was trying before

I have heard of others having issues w/ a 2 parm docmd.

thanks for your effort.

jwa

 

[tigersbh]

If either of those fields have a single quote in the data, you need to change your code. Wherever you have a single quote, replace it with 2 double-quotes:

Criteria = "([OffTypeDesc] = """ & frm!cboOffType & """) And " & _
"([FileTypeDesc] = """ & frm!cboFileType & """)

 

[jwa6]

neither have a quote in the field

but thanks
Ill keep this for future reference
Seems using 2 feilds is a no no in Access based on this board and ITtoolbox as well.

Access is a funny beast -- I have problems using the wizards to open a form ( single record) based on a subform.

I was thinking of writing a back end qry - putting =form field in the qry - then another qry based on that and one more = formfield as the report qry

 

[jrbarnett]

jwa6,

If you create a query based on the SQL for this report, and substitute the form values for data on this criteria in the where clause, does it show all 3 records that you would expect when run?

John

 

[jwa6]

yes it does show the correct records

 

[jrbarnett]

OK try a slight variation on TheAceMan1's code:

Dim Criteria As String
      
   Criteria = "([OffTypeDesc] = '" & Forms!fCBox.Form!cboOffType & "') AND " 

   Criteria = Criteria & "([FileTypeDesc] = '" & Forms!fCBox.Form!cboFileType & "')"

   DoCmd.OpenReport "rptqCBoxes_w_Years_exp2", acViewPreview, , Criteria, acNormal

John

 

[jwa6]

got it!

thanks john for the code

Dim Criteria As String

Criteria = "([OffTypeDesc] = '" & Forms!fCBox.Form!cboOffType & "') AND "

Criteria = Criteria & "([FileTypeDesc] = '" & Forms!fCBox.Form!cboFileType & "')"

DoCmd.OpenReport "rptqCBoxes_w_Years_exp2", acViewPreview, , Criteria, acNormal

jwa