Opening report using WHERE CLAUSE from a click of button

babui386

Registered User.
Local time
Today, 23:57
Joined
Feb 25, 2013
Messages
22
Hi

I am trying to pass value from a button to my report using Table Culum called FormID = 1 or 2 or 3.

In the Report I used Recorsource SQL as below -

SELECT tblHelp.FormID, tblHelp.HelpID, tblHelp.HelpCategory, tblHelp.HelpFormPageName, tblHelp.HelpQuestion, tblHelp.HelpAnswer, tblHelp.HelpImageLink
FROM tblHelp
WHERE (((tblHelp.[FormID])=[Forms]![rptHelp]![FormID]));

When I open the report directly, it ask for Parameter value and if I enter value = 1 or 2 or 3 (FormID), it shows all FormID which has value=1, which I was expecting.

In my form, I used a button which contains below vba -

Private Sub lblhelp_Click()

formnum = 1
' change the 1 above to the correct FormID value

DoCmd.OpenForm "rptHelp", , , "FormID=" & formnum
End Sub

I am getting below ERROR -

The form name 'rptHelp' is misspelled or refers to a form that doesnt exist

And in DEBUG highlight -

DoCmd.OpenForm "rptHelp", , , "FormID=" & formnum

Could you please help to work it around?

Regards

Babui386
 
Looks like if the report is actually a report then you should be using DoCmd.OpenReport!
 
Hi CJ thanks for looking into it....

Yes it worked with

DoCmd.OpenReport "rptHelp", , , "FormID=" & formnum

Its asking for PARAMETER VALUE and also its going to PRINTER directly, where I do not want to print, I want view on screen.

Any sugestion?
 
use DoCmd.OpenReport "rptHelp",acViewPreview , , "FormID=" & formnum

to open for screen preview (default is acViewNormal which goes to printer)

With regards parameter value I need to know the exact message - it implies you have something in the query used by the report that needs to be completed - for example you may think it is taking a value from the form, but because of a typo it isn't or maybe the form is closed
 
Hi CJ,

with use of
DoCmd.OpenReport "rptHelp",acViewPreview , , "FormID=" & formnum

It does pull the records in Preview mode, it ask me to enter parameter value which is FormID=1 or 2 or 3.

As I already said the form I am working on is relate to all records in FormID=1, is there anyway I can PRE enter the value into vb that all records contain FormID=1 to pull and so on for the other forms value 2 or 3.

It seems even entering the parameter value 1 only pulls the record contain value 1, but I try to enter value=2 or value=3 no records, where table contain records.

In the Form when I click button this appears -
Enter Parameter value window -

Forms!rptHelp!FormID

Any advise?
 
The report is looking for your form Forms!rptHelp!FormID which is not open when the report is opened.

Since you are sending the filter when you open the report you do not need this in the form query - open the form query and delete the criteria Forms!rptHelp!FormID
 
sorry forgot this bit

It seems even entering the parameter value 1 only pulls the record contain value 1, but I try to enter value=2 or value=3 no records, where table contain records.

Reason is your filter in DoCmd.OpenReport "rptHelp",acViewPreview , , "FormID=" & formnum

is already filtering records for form 1 so you now have a filter which says where form =1 and form=2 and this will clearly be nothing!

Fix as per last post and this problem will be solved as well
 
Hi CJ,

That's great, its working fine.....

The report is opening into PRINT PREVIEW mode with zoom in out view, is there anyway I can make normal preview mode without printing?


Thanks.

Babui386
 

Users who are viewing this thread

Back
Top Bottom