Coding with varsortby and options

[Kat]

I know the string that I am using works but I have restrutured it. What I
am trying to do is have a varsortby option and combo box drive mulitply
reports. All the quiers and reports have been tested they work. However,
when I use process button where I created the string of code to combine the
use of a option button (varsortby) and the combo box (selected item) it skips
the combo box selections. I am not sure if the varysortby field type of one
of them would effect it. But one field type I have is a autonumber. Would
that create the error?

Here is the string:

Private Sub Command25_Click()

syscursor = 5
Dim stDocName As String
Dim varhowsort As Variant
Dim vardate As Variant
Dim vardateto As Variant
Dim varFM As Variant
Dim varID As Variant
Dim varNH As Variant
Dim varTrade As Variant

varsortby = Me!Frame108
vardate = Me!Combo1
vardateto = Me!Combo6
varFM = Me!Combo2
varID = Me!Combo3
varNH = Me!Combo4
varTrade = Me!Combo5

'These reports handel all of sort by are selected and not combo box
stDocName1 = "WRAbyDate"
stDocName2 = "WRAbyFM"
stDocName3 = "WRAbyID"
stDocName4 = "WRAbyNH"
stDocName5 = "WRAbyTrade"

'These reports handel all the sort by for the date field for each combobox
stDocName6 = "WRAbyDate1"
stDocName7 = "WRAbyDate2"
stDocName8 = "WRAbyDate3"
stDocName9 = "WRAbyDate4"
stDocName10 = "WRAbyDate5"

'These reports handel all the sort by for the FM for each combobox
stDocName11 = "WRAbyFM1"
stDocName12 = "WRAbyFM2"
stDocName13 = "WRAbyFM3"
stDocName14 = "WRAbyFM4"
stDocName15 = "WRAbyFM5"

If varsortby = 1 And vardate = "" And vardateto = "" And varFM = "" And
varID = "" And varNH = "" And varTrade = "" Then GoTo Adate
If varsortby = 2 And vardate = "" And vardateto = "" And varFM = "" And
varID = "" And varNH = "" And varTrade = "" Then GoTo BFM
If varsortby = 3 And vardate = "" And vardateto = "" And varFM = "" And
varID = "" And varNH = "" And varTrade = "" Then GoTo CID
If varsortby = 4 And vardate = "" And vardateto = "" And varFM = "" And
varID = "" And varNH = "" And varTrade = "" Then GoTo DNH
If varsortby = 5 And vardate = "" And vardateto = "" And varFM = "" And
varID = "" And varNH = "" And varTrade = "" Then GoTo ETrade

'This will show the WRA report seleted by date and sorted(handles each sortby)
If varsortby = 1 And varFM = "" And varID = "" And varNH = "" And varTrade =
"" Then
DoCmd.OpenReport stDocName6, acViewPreview
GoTo SkipOver
End If
If varsortby = 2 And varFM = "" And varID = "" And varNH = "" And varTrade =
"" Then
DoCmd.OpenReport stDocName7, acViewPreview
GoTo SkipOver
End If
If varsortby = 3 And varFM = "" And varID = "" And varTrade = "" Then
DoCmd.OpenReport stDocName8, acViewPreview
GoTo SkipOver
End If
If varsortby = 4 And varFM = "" And varID = "" And varTrade = "" Then
DoCmd.OpenReport stDocName9, acViewPreview
GoTo SkipOver
End If
If varsortby = 5 And varFM = "" And varID = "" And varTrade = "" Then
DoCmd.OpenReport stDocName10, acViewPreview
GoTo SkipOver

End If

Adate: DoCmd.OpenReport stDocName1, acViewPreview
GoTo SkipOver
BFM: DoCmd.OpenReport stDocName2, acViewPreview
GoTo SkipOver
CID: DoCmd.OpenReport stDocName3, acViewPreview
GoTo SkipOver
DNH: DoCmd.OpenReport stDocName4, acViewPreview
GoTo SkipOver
ETrade: DoCmd.OpenReport stDocName5, acViewPreview
GoTo SkipOver

SkipOver: End
Exit_Command25_Click:
Exit Sub

 

[John Nurick]

1) Always declare
Option Explicit
at the beginning of each module. This forces you to declare all your
variables and thus protects you from many code errors, especially those
caused by typing mistakes.

2) It is a good idea to use descriptive names for controls. E.g. instead
of Frame108, call it something like fraSortBy. Instead of Combo6,
cboDateFrom.

3) Your code would be much easier to read - and therefore easier for you
to debug and improve - if you restructure it to make the conditional
tests and flow of control cleare. For instance, most of the present code
could be replaced by something like this. Note how the If statements are
laid out so it is easy to see the differences between them:

If (varDate = "") And (varDateTo = "") _
And (varFM = "") And (varNH = "") And (varTrade = "") Then
Select Case varSortBy
Case 1: stDocName = "WRAbyDate"
Case 2: stDocName = "WRAbyFM"
Case 3: stDocName = "WRAbyID"
Case 4: stDocName = "WRAbyNH"
Case 5: stDocName = "WRAbyTrade"
End Select

ElseIf (varID = "")_
And (varFM = "") And (varNH = "") And (varTrade = "") Then
Select Case varSortBy
Case 1: stDocName = "WRAbyDate1"
Case 2: stDocName = "WRAbyDate2"
Case 3: stDocName = "WRAbyDate3"
Case 4: stDocName = "WRAbyDate4"
Case 5: stDocName = "WRAbyDate5"
End Select
End If

'Open the report
DoCmd.OpenReport stDocName, acViewPreview

 

[Kat]

I get an error message when I input the code and made the changes. By the
way the suggestion on control names was great thanks.

The error message I get is:
Else without If

 

[John Nurick]

This probably means that you made a mistake entering the code. If the
error appeared at the ElseIf line, it's likely that you omitted the
preceding End Select or included an End If.

 

[Kat]

Thank you.

I must be the most hard headed person. Now I get an error of:

The action or method requires a Report Name argument.

Error 2497

 

[John Nurick]

This probably means that you're calling DoCmd.OpenReport without passing
its report name argument, or that the variable you think contains a
report name is in fact empty.

Set a breakpoint (see Help for details on breakpoints and debugging) at
the beginning of your code and step through it to see just what's
happening.

 

[Douglas J. Steele]

Since it appears that it's possible that the code given won't assign a
document name, you might try:

If Len(strDocName) > 0 Then
'Open the report
DoCmd.OpenReport stDocName, acViewPreview
End If

 

[John Nurick]

If Len(strDocName) > 0 Then
'Open the report
DoCmd.OpenReport stDocName, acViewPreview
End If

Good thought. But maybe:

If Len(strDocName) > 0 Then
'Open the report
DoCmd.OpenReport stDocName, acViewPreview
Else
MsgBox "No report selected."
End If

 

[Douglas J Steele]

Good thought. But maybe:

If Len(strDocName) > 0 Then
'Open the report
DoCmd.OpenReport stDocName, acViewPreview
Else
MsgBox "No report selected."
End If

What's this: adding usability? <g>

 

[Kat]

John,

I can't seam to find the issue. Can you please review the code to make sure
I am not missing something:

Private Sub WRACmd_Click()

'Dim stDocName As String
Dim varhowsort As Variant
Dim varsortby As Variant
Dim vardate As Variant
Dim vardateto As Variant
Dim varFM As Variant
Dim varID As Variant
Dim varNH As Variant
Dim varTrade As Variant


varsortby = Me!FrameSortBy
vardate = Me!CboDateFrom
vardateto = Me!CboDateTo
varFM = Me!CboFM
varID = Me!CboID
varNH = Me!CboNH
varTrade = Me!CboTrade

'This is if all empty
If (vardate = "") And (vardateto = "") And (varFM = "") And (varID = "") And
(varNH = "") And (varTrade = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyDate"
Case 2: stDocName = "WRAbyFM"
Case 3: stDocName = "WRAbyID"
Case 4: stDocName = "WRAbyNH"
Case 5: stDocName = "WRAbyTrade"
End Select
'This is if date empty
ElseIf (varFM = "") And (varID = "") And (varNH = "") And (varTrade = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyDate1"
Case 2: stDocName = "WRAbyDate2"
Case 3: stDocName = "WRAbyDate3"
Case 4: stDocName = "WRAbyDate4"
Case 5: stDocName = "WRAbyDate5"
End Select
'This is if FM Empty
ElseIf (vardate = "") And (vardateto = "") And (varID = "") And (varNH = "")
And (varTrade = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyFM1"
Case 2: stDocName = "WRAbyFM2"
Case 3: stDocName = "WRAbyFM3"
Case 4: stDocName = "WRAbyFM4"
Case 5: stDocName = "WRAbyFM5"
End Select
'This is if ID Empty
ElseIf (vardate = "") And (vardateto = "") And (varFM = "") And (varNH = "")
And (varTrade = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyID1"
Case 2: stDocName = "WRAbyID2"
Case 3: stDocName = "WRAbyID3"
Case 4: stDocName = "WRAbyID4"
Case 5: stDocName = "WRAbyID5"
End Select
'This is if NH Empty
ElseIf (vardate = "") And (vardateto = "") And (varFM = "") And (varID = "")
And (varTrade = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyNH1"
Case 2: stDocName = "WRAbyNH2"
Case 3: stDocName = "WRAbyNH3"
Case 4: stDocName = "WRAbyNH4"
Case 5: stDocName = "WRAbyNH5"
End Select
'This is if Trade Empty
ElseIf (vardate = "") And (vardateto = "") And (varFM = "") And (varID = "")
And (varNH = "") Then
Select Case varsortby
Case 1: stDocName = "WRAbyTrade1"
Case 2: stDocName = "WRAbyTrade2"
Case 3: stDocName = "WRAbyTrade3"
Case 4: stDocName = "WRAbyTrade4"
Case 5: stDocName = "WRAbyTrade5"
End Select
End If

If Len(stDocName) > 0 Then
DoCmd.OpenReport stDocName, acViewPreview
Else
MsgBox "No Report Selected."
End If


DoCmd.OpenReport stDocName, acViewPreview


End Sub

 

[John Nurick]

Not unless you tell me what it does that you think it shouldn't, or what
it doesn't do that you think it should, and what line of code the
problem occurs at.

 

[Kat]

Error seams to by pass the varysortby option. It does not seam to run. The
error i get is:
"2497"

By the way what would it cost a company to have some fix some of these errors?

 

[John Nurick]

One obvious error is at the end of your procedure:

If Len(stDocName) > 0 Then
DoCmd.OpenReport stDocName, acViewPreview
Else
MsgBox "No Report Selected."
End If

DoCmd.OpenReport stDocName, acViewPreview

The If ... End If structure which Doug suggested means that Access will
only attempt to open the report if there's a name in stDocName, and
otherwise will put up a warning message. That's fine ... but if you have
it you don't need the final line - which just tries to open the report a
second time.

What I don't understand is this: you say you're getting Error 2497. The
only way I can produce that error is by using
DoCmd.OpenReport stDocName, acViewPreview
when stDocName is empty.

But in that case the If ... End If above would cause the "No Report
Selected" message to be displayed. Are you seeing that message before
you see the one about Error 2497?

Assuming that you are seeing the "No Report Selected" message but didn't
mention it, Error 2497 molst likely means that your code isn't assigning
a value to stDocName. Most likely there's a problem in your logic, or
that the values that the code is picking up from the form don't match
what the code assumes.

Do what I suggested ages ago: set a breakpoint at the beginning of the
procedure and step through the code line by line. If you display the
Locals pane (on the View menu of the VB editor) you'll be able to see
the values of all your variables and see where things go wrong.

Also, please make sure that you have put
Option Explicit
at the beginning of the module, and confirm that you have done so.