Run-time error '438' (Help with VBA coding)

[Amy Badgett]

I have been getting this error:
"object doesn't support this object or method."

The debug is saying the problem is in the series of if statements based on which product I enter in the "drpProduct " control on the form. (Lines 26-48)

Private Sub btnSave_Click()

    'Automatically sets the Location of the transaction to SSP. -AJB 4/13/11
    Me.txtLocation = "SCP"

    'Makes sure a password is entered. -AJB 4/12/11
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "You must enter an authorization password.", vbOKOnly, "Invalid Entry"
        Me.txtPassword.SetFocus
    Exit Sub
    End If
        
    'Redirects an incorrect password -JLJ 4/12/11
    If Me.txtPassword <> Me.txtPasswordLookup Then
        MsgBox "You have entered an invalid EID/password.", vbOKOnly, "Invalid Entry"
        Me.txtPassword.SetFocus
    Exit Sub
    End If

    'If the transaction is Outbound, the stock adjustment number gets a negative sign added to the front of it. -AJB 4/12/11
    If Me.drpIn_Out = "Outbound" Then
        Me.txtStockAdjust = "-" & Me.txtStockAdjust
    End If
    
    'Sets the stockID of the current transaction. -AJB 4/13/11
    If Me.drpProduct = "Sleeved DVDs" Then
        Me.txtStockNum = 1
    End If
    
    If Me.drpProduct = "Cased DVDs" Then
        Me.txtStockNum = 2
    End If
    
    If Me.drpProduct = "6x9 Envelopes" Then
        Me.txtStockNum = 3
    End If
    
    If Me.drpProduct = "Booklets" Then
        Me.txtStockNum = 4
    End If
    
    If Me.drpProduct = "Letters" Then
        Me.txtStockNum = 5
    End If
    
    If Me.drpProduct = "Bibles" Then
        Me.txtStockNum = 6
    End If
    
    'Makes sure all of the controls are filled. -AJB 4/12/11
    If Me.drpIn_Out = "" Or Me.txtLocation = "" Or Me.drpProduct = "" Or Me.txtStockAdjust = Null Or Me.txtPackerEID = "" Or Me.txtAuthEID = "" Or Me.txtTransDate = "" Or Me.txtStockNum = Null Then
        MsgBox "Make sure all fields are filled.", vbOKOnly, "Incomplete Form"
        Exit Sub
    End If
    
    'Clears Password field. - AJB 4/15/11
    Me.txtPassword = ""
    
    'Set Saved checkbox to true which will enable user to close the form. -AJB 4/13/11
    Me.chkSaved = True
    
    'Everything was saved. -AJB 4/16/11
    MsgBox "This inventory transaction was saved.", vbOKOnly, "Transaction Saved"
        
End Sub

Any kind of help would be greatly appreciated.

[NeoPa]

I'm confused as to why the line number is given as a range (26-48). In my experience only one line is highlighted when the error occurs (and that's the one we want to look at more closely).

[Amy Badgett]

For example, when I chose "Cased DVDs" line #31 is highlighted. Sorry for the confusion.

[NeoPa]

Amy, you may want to consider a more succinct version of the code. This has many benefits, but one is that it's easier to ensure no errors get into the code. This code would replace your lines #25 to #48 :

    'Sets the stockID of the current transaction. -AJB 4/13/11
    Select Case Me.drpProduct
    Case "Sleeved DVDs" 
        Me.txtStockNum = 1
    Case "Cased DVDs" 
        Me.txtStockNum = 2
    Case "6x9 Envelopes" 
        Me.txtStockNum = 3
    Case "Booklets" 
        Me.txtStockNum = 4
    Case "Letters" 
        Me.txtStockNum = 5
    Case "Bibles" 
        Me.txtStockNum = 6
    End Select

Alternatively :

    'Sets the stockID of the current transaction. -AJB 4/13/11
    With Me
        .txtStockNum = Switch(.drpProduct="Sleeved DVDs",1, _
                              .drpProduct="Cased DVDs",2, _
                              .drpProduct="6x9 Envelopes",3, _
                              .drpProduct="Booklets",4, _
                              .drpProduct="Letters",5, _
                              .drpProduct="Bibles",6)
    End With

[NeoPa]

Is there anything special about Me.txtStockNum? All else being standard, it's unusual for a TextBox control to struggle with a simple numeric assignment. I don't suppose any of your other controls have problems when any data is assigned to them from your code?

[Amy Badgett]

As far as I can tell, this is the only problem. I will implement the Case statements as you've suggested and let you know the results, and if any other controls are giving me problems.

Thank you!

[Amy Badgett]

Thank you, NeoPa, this was helpful, and the code is working now.

[NeoPa]

I'm very pleased to hear it Amy, but if I'm honest, also a little surprised. What I suggested was a reworking (albeit a little more efficiently), but not a fix to any perceived problem.

At the end of the day though, working code is generally a good thing (even if the understanding of the actual problem eluded us all).