trying to create a report base on the values from the current form and subforms

[rodmc]

Hi Folks

Im trying to create a report based on the current form and the subform contained within the main form.

Currently I have a print report button on my main form.The report is based on a parameter query which prompts for a OrderID and then displays the report. Now rather than having to prompt the user for the OrderID (which by the way is on the subform currently displayed). I want it to reference this ID field so that the user doesnt have to be prompted.

Here is how I thought I could do it:

Private Sub btnPrintRec_Click()
On Error GoTo Err_btnPrintRec_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PrintOff"
srLinkCriteria = "[PrintOrderDetails].[OrderID]=" & Me.[frmOrders].[OrderID]
DoCmd.OpenReport stDocName, acPreview

Clearly Im way off the mark, so any pointers would be greatly appreciated

Thanks

Rodmc

 

[vbaInet]

If your subform has the OrderID field, shouldn't that same OrderID field exist in your main form's record source? Isn't the subform linked to the main form via OrderID?

Have a look at this link and you can apply the same concept for opening your report:

http://baldyweb.com/wherecondition.htm

 

[rodmc]

If your subform has the OrderID field, shouldn't that same OrderID field exist in your main form's record source? Isn't the subform linked to the main form via OrderID?

Have a look at this link and you can apply the same concept for opening your report:

http://baldyweb.com/wherecondition.htm

Hi vbaInet

No the Client and Orders table have a one to many relationship and the Client table does not contain the OrderID, but obviously the Client ID is in the Orders table as a foreign key. Apologies, I probably should have mentioned that in the op

I'll look at Paul's site however and see if it helps

cheers

 

[vbaInet]

So your subform is a continous form isn't it? If the button is on the main form, how would you know which Order ID is the current Order ID since the subform no longer has focus?

Drop a button onto your subform so the user can click on the button that relates to the order to open the report. Paul's site has what you need.

Shout if you need clarification.

 

[rodmc]

So your subform is a continous form isn't it? If the button is on the main form, how would you know which Order ID is the current Order ID since the subform no longer has focus?

Drop a button onto your subform so the user can click on the button that relates to the order to open the report. Paul's site has what you need.

Shout if you need clarification.

cheers dude, just thought that myself, feel free to give me an e-slap for being so stupid

 

[vbaInet]

cheers dude, just thought that myself, feel free to give me an e-slap for being so stupid

I'll see if the site Admins can add one of those so I can drop you one. You asked for it lol Just kidding.

All you needed was a nudge in the right direction. Glad you got it sorted.