Statements and lables invalid between Select case and first case?

[snicker]

Code
__________________________________________________
Private Sub Command28_Click()
Dmaxoutput = DMax("FileNum", "ClientFiles", "ClientNum = Forms![Transfer Property Wizard]![cboNewClientNumber] AND [CatNum] = '702.48'")
NewParcelNumber = 1
Address1 = Me.txtProperty_Address
Address2 = Me.txtCSZ
FYOC = Me.txtFirstProtestYear
NewClientsName = DLookup("IIf(IsNull(ClientInfo!FirstNameTwo),ClientInfo!FirstNameOne & ' ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName,ClientInfo!FirstNameOne & ' and ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName)", "ClientInfo", "[ClientNum] = Forms![Transfer Property Wizard]!cboNewClientNumber")
PINCount = DCount("[PIN]", "REPINInfo", "[PropertyAddy] = Forms![BCBClients]![BCBRETaxSubForm].Form![txtID] ")
NewClientsName = DLookup("IIf(IsNull(ClientInfo!FirstNameTwo),ClientInfo!FirstNameOne & ' ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName,ClientInfo!FirstNameOne & ' and ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName)", "ClientInfo", "[ClientNum] = Forms![Transfer Property Wizard]!cboNewClientNumber")


NewFileNumber = IIf(DCount("ClientNum", "ClientFiles", "ClientNum=Forms![Transfer Property Wizard]!cboNewClientNumber") > 9, Forms![Transfer Property Wizard]!cboNewClientNumber & "." & DCount("FileNum", "ClientFiles", "ClientNum = Forms![Transfer Property Wizard]![cboNewClientNumber]") + 1, Forms![Transfer Property Wizard]!cboNewClientNumber & ".0" & DCount("FileNum", "ClientFiles", "ClientNum = Forms![Transfer Property Wizard]![cboNewClientNumber]") + 1)
NewParcelNumber = IIf(DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") > 9, NewFileNumber & "." & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1, NewFileNumber & ".0" & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1)

Select Case iresponse
iresponse = MsgBox(Prompt:="Are you sure you want to transfer the following" & vbNewLine & _
"information to " & NewClientsName & ":" & vbNewLine & vbNewLine & _
"New File Number: " & NewFileNumber & vbNewLine & _
"Address: " & Address1 & vbNewLine & _
" " & Address2 & vbNewLine & _
"Township: " & Township & vbNewLine & _
"County: " & County & vbNewLine & _
"First year of protest: " & FYOC & vbNewLine & _
"PIN(s): " & PINCount & " accociated PIN(s)", _
Buttons:=vbYesNo + vbExclamation, Title:="Transfer Property to " & NewClientsName)
Select Case iresponse
Case vbYes
Case vbNo
End Select
End Sub
__________________________________________________

This code works fine without the Select Case. What I am trying to accomplish is to have the user presented with all of the info for a new record. I would like the user to be able to say yes or no. Is my syntax wrong? Anyone know what I'm missing?

Thx in advance
Mike

 

[WayneRyan]

Snicker,

Just off the top of my head:

Dim Dmaxoutput As Variant

Dmaxoutput = DMax("FileNum", _
                  "ClientFiles",  _
                  "ClientNum = " & Forms![Transfer Property Wizard]![cboNewClientNumber] & " AND " & _
                  "[CatNum] = 702.48)

Wayne

 

[billyr]

Folling WayneRyan's thought, I don't see where you declare iresponse either.
You have a select case statement before the msgbox statement. At that point iresponse has no value - and - you have 2 select case statements and only 1 end select. If you don't intend to act on iresponse (VbYes/VbNo), why bother with the case at all?

 

[snicker]

Following all your advise this is what I came up with. If you see anything that could cause a problem let me know, but so far it works great.

________________________________________________

Private Sub btnNext_Click()

'declarations
Dmaxoutput = DMax("FileNum", "ClientFiles", ClientNum = "Forms![Transfer Property Wizard]![cboNewClientNumber] And [CatNum] = 702.48")
NewParcelNumber = 1
Address1 = Me.txtProperty_Address
Address2 = Me.txtCSZ
FYOC = Me.txtFirstProtestYear
NewClientsName = DLookup("IIf(IsNull(ClientInfo!FirstNameTwo),ClientInfo!FirstNameOne & ' ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName,ClientInfo!FirstNameOne & ' and ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName)", "ClientInfo", "[ClientNum] = Forms![Transfer Property Wizard]!cboNewClientNumber")
PINCount = DCount("[PIN]", "REPINInfo", "[PropertyAddy] = Forms![BCBClients]![BCBRETaxSubForm].Form![txtID] ")
NewClientsName = DLookup("IIf(IsNull(ClientInfo!FirstNameTwo),ClientInfo!FirstNameOne & ' ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName,ClientInfo!FirstNameOne & ' and ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName)", "ClientInfo", "[ClientNum] = Forms![Transfer Property Wizard]!cboNewClientNumber")
NewParcelNumber = IIf(IsNull(DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber")) > 9, NewFileNumber & "." & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1, NewFileNumber & ".0" & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1)
Township = Forms![Transfer Property Wizard]!Township
County = Forms![Transfer Property Wizard]!County

' makes sure a client number is selected
If IsNull(Me.cboNewClientName) = True Then
    MsgBox "Please select the client you would like to transfer this" & vbNewLine & _
    "information to.", vbOKOnly + vbExclamation, _
    "Appropriate Value Needed."
    Me.cboNewClientNumber.SetFocus
    Exit Sub
End If

'Finds RAO File number if needed
If Not IsNull(DLookup("[FileNum]", "[ClientFiles]", "[ClientNum] = _ 
Forms![Transfer Property Wizard]![cboNewClientNumber] AND [CatNum]='702.48'")) = True Then
        NewFileNumber = DLookup("[FileNum]", "[ClientFiles]", "[ClientNum] = Forms![Transfer Property Wizard]![cboNewClientNumber] AND [CatNum]='702.48'")
    Else:
        If IsNull(DMax("FileNum", "ClientFiles", "ClientNum = Forms![Transfer Property Wizard]![cboNewClientNumber]")) = True Then
                NewFileNumber = Forms![Transfer Property Wizard]![cboNewClientNumber] & ".01"
            Else:
                     If DCount("ClientNum", "ClientFiles", "ClientNum=Forms![Transfer Property Wizard]!cboNewClientNumber") < 9 = True Then
                    NewFileNumber = Forms![Transfer Property Wizard]![cboNewClientNumber] & ".0" & DCount("ClientNum", "ClientFiles", "ClientNum=Forms![Transfer Property Wizard]!cboNewClientNumber") + 1
                     Else:
                    NewFileNumber = Forms![Transfer Property Wizard]![cboNewClientNumber] & "." & DCount("ClientNum", "ClientFiles", "ClientNum=Forms![Transfer Property Wizard]!cboNewClientNumber") + 1
                End If
            End If
    End If


'Finds New parcel number.
If Not IsNull(DLookup("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber")) = True Then
        If DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") < 9 = True Then
                NewParcelNumber = NewFileNumber & ".0" & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1
                 Else:
                NewParcelNumber = NewFileNumber & "." & DCount("CustKey", "REPropertyAddy", "CustKey = Forms![Transfer Property Wizard]!cboNewClientNumber") + 1
                End If
    Else:
        NewParcelNumber = NewFileNumber & ".01"
End If

'Creates new RAO File if Nessessary
If IsNull(DLookup("[FileNum]", "[ClientFiles]", "[ClientNum] = Forms![Transfer Property Wizard]![cboNewClientNumber] AND [CatNum]=""702.48""")) Then
    Select Case MsgBox(prompt:="A new RAO file will now be set up for " & NewClientsName & "." & vbNewLine & _
    vbNewLine & _
    "File Name:                       RAO file for " & DLookup("IIf(IsNull(ClientInfo!FirstNameTwo),ClientInfo!FirstNameOne & ' ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName,ClientInfo!FirstNameOne & ' and ' & ClientInfo!FirstNameTwo & ' ' & ClientInfo!LastName)", "ClientInfo", "[ClientNum] = Forms![Transfer Property Wizard]!cboNewClientNumber") & vbNewLine & _
    "File Number:                   " & NewFileNumber & vbNewLine & _
    "Category Name:             RAO" & vbNewLine & _
    "Category Number:         702.48" & vbNewLine & _
    "Status:                           Active" & vbNewLine & _
    "Open Date:                    " & Date & vbNewLine & vbNewLine & _
    "Would you like to automatically set up this file. (Recomended)", _
    Buttons:=vbYesNo + vbQuestion, title:="RAO File Missing")
    Case vbYes
    DoCmd.RunSQL Blah Blah
    Case vbNo
    Exit Sub
    End Select
End If

'Informs user of new info
Select Case MsgBox(prompt:="The following information will be transfered " & vbNewLine & _
            "to " & NewClientsName & ":" & vbNewLine & _
            vbNewLine & _
            "New RAO File Number:   " & NewFileNumber & vbNewLine & _
            "New Parcel Number:       " & NewParcelNumber & vbNewLine & _
            "Address:                         " & Address1 & vbNewLine & _
            "                                        " & Address2 & vbNewLine & _
            "Township:                       " & Township & vbNewLine & _
            "County:                          " & County & vbNewLine & _
            "First year of protest:      " & FYOC & vbNewLine & _
            "PIN(s):                            " & PINCount & " accociated PIN(s)", Buttons:=vbYesNo + vbInformation, title:="Transfer Property to " & NewClientsName & " Verification")
            Case vbNo
                    MsgBox "User cancelled action.", vbookonly, "Action Cancled."
            Case vbYes
             blah blah 
              docmd.runsql......
              End Select
End Sub

thx again
mike

 

[Mile-O]

snicker, when posting large sections of code (especially as large as that can you please use the [CODE] [/CODE] tags provided? Thanks. For more info...

Usually I'd say that using them makes it easier to read...