Help with code

[Guest]

Access 2000. I would appreciate some help with this code. I keep getting
an error of "Type Mismatch" on "For New Cert. Num" just after "If Response
= vbYes Then" I'm pretty much code illiterate. This code worked previously
when the "CertBeg" and "CertEnd" was strictly a number. Now it has to be
Text because the number now begins with a letter, such as Z12332. Thanks
for any help you can give, Randy.

Private Sub Form_AfterUpdate()
Dim stDocName As String
Dim strSQL As String
Dim NewCertNum As String
Dim CertBeg As String
Dim CertEnd As String
Dim cnt As Integer
Dim Msg As String
Dim Response As String
DoCmd.SetWarnings False

If [EndCertNolbl] = 0 Then
stDocName = "SingleCertAppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else


'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl
cnt = 0

' Define message.
Msg = "BegCert # = " & CertBeg & vbCrLf & "EndCert # = " & CertEnd &
vbCrLf
'Msg = Msg & "Letter = " & Me.BeginningInitial & vbCrLf & vbCrLf
Msg = Msg & vbCrLf & "Insert Certifcates?"
Style = vbQuestion + vbYesNo + vbDefaultButton1 ' Define buttons.
' Display message.
Response = MsgBox(Msg, Style)
If Response = vbYes Then ' User chose Yes.
For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut )"
strSQL = strSQL & " Values ( "
strSQL = strSQL & NewCertNum & "', "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "', "
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,'''"
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ",); "

' now do it





CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub

 

[mcescher]

Access 2000.  I would appreciate some help with this code.  I keep getting
an error of  "Type Mismatch" on "For New Cert. Num" just after "If Response
= vbYes Then"  I'm pretty much code illiterate.  This code worked previously
when the "CertBeg" and "CertEnd" was strictly a number.  Now it has to be
Text because the number now begins with a letter, such as Z12332.  Thanks
for any help you can give, Randy.

Private Sub Form_AfterUpdate()
    Dim stDocName As String
    Dim strSQL As String
    Dim NewCertNum As String
    Dim CertBeg As String
    Dim CertEnd As String
    Dim cnt As Integer
    Dim Msg As String
    Dim Response As String
    DoCmd.SetWarnings False

    If [EndCertNolbl] = 0 Then
    stDocName = "SingleCertAppendQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Else

    'get the beginning and ending Cert numbers
    CertBeg = Me.BeginCertNolbl
    CertEnd = Me.EndCertNolbl
    cnt = 0

' Define message.
    Msg = "BegCert # = " & CertBeg & vbCrLf & "EndCert # = " & CertEnd &
vbCrLf
    'Msg = Msg & "Letter = " & Me.BeginningInitial & vbCrLf & vbCrLf
    Msg = Msg & vbCrLf & "Insert Certifcates?"
    Style = vbQuestion + vbYesNo + vbDefaultButton1     ' Definebuttons.
    ' Display message.
    Response = MsgBox(Msg, Style)
    If Response = vbYes Then    ' User chose Yes.
        For NewCertNum = CertBeg To CertEnd
            ' create the SQL statement to insert the new records
            strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut )"
            strSQL = strSQL & " Values ( "
            strSQL = strSQL & NewCertNum & "', "
            strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "', "
            strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,'''"
            strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ",); "

            ' now do it

           CurrentDb.Execute strSQL
            cnt = cnt + 1
             Next

      DoCmd.SetWarnings True
    End If
    End If
End Sub

You mentioned that the NewCertNum is now text, so you'll need to
enclose the text in quotes. You put a single quote after NewCertNum
in your SQL, but you didn't put one before it.

strSQL = strSQL & " Values ( "
should be
strSQL = strSQL & " Values ( '"

This will then evaluate to:

Values('Z12332', etc....

Hope this helps,
Chris M.

 

[Guest]

I tried your suggestion, here are the changes, but I still get a type
mismatch for "NewCertNum", Thanks

Private Sub Form_AfterUpdate()
Dim stDocName As String
Dim strSQL As String
Dim NewCertNum As String
Dim CertBeg As String
Dim CertEnd As String
Dim cnt As Integer
Dim Msg As String
Dim Response As String
DoCmd.SetWarnings False

If [EndCertNolbl] = 0 Then
stDocName = "SingleCertAppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else


'get the beginning and ending Cert numbers
CertBeg = Me.BeginCertNolbl
CertEnd = Me.EndCertNolbl
cnt = 0

' Define message.
Msg = "BegCert # = " & CertBeg & vbCrLf & "EndCert # = " & CertEnd &
vbCrLf
'Msg = Msg & "Letter = " & Me.BeginningInitial & vbCrLf & vbCrLf
Msg = Msg & vbCrLf & "Insert Certifcates?"
Style = vbQuestion + vbYesNo + vbDefaultButton1 ' Define buttons.
' Display message.
Response = MsgBox(Msg, Style)
If Response = vbYes Then ' User chose Yes.
For NewCertNum = CertBeg To CertEnd
' create the SQL statement to insert the new records
strSQL = "INSERT INTO CheckedOutCertsAllNumbers (
CertNo,Inspector,TypeOfCert,DateCheckedOut )"
strSQL = strSQL & " Values ('"
strSQL = strSQL & NewCertNum & "', "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "', "
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,'''"
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ",); "

' now do it



CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub

 

[Chris]

Put a break point at the line CurrentDb.Execute strSQL and then in the
immediate window type ?strSQL and hit enter. Verify that your SQL statement
appears as you would expect it to.