Limit cmdOpenReport to single record set from a form

[Pookaroo85]

Any help would be appreciated. I have a simple form (frmSoapPelletCoA) with a 'Report' button that I would like to print preview a single record set into a report (rptSoapPelletCoA) based on the 'Material IDH'(text field). It should go through a query named qryCoAHistory. The following code results in an Enter Parameter Value: Material_IDH pop up. I would like it to automatically go through to the report.

Private Sub cmdReportPrintPreview_Click()
On Error GoTo Err_cmdReportPrintPreview_Click
 
    Dim strDocName As String
    Dim strWhere As String
    strDocName = "rptSoapPelletCoA"
    strWhere = "Material_IDH='" & Me.Material_IDH.Value & "'"
    DoCmd.OpenReport strDocName, acPreview, , strWhere
 
 
Exit_cmdReportPrintPreview_Click:
    Exit Sub
 
Err_cmdReportPrintPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdReportPrintPreview_Click
 
End Sub

[zmbd]

You do not state what, if anything, you are getting now.

Please look at line 7
strWhere = "Material_IDH='" & Me.Material_IDH.Value & "'"

Unless Me.Material_IDH.Value is a string/text then you can and should remove the single quotes
strWhere = "Material_IDH=" & Me.Material_IDH.Value

Also you have a "Material_IDH pop up" you need to go back to the table/query that the report is based on and do two things:

1) Check that the field is actually there (you'd be amazed howmany times in a query the field was omitted by accident!)
I don't suspect this is the issue; however, easy enough to double check.
2) Check that "Material_IDH" is actually spelled that way...usually, now days, case doesn't matter; however, I try to match the case anyway (old school habits are hard to break).

Insert between line7 and line8
debug.print "strWhere = " & strWhere
run your code press <ctrl><g> the immediates window in the VBE will open, please take note of the resulting string. and post it back here.

If the Report is based on a query, please open the query in design view, right click on an empty space in the tables area, selct SQL view from the popup.

Paste that text into a post in this thread, select the pasted text in the post and click on the [CODE/] formatting button in the toolbar.

[Pookaroo85]

Material_IDH is a text field.

Yes, Material IDH is there and spelled correctly.

<ctrl><g> came up with nothing in the Immediate window.

The report is based on a query. See below:

SELECT tblCoAHistory.[Material IDH]
   , tblSoapPelletSpecs.Description
   , tblCoAHistory.[Production Date]
   , tblCoAHistory.[Batch Code]
   , tblCoAHistory.[NaCl,%]
   , tblCoAHistory.[Glycerin,%]
   , tblCoAHistory.[Sorbitol,%]
   , tblCoAHistory.[Alkalinity,%]
   , tblCoAHistory.[Free Fatty Acid,%]
   , tblCoAHistory.[Moisture,%]
   , tblCoAHistory.Color
   , tblCoAHistory.Odor
   , tblCoAHistory.[Foreign Matter]
FROM tblSoapPelletSpecs 
   INNER JOIN tblCoAHistory 
      ON tblSoapPelletSpecs.[Material IDH] 
         = tblCoAHistory.[Material IDH];

[zmbd]

Material_IDH is a text field.

Sigh, was hoping for the simple fix :)

Yes, Material IDH is there and spelled correctly.

As I thought; however, it is such a common issue that it's always worth a double check.

<ctrl><g> came up with nothing in the Immediate window.

(?_?)
You did place:
debug.print "strWhere = " & strWhere
between lines 7 and 8 first?
You should have at least seen "strWhere = "

SQL:
"SELECT tblCoAHistory.[Material IDH], "

You have an underscore in Line7 of the code block in post#1
You do not have an underscore here... this would be a typo and why you are getting the prompt.
Change line7 to:"Material IDH='" & Me.Material_IDH.Value & "'"
Let us know what happens.

As I mentioned, the typo is one of the most common issues.
Please double check all of your field references in the code vs. the field names in the SQL.

BTW: Although is now allowed in code and SQL for many of the newer versions of software, it is still advisable to use the underscore and not spaces in field names and in general to not use anything except alphanumerics:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access
Using that comma and the percent sign in the field names will come back and haunt you... in the very near future!

[dgunner71]

Pookaro85,

You might also try to specify the column.

If your combobox has a different bound column than the value you're trying to run the report on, you may have trouble. It might be safer to use:

strWhere = "Material_IDH='" & Me.Material_IDH.Column(1) & "'"

Where column 1 is the actual value you want to use for the displayed value.

Gunner

[zmbd]

The solution offered by dgunner71 is not needed:

There is no need to refer to the specfic column IF the control has the bound column property set then the value of the control is the value of the bound columm. Doing this adds an additional level of complication that is unneeded.

Furthermore, this reference will not fix the issue as the field is still mis-spelled.
-z