end select without select case error

[Mr_Si]

Hi all,

I'm getting a strange ocurrence of the error in the title when I add a couple of lopps within a select case statement.

The full code is below (including the (currently commented out) for each next code that throws the private sub in to an error).

Private Sub btnSaveClose_Click()
    Dim strINSERT As String
    Dim numBatch As Long
    Dim ctl As Control
    Dim strMsg As String
           
    'pre-cleanup
    strINSERT = ""
    numBatch = 0
           
    Debug.Print OpenArgs
           
    Select Case (OpenArgs)
        Case Is = 5
                    
            'For Each ctl In Me.Controls
            '    If IsNull(ctl) Then
            '        strMsg = strMsg & "_ " & ctl.Name & vbCrLf
            '    End If
            'Next ctl
            
            'If strMsg <> "" Then
            '    If vbOK = MsgBox("The following fields require need filling out" & vbCrLf & vbCrLf & _
            '    strMsg & vbCrLf & vbCrLf & "Do you want to continue?", vbOKOnly) Then
            '        Me.Component.SetFocus
            '        Cancel = True
            'Else
            
            If Me.Dirty Then Me.Dirty = False 'code to force a save of the record.
            
            numBatch = DMax("idsComponentBatchID", "tblComponentBatch")
            Debug.Print numBatch
                                              
            'Add the history element to the component
            strINSERT = "INSERT INTO tblComponentHistory (dtmDate, Component, History, Quantity, Batch, Comments, Supplier, blnUpdate)" _
                        & " VALUES (#" & Format(Now(), "mm/dd/yyyy") & "#, " _
                        & Me.Component & ", " _
                        & "2, " _
                        & Me.QtyReceived & ", " _
                        & numBatch & ", '" _
                        & Me.Comments & "', " _
                        & Me.Supplier & ", " _
                        & "-1);"
            
             'for debugging purposes
            
            Debug.Print "date = " & Me.BatchReceived
            Debug.Print "component = " & Me.Component
            Debug.Print "qty = " & Me.QtyReceived
            Debug.Print "batch no = " & Me.ComponentBatchNo
            Debug.Print "comments = " & Me.Comments
            Debug.Print "supplier = " & Me.Supplier
            Debug.Print strINSERT
            
            CurrentDb.Execute strINSERT
            
            'End If
            
    End Select
    
    'cleanup
    strINSERT = ""
    numBatch = 0
    
    DoCmd.Close
    
End Sub

Basically, I'm trying to add a bit of error handling functionality that checks to see if any of the form controls are empty and if so, state that they need to be filled out otherwise not commit the results to the table and exit the sub.


Can anyone throw ideas out there as to why it's giving me the error I'm getting?

 

[gemma-the-husky]

 'If strMsg <> "" Then
            '    If vbOK = MsgBox("The following fields require need filling out" & vbCrLf & vbCrLf & _
            '    strMsg & vbCrLf & vbCrLf & "Do you want to continue?", vbOKOnly) Then
            '        Me.Component.SetFocus
            '        Cancel = True
            'Else

if you uncomment this, I think you need an end if, which you do not appear to have.
That might be the issue.

Try compiling the code.
make sure you have "option explicit" declared at the top of the module.
Try adding a breakpoint and stepping through the code.

 

[Mr_Si]

Don't worry, I've fixed it - I'd missed an end if statement.

 

[Mr_Si]

 'If strMsg <> "" Then
            '    If vbOK = MsgBox("The following fields require need filling out" & vbCrLf & vbCrLf & _
            '    strMsg & vbCrLf & vbCrLf & "Do you want to continue?", vbOKOnly) Then
            '        Me.Component.SetFocus
            '        Cancel = True
            'Else

if you uncomment this, I think you need an end if, which you do not appear to have.
That might be the issue.

Try compiling the code.
make sure you have "option explicit" declared at the top of the module.
Try adding a breakpoint and stepping through the code.

Ah, yes that's what I'd found whilst searching some more. Thank you.

Btw, what does option explicit actually do?

 

[Frothingslosh]

It requires you to explicitly declare variables with a Dim statement. (Or Private, or Public, or Global, etc)

That way you don't try to work with ABCDE but accidentally put your data inside ABDCE.

If you don't use that option, then when Access runs across a variable you haven't yet declared, it immediately creates a variant with that name.