By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Limit cmdOpenReport to single record set from a form

P: 34
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReportPrintPreview_Click()
  2. On Error GoTo Err_cmdReportPrintPreview_Click
  4.     Dim strDocName As String
  5.     Dim strWhere As String
  6.     strDocName = "rptSoapPelletCoA"
  7.     strWhere = "Material_IDH='" & Me.Material_IDH.Value & "'"
  8.     DoCmd.OpenReport strDocName, acPreview, , strWhere
  11. Exit_cmdReportPrintPreview_Click:
  12.     Exit Sub
  14. Err_cmdReportPrintPreview_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_cmdReportPrintPreview_Click
  18. End Sub
Nov 26 '13 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 5K+
P: 5,397
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.
Nov 26 '13 #2

P: 34
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCoAHistory.[Material IDH]
  2.    , tblSoapPelletSpecs.Description
  3.    , tblCoAHistory.[Production Date]
  4.    , tblCoAHistory.[Batch Code]
  5.    , tblCoAHistory.[NaCl,%]
  6.    , tblCoAHistory.[Glycerin,%]
  7.    , tblCoAHistory.[Sorbitol,%]
  8.    , tblCoAHistory.[Alkalinity,%]
  9.    , tblCoAHistory.[Free Fatty Acid,%]
  10.    , tblCoAHistory.[Moisture,%]
  11.    , tblCoAHistory.Color
  12.    , tblCoAHistory.Odor
  13.    , tblCoAHistory.[Foreign Matter]
  14. FROM tblSoapPelletSpecs 
  15.    INNER JOIN tblCoAHistory 
  16.       ON tblSoapPelletSpecs.[Material IDH] 
  17.          = tblCoAHistory.[Material IDH];
Nov 26 '13 #3

Expert Mod 5K+
P: 5,397
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 = "

"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!
Nov 26 '13 #4

P: 107

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:

Expand|Select|Wrap|Line Numbers
  1. strWhere = "Material_IDH='" & Me.Material_IDH.Column(1) & "'"
Where column 1 is the actual value you want to use for the displayed value.

Nov 30 '13 #5

Expert Mod 5K+
P: 5,397
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.
Nov 30 '13 #6

Post your reply

Sign in to post your reply or Sign up for a free account.