end select without select case error (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 15:51
Joined
Dec 8, 2007
Messages
163
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).

Code:
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

Super Moderator
Staff member
Local time
Today, 15:51
Joined
Sep 12, 2006
Messages
15,613
Code:
 '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

Registered User.
Local time
Today, 15:51
Joined
Dec 8, 2007
Messages
163
Don't worry, I've fixed it - I'd missed an end if statement.
 

Mr_Si

Registered User.
Local time
Today, 15:51
Joined
Dec 8, 2007
Messages
163
Code:
 '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

Premier Pale Stale Ale
Local time
Today, 11:51
Joined
Oct 17, 2012
Messages
3,276
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.
 
Last edited:

Users who are viewing this thread

Top Bottom