Type Mismatch / ADO AddNew

[cheuschober]

Anyone know why I might be getting a type mismatch error with the following?

Private Sub cboBusinessTypeID_NotInList(NewData As String, Response As Integer)

    On Error GoTo Err_ErrorHandler
    
    Const Message1 = "The data you have entered is not in the current selection."
    Const Message2 = "Would you like to add it?"
    Const Title = "Unknown entry..."
    Const NL = vbCrLf & vbCrLf
    
    Dim strSourceTbl As String
    Dim strKeyField As String
    Dim strDataField As String
    
    strSourceTbl = "lkupBusinessType"
    strKeyField = "BusinessTypeID"
    strDataField = "BusinessType"
    
    Dim cn As Connection
    Dim rs As ADODB.Recordset
    Dim bytKeyID As Byte
        
    If MsgBox(Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
    
        bytKeyID = 1 + DMax(strKeyField, strSourceTbl, strKeyField <> 255)
         
        Set cn = CurrentProject.Connection
        Set rs = New ADODB.Recordset
        With rs
            .Open strSourceTbl, CurrentProject.Connection, adOpenStatic, adLockPessimistic
            .AddNew
            .Fields(strKeyField) = bytKeyID
            .Fields(strDataField) = NewData
            .Update
            .Close
        End With
        Response = acDataErrAdded
    Else
        Me.cboBusinessTypeID.Undo
        Response = acDataErrContinue
    End If
    
Exit_ErrorHandler:
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub
    
Err_ErrorHandler:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_ErrorHandler
End Sub

The destination table for the Add record uses a byte and a text field, respectively so I'm left a little boggled why it won't add the records.

Thanks,
~Chad

 

[Mile-O]

Dim cn As Connection

[b]change to[/b]

Dim cn As ADODB.Connection

.Open strSourceTbl, CurrentProject.Connection, adOpenStatic, adLockPessimistic

[b]change to[/b]

.Open strSourceTbl, cn, adOpenStatic, adLockPessimistic

Check that a reference to ADO (Microsoft ActiveX Data Objects) is set by opening a module, going to Tools -> References

Also, comment out the On Error Goto line to find the exact line that causes the problem.

 

[cheuschober]

Seems there's an error in my syntax for the DMax function... Thanks for the assist, now the followup -- I suspect that my error is found in the 'where' statement of the DMax() function. Any ideas?

~Chad

 

[Mile-O]

strKeyField & " <> 255"

 

[cheuschober]

strKeyField & " <> 255"

Mile-O, you're a genius.

I think I honestly tried every other single quotation and double quotation variant of that possible. Okhams razor, for once holds true I guess.

I could kiss you if it wouldn't have Rich and Ken jumping all over me.