Need Query criteria to look to a subform for a customerID 1

[cimoli]

I believe that i need to tweak a query criteria for a report.
I am trying to select a customerID (cusID) as the basis for the report.
I have a couple of subforms on a form.

The first subform has the CusID and minor data in it.
I wanted to arrow or mouse to the subform for a desired customer.
Then look at various pieces of data.

I wanted to avoid having to do a pull down for the Report Button.
I want to hit a Button that i have on the form,
to go to Print Preview for the desired customer that is shown in the first subform.


The form button has an On Click event that refers to a report. I shortened it up for here.

Private Sub cmd4245_Click()
On Error GoTo Err_cmd4245_Click
Dim stDocName As String
stDocName = "rpt4245Main"
DoCmd.OpenReport stDocName, acPreview

DoCmd.Maximize
Exit_cmd4245_Click:
Exit Sub
Err_cmd4245_Click:
MsgBox Err.Description
Resume Exit_cmd4245_Click
End Sub
-----------------

The rpt4245Main uses a query Qry4234SpringBuyCustomerAddress to use for the report letter
for the selected customer.

Inside this Qry, in the CusID field, I have a criteria as such:
[Forms]![frm4200SprintBuy]![sfrm4243Customer]![CusID]

When I hit the form button, i get the above. If i manually answer a correct CusID,
the query and report continue on correctly anyway.
The query is ignoring the info above criteria string and accepting my manual CusID input.

I hope that i explained this ok.

Thanks much.


The report in turn is based on a query

 

[MajP]

When you reference a subform
[Forms]!{YourFormName]![YourSubFormControlName].Form.YourControlName

However there is an easier way to do it. You can use 'where' parameter of the open report method

dim strWhere as string
strWhere = "CustID = " & me.CustID
'or if text strWhere = "CustID = '" & me.CustID & "'"
docmd.openreport stDocName, acPreview,,strWhere

You do not need a where on your report query

 

[MajP]

The { is a typo should be [

 

[cimoli]

MajP - I think I do not know the second method. I put your comments into my form BUTTON.
In the ON Click event. Maybe i went to the wrong spot ?

Private Sub cmd4245_Click()

On Error GoTo Err_cmd4245_Click

Dim strWhere As String

strWhere = "CustID = " & Me.CustID
'or if text strWhere = "CustID = '" & me.CustID & "'"

Dim stDocName As String

stDocName = "rpt4245Main"
DoCmd.OpenReport stDocName, acPreview, , strWhere

DoCmd.Maximize

Exit_cmd4245_Click:
Exit Sub

Err_cmd4245_Click:
MsgBox Err.Description
Resume Exit_cmd4245_Click

End Sub

 

[MajP]

http://msdn.microsoft.com/en-us/library/office/bb243764(v=office.12).aspx

If the button is on the main form and the custid is on the subform then your code would be
strWhere = "CusID = " & Me.sfrm4243Customer.CusID

Also I used 'CustID' but i see it is CusID

 

[cimoli]

I changed things like you said. I get a compile error.
But i see something. One of the fields in the underlying tblAccount is an Autonumber.
The compile error referred to the "cusID" which is at the end of the 4th row.
Where you see ....... Me.sfrm4243Customer.CusID

Private Sub cmd4245_Click()

On Error GoTo Err_cmd4245_Click

Dim strWhere As String

strWhere = "CusID = " & Me.sfrm4243Customer.CusID

'or if text strWhere = "CusID = '" & me.CusID & "'"

Dim stDocName As String

stDocName = "rpt4245Main"
DoCmd.OpenReport stDocName, acPreview, , strWhere

DoCmd.Maximize

Exit_cmd4245_Click:
Exit Sub

Err_cmd4245_Click:
MsgBox Err.Description
Resume Exit_cmd4245_Click

End Sub

 

[cimoli]

MajP - I went with your first method which puts the Forms code into the query.
I tend to prefer using plain Access things than VBA which is over my head.

So you don't need to try the 2nd way. I liked your simple first way.
It worked great.

Here is what I put in the query in the CusID field.

[Forms]![frm4200SpringBuy]![sfrm4243Customer].[Form].[CusID]

Thanks much. You made my day.