Help with On Open event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
EdS said:
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.
Click to expand...


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
 
Perfect!! Appreciate it!

Duane Hookom said:
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

--
Duane Hookom
MS Access MVP
--

EdS said:
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.
Click to expand...
Click to expand...
 
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)?

Duane Hookom said:
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

--
Duane Hookom
MS Access MVP
--

EdS said:
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.
Click to expand...
Click to expand...
 
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

--
Duane Hookom
MS Access MVP


EdS said:
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)?

Duane Hookom said:
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

--
Duane Hookom
MS Access MVP
--

EdS said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Once again, Perfect! I'm in your debt...

Duane Hookom said:
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

--
Duane Hookom
MS Access MVP


EdS said:
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)?

Duane Hookom said:
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

--
Duane Hookom
MS Access MVP
--

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top