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

How to insert a default parameter value in VB OR use another function in report query

P: 1
Hi,

I searched online for some similar issues that I am facing but was not able come up with anything. I am fairly new with Access (2 months experience) and I am trying to remove a message stating, ENTER PARAMETER VALUE when I am generating a report. I know that the culprit lies in the query stored in the report. I have entered data in the CRITERIA (entered forms!frmproducts!lngproductID) window and OR (entered forms!frmproduser!lngproductID) window. The problem is that if one is filled with a value, the other one asks for a value through an ENTER PARAMETER VALUE window.

I would like to avoid using two reports (one for each form) to solve the problem by only have one criteria filled in each report query.

There are two ways I could think of to solve the problem:

First way: using VB to detect and automatically fill in a value of '0' (zero) when the form frmproducts is closed on the same computer.

I have two forms that print to the exact same report:
1) forms!frmproducts!lngproductID - for administrator
2) forms!frmproduser!lngproductID - for product manager (limited data changes and access).

Below is the current VB code from print command button on the form, FRMPRODUSER.

*** BEGIN VB CODE *****
Expand|Select|Wrap|Line Numbers
  1. Private Sub Preview_Click()
  2. On Error GoTo Err_Preview_Click
  3.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  4.     If IsNull(Me![LngProductID]) Then
  5.         MsgBox "Please enter the product data in the form."
  6.     Else
  7.         MsgBox "Opening default label, 4x3, to preview for printing."
  8.         DoCmd.OpenReport "rpt4x3StdLabel", acPreview, , "Forms![frmProdUser].[lngProductID]=" & Me![lngProductID]
  9.     End If
  10.  
  11. Exit_Preview_Click:
  12.     Exit Sub
  13.  
  14. Err_Preview_Click:
  15.     If Err <> 2501 Then
  16.         MsgBox Err.DESCRIPTION
  17.     End If
  18.     Resume Exit_Preview_Click
  19. End Sub
*** END VB CODE ***

Secondly, is there a function that would be satisfied if only one form forwards the data via VB? I would be entering the parameters in teh CRITERIA box in the report query.

I hope that I am making sense. If not, feel free to clarify. I am self-taught on Access (by way of books and reading online), so I am not quite familiar with the common terms you all use.

Any help would be greatly appreciated.

-Eric
Dec 5 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,494
This is probably easier than you think.
But, to guide you through it I'll need the SQL of the query your report uses.
I think it's a great idea to use the same report flexibly - I try to do that all the time myself.
Your stLinkCriteria is definitely wrong!
What it should be I can only tell with access to the reports query SQL.
In outline, though, it should say something like :
Expand|Select|Wrap|Line Numbers
  1. "([ProductID]=" & Me.[lngProductID] & ")"
Assuming the query has a field being filtered called [ProductID].
Assuming also (from name lngProductID) that field is numeric.
Dec 5 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
I'm not sure what your second question was about.
If it still needs an answer then ask me again (perhaps using different wording).
Dec 5 '06 #3

Post your reply

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