Error on opening report from form

  • Thread starter Thread starter NeoFax
  • Start date Start date
N

NeoFax

Below is the code I am using to open a report based on criteria from
three comboboxes. The problem is that the report errors as the
WhereCondition is wrong. Here is the code:

Private Sub btnJC_OpenFull_Click()
On Error GoTo Err_btnJC_OpenFull_Click
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(cmbHelo) Then
stLinkCriteria = "[Stage#]" & "=" & """ & cmbStage & """ &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbStage) Then
stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbResp) Then
stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" & "[Stage#]" &
"=" & """ & cmbStage & """
Else
stLinkCriteria = "[HELO#]" & "=" & """ & cmbHelo & """ & "[Stage#]"
& "=" & """ & cmbStage & """ & "[Responsibility]" & "=" & """ &
cmbResp & """
End If

stDocName = "rptJC_OpenFull"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btnJC_OpenFull_Click:
Exit Sub

Err_btnJC_OpenFull_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnJC_OpenFull_Click

End Sub

Any help would be appreciated. Thanks!

Terry
 
Use either a Debug.Print, MsgBox or single step to see what you are actually
putting in your WhereCondition string.
Below is the code I am using to open a report based on criteria from
three comboboxes. The problem is that the report errors as the
WhereCondition is wrong. Here is the code:

Private Sub btnJC_OpenFull_Click()
On Error GoTo Err_btnJC_OpenFull_Click
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(cmbHelo) Then
stLinkCriteria = "[Stage#]" & "=" & """ & cmbStage & """ &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbStage) Then
stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbResp) Then
stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" & "[Stage#]" &
"=" & """ & cmbStage & """
Else
stLinkCriteria = "[HELO#]" & "=" & """ & cmbHelo & """ & "[Stage#]"
& "=" & """ & cmbStage & """ & "[Responsibility]" & "=" & """ &
cmbResp & """
End If

stDocName = "rptJC_OpenFull"

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btnJC_OpenFull_Click:
Exit Sub

Err_btnJC_OpenFull_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnJC_OpenFull_Click

End Sub

Any help would be appreciated. Thanks!

Terry
Click to expand...
 
Use either a Debug.Print, MsgBox or single step to see what you are actually
putting in your WhereCondition string.




Below is the code I am using to open a report based on criteria from
three comboboxes.  The problem is that the report errors as the
WhereCondition is wrong.  Here is the code:
Click to expand...
Private Sub btnJC_OpenFull_Click()
On Error GoTo Err_btnJC_OpenFull_Click
Dim stDocName As String
Dim stLinkCriteria As String
Click to expand...
If IsNull(cmbHelo) Then
 stLinkCriteria = "[Stage#]" & "=" & """ & cmbStage & """ &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbStage) Then
 stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbResp) Then
 stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" & "[Stage#]" &
"=" & """ & cmbStage & """
Else
 stLinkCriteria = "[HELO#]" & "=" & """ & cmbHelo & """ & "[Stage#]"
& "=" & """ & cmbStage & """ & "[Responsibility]" & "=" & """ &
cmbResp & """
End If
Click to expand...
stDocName = "rptJC_OpenFull"
Click to expand...
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Click to expand...
Exit_btnJC_OpenFull_Click:
   Exit Sub
Click to expand...
Err_btnJC_OpenFull_Click:
   MsgBox Err.DESCRIPTION
   Resume Exit_btnJC_OpenFull_Click
Click to expand...
End Sub
Click to expand...
Any help would be appreciated.  Thanks!
Click to expand...
Terry
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200803/1- Hide quoted text -

- Show quoted text -
Click to expand...

Here is what I get when I click the button:

"[HELO#]=14704 And [Stage #]=" & cmbStage & " And [Lead]=" & cmbResp &
""

This is what it should look like:

"[HELO#]=14704 And [Stage #]="4FL" And [Lead]="Flightline""

The problem is the triple quotation marks. How do I get quotation
marks to show up in VBA?
 
Use either a Debug.Print, MsgBox or single step to see what you are actually
putting in your WhereCondition string.
Click to expand...
NeoFax said:
Below is the code I am using to open a report based on criteria from
three comboboxes.  The problem is that the report errors as the
WhereCondition is wrong.  Here is the code:
Private Sub btnJC_OpenFull_Click()
On Error GoTo Err_btnJC_OpenFull_Click
Dim stDocName As String
Dim stLinkCriteria As String
If IsNull(cmbHelo) Then
 stLinkCriteria = "[Stage#]" & "=" & """ & cmbStage & """ &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbStage) Then
 stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" &
"[Responsibility]" & "=" & """ & cmbResp & """
ElseIf IsNull(cmbResp) Then
 stLinkCriteria = "[HELO#]" & "=" & """&cmbHelo&""" & "[Stage#]"&
"=" & """ & cmbStage & """
Else
 stLinkCriteria = "[HELO#]" & "=" & """ & cmbHelo & """ & "[Stage#]"
& "=" & """ & cmbStage & """ & "[Responsibility]" & "=" & """ &
cmbResp & """
End If
stDocName = "rptJC_OpenFull"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_btnJC_OpenFull_Click:
   Exit Sub
Err_btnJC_OpenFull_Click:
   MsgBox Err.DESCRIPTION
   Resume Exit_btnJC_OpenFull_Click
End Sub
Any help would be appreciated.  Thanks!
Terry
Click to expand...
Click to expand...
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200803/1-Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Here is what I get when I click the button:

"[HELO#]=14704 And [Stage #]=" & cmbStage & " And [Lead]=" & cmbResp&
""

This is what it should look like:

"[HELO#]=14704 And [Stage #]="4FL" And [Lead]="Flightline""

The problem is the triple quotation marks.  How do I get quotation
marks to show up in VBA?- Hide quoted text -

- Show quoted text -
Click to expand...

I figured it out. I just had to change the quotes to triple for begin
and quadruple for end. Now to figure out how to build the
strlinkcriteria if they don't select certain data in the comboboxes.
 
You are on your way NeoFax. Excellent!
On Mar 13, 2:52 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:
Click to expand...
[quoted text clipped - 64 lines]
- Show quoted text -
Click to expand...

I figured it out. I just had to change the quotes to triple for begin
and quadruple for end. Now to figure out how to build the
strlinkcriteria if they don't select certain data in the comboboxes.
Click to expand...
 
Back
Top