Help with On Open event

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EdS - 21 Oct 2004 14:55 GMT

Hello.. I need my generic report to be based on certain selections made from
various combo boxes on my form. This is sample of the code for one of the
form's combo boxes:

Private Sub cbomsup1_Click(Cancel As Integer)
globalCriteria = "WHERE ([Cust]![CusID] = """ & Me.CusID & """ )"
globalCloseFilt = True
DoCmd.OpenReport "CustSurvey", acViewPreview
End Sub

Can someone help me with the report's "On Open" event code, so that the
report is based on the combo selection? I do not wish to use a macro.

Signature

Thanks. Ed

Reply to this Message

Duane Hookom - 21 Oct 2004 16:43 GMT

You can provide the where clause in the DoCmd.OpenReport. However, this text
should not include the "WHERE" and probably not the table name "[Cust]!"

Dim strWhere as String
strWhere = "[CusID] = """ & Me.CusID & """
DoCmd.OpenReport "CustSurvey", acViewPreview, , strWhere

Signature

Duane Hookom
MS Access MVP
--

> Hello.. I need my generic report to be based on certain selections made
> from
[quoted text clipped - 9 lines]
> Can someone help me with the report's "On Open" event code, so that the
> report is based on the combo selection? I do not wish to use a macro.

Reply to this Message

EdS - 21 Oct 2004 17:39 GMT

Perfect!! Appreciate it!

> You can provide the where clause in the DoCmd.OpenReport. However, this text
> should not include the "WHERE" and probably not the table name "[Cust]!"
[quoted text clipped - 16 lines]
> > Can someone help me with the report's "On Open" event code, so that the
> > report is based on the combo selection? I do not wish to use a macro.

Reply to this Message

EdS - 21 Oct 2004 17:51 GMT

If I can bother you one more time... How would I modify the strWhere to
include a second combo box selection (such as customer type)?

Reply to this Message

Duane Hookom - 21 Oct 2004 18:28 GMT

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.CusID) Then
strWhere = strWhere & " AND [CusID] = """ & Me.CusID & """
End If
If Not IsNull(Me.cboCustType) Then
'assumes CustType is numeric
strWhere = strWhere & " AND [CustType] =" & Me.cboCustType
End If

DoCmd.OpenReport "CustSurvey", acViewPreview, , strWhere

Signature

Duane Hookom
MS Access MVP

> If I can bother you one more time... How would I modify the strWhere to
> include a second combo box selection (such as customer type)?
[quoted text clipped - 19 lines]
> > > Can someone help me with the report's "On Open" event code, so that the
> > > report is based on the combo selection? I do not wish to use a macro.

Reply to this Message

EdS - 21 Oct 2004 18:59 GMT

Once again, Perfect! I'm in your debt...

> Dim strWhere as String
> strWhere = "1=1 "
[quoted text clipped - 35 lines]
> the
> > > > report is based on the combo selection? I do not wish to use a macro.

Reply to this Message

Marshall Barton - 21 Oct 2004 17:29 GMT

>Hello.. I need my generic report to be based on certain selections made from
>various combo boxes on my form. This is sample of the code for one of the
[quoted text clipped - 8 lines]
>Can someone help me with the report's "On Open" event code, so that the
>report is based on the combo selection? I do not wish to use a macro.

Either you haven't explained enough of the problem for me to
know what you want OR you don't need to mess with the
report's Open event.

Assuming the latter, try this:

Private Sub cbomsup1_Click(Cancel As Integer)
Dim stCriteria As String
    stCriteria = "CusID = """ & Me.CusID & """"
    DoCmd.OpenReport "CustSurvey", acViewPreview, , stCriteria
End Sub

If the CusID is a numeric type field, then you son't want
all those quotes:

    stCriteria = "CusID = " & Me.CusID

Signature

Marsh
MVP [MS Access]

Reply to this Message