Help with code "Type Mismatch"

G

Guest

Access 2000. I keep getting a type mismatch on "NewCertNum" with this code
shown below. "NewCertNum" should be text. I tried a sugggestion a week or
so ago but it didn't work. Any ideas? Thanks for any help, 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 knt 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 & 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]![cboInspector] & ", '"
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,''' "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "'); "

' now do it



CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub
 
D

Douglas J. Steele

strSQL = strSQL & "'" & NewCertNum & "', "

Exagerated for clarity, that's

strSQL = strSQL & " ' " & NewCertNum & " ' , "
 
G

Guest

I tried your suggestion but still get a Type Mismatch on "NewCertNum".
Here's the code with your suggestions. Thanks, 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 knt 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 & 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]![cboInspector] & ", '"
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,''' "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "'); "

' now do it



CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub
 
D

Douglas J. Steele

Insert the line of code

Debug.Print strSQL

directly in front of the line

CurrentDb.Execute strSQL

When your code runs (and fails), go to the Immediate Window (Ctrl-G), copy
the SQL string that's there and paste it into your reply.
 
G

Guest

I tried what u suggested, but the warning type mismatch still keeps coming
up with nothing in the immediate window. Heres how I entered debub.Print
strSQL below. Thanks again.

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 knt 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 & 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,DateCheckedOut,TypeOfCert )"
strSQL = strSQL & " Values ( '"
strSQL = strSQL & "'" & NewCertNum & "', "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ", '"
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,''' "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "'); "

' now do it


Debug.Print strSQL CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub
 
D

Douglas J. Steele

It needs to be two separate lines:

Debug.Print strSQL
CurrentDb.Execute strSQL

That having been said, you should have got a compile error for that, not the
type mismatch warning.

You may have to put a breakpoint into the module, so that you can
single-step through the code to see which line is causing the error. To do
this, simply click in the margin to the left of an "executable" line of code
(i.e.: a line that does something, not a Dim statement). That should put a
big circle in the margin and highlight the line of code. Now, when the code
runs, you'll be taken into the VB Editor. Use the F8 key to step from line
to line. (Note: You may have to do this more than once, because you're bound
to get surprised the first time!)
 
G

George Nicholson

For NewCertNum = CertBeg To CertEnd
.....
Next
Click to expand...

Um, NewCertNum, CertBeg and CertEnd ALL have to be numeric variables, don't
they? I would have thought that a For..Next loop simply demands numbers. (If
strings were supported, I shudder to think what might happen if you included
an optional "Step 2" directive?)

When you say you are getting a TypeMismatch on NewCertNum, which line are
you getting it on? The "For NewCertNum" line?

You could try changing the declaration of NewCertNum to a numeric type (lets
say Long just for argument) and change the offending line to:
For NewCertNum = Clng(CertBeg) to cLng(CertEnd)

You might (or might not) also need to substitute Cstr(NewCertNum) for other
occurances of NewCertNum.

--
HTH,
George


I tried what u suggested, but the warning type mismatch still keeps coming
up with nothing in the immediate window. Heres how I entered debub.Print
strSQL below. Thanks again.

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 knt 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 & 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,DateCheckedOut,TypeOfCert )"
strSQL = strSQL & " Values ( '"
strSQL = strSQL & "'" & NewCertNum & "', "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboInspector] & ", '"
strSQL = strSQL & "#" &
[Forms]![frmCheckedOutCertificates]![DateCheckedOutlbl] & "#,''' "
strSQL = strSQL &
[Forms]![frmCheckedOutCertificates]![cboTypeofCert] & "'); "

' now do it


Debug.Print strSQL CurrentDb.Execute strSQL
cnt = cnt + 1
Next

DoCmd.SetWarnings True
End If
End If
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with code 3
Need help with form coding (Date Problems) 1
Type mismatch error 2
Help with syntax error 1
convert vbProperCase when saving new entry in combox 2
Add Record with combo box 13
Code works in query but not in vba - help 4
data type mismatch 4

Top