trying to use a select case but having problems

[Jon123]

here is the code

Private Sub WCReport_Click()
    Select Case [var3]
 '"""""""""""""""""""""""" DPN """""""""""""""""""""""""""""""""
    Case "DPN"
      DoCmd.SetWarnings False
      Dim stDocName As String
      stDocName = "Qry-makewcreporttableDPN"
      DoCmd.OpenQuery stDocName
      DoCmd.SetWarnings True
        If DCount(" * ", "Qry-Testzeroreport") = 0 Then
         MsgBox " There is no data for this time period to report. Please re-enter your parameters"
        Exit Sub
        Else
           Dim stDocName1 As String
           stDocName1 = "Rpt-DPN WC"
           DoCmd.OpenReport stDocName1, acPreview
        Exit Sub
        End If
'"""""""""""""""""""""""""" Radiance Plus """"""""""""""""""""""""""""""""
    Case "Radiance Plus"
      DoCmd.SetWarnings False
      Dim stDocName4 As String
      stDocName4 = "Qry-makewcreporttableRTP"
      DoCmd.OpenQuery stDocName4
      DoCmd.SetWarnings True
        If DCount(" * ", "Qry-Testzeroreport") = 0 Then
         MsgBox " There is no data for this time period to report. Please re-enter your parameters"
        Exit Sub
        Else
           Dim stDocName5 As String
           stDocName5 = "Rpt-DPN WC"
           DoCmd.OpenReport stDocName5, acPreview
        Exit Sub
        End If
'"""""""""""""""""""""""""""""" CASE ELSE """"""""""""""""""""""""""""""""""""""""
      Case Else
            MsgBox "Please select a valid Report to open!", vbCritical, "Invalid Form"
            Exit Sub
    End Select
End Sub

when the case ='s Radiance Plus I get an error "can not define field more than once" but if its DPN it works ok


Thanks

jon

 

[wazz]

i would guess it has to do with Qry-Testzeroreport. judging by the name of the query, it's looking at reports and checks for data. and by the error description it looks like you might have the same field in the query more than once, which you can't do. you'll have to rename each field to be unique.

btw, do you know about the NoData event? look that up and see if it might help. it might not in this case but it can help.

i would consider renaming a few things and putting them at the top, like

stQryDPN = "Qry-makewcreporttableDPN"
stQryRTP = "Qry-makewcreporttableRTP"

stRptDPN = "Rpt-DPN WC"

hmm, i see you've assigned stDocName5 the same as stDocName1 (very difficult to see that the way all the variables are hidden within the code). did you mean to do that?