Wrong Data Type Error when calling Sub

[Simon Harris]

Hi All,

When I call this sub:

Public Sub MakePitches(intSiteID, intReqPitchQty)
Dim i As Integer, strSQL As String
DoCmd.SetWarnings ("off")
For i = 0 To intReqPitchQty
'strSQL = "insert into TBL_Pitches (ParkID, PitchNumber) values
(" & intSiteID & ", " & i & ")"
'DoCmd.RunSQL (strSQL)
Next
DoCmd.SetWarnings ("on")
End Sub

Using:

Call MakePitches(3, 31)


I get the following error:

"An expression you entered is the wrong data type for one of the arguments"

I have also tried setting up my sub like this:
Public Sub MakePitches(intSiteID as Integer, intReqPitchQty as Integer)

Any ideas/suggestions will be much appreciated!

Thanks,
Simon.

 

[Douglas J. Steele]

The second way you mentioned should work.

Are you actually calling the sub using constants, like in your example, or
are you passing it variables, or recordset fields?

 

[Wayne Morgan]

With the "As Integer" line is the correct one. However, I don't think your
error is coming from that line, I think it is from the DoCmd.SetWarnings
lines. They should be

DoCmd.SetWarnings False
and
DoCmd.SetWarnings True

 

[Simon Harris]

Thank you both for your replies.

It was indeed the doCmd.SetWarnings line that was causing the error.

Simon.

 

[Mike Labosh]

Public Sub MakePitches(intSiteID, intReqPitchQty)

Dim i As Integer, strSQL As String
DoCmd.SetWarnings ("off")
For i = 0 To intReqPitchQty
'strSQL = "insert into TBL_Pitches (ParkID, PitchNumber) values
(" & intSiteID & ", " & i & ")"
'DoCmd.RunSQL (strSQL)
Next
DoCmd.SetWarnings ("on")
End Sub

Perhaps this is what you are looking for:

Public Sub MakePitches(siteID As Integer, reqPitchQty As Integer)
Dim i As Integer
Dim sql As String

For i = 0 To reqPitchQty
sql = "INSERT INTO tbl_Pitches " & vbCrLf & _
"(ParkID, PitchNumbers) " & vbCrLf & _
"VALUES " & vbCrLf & _
"(" & CStr(siteID) & ", " & CStr(i) & ")"

Debug.Print sql ' This will list your code in the Immediate
Window

DoCmd.RunSQL(sql)
Next i

End Sub

OK -- the bit about the vbCrLf and the line-continuation characters (space &
underscore) are a bit much, but are useful for debugging. Try the code
above, and set a breakpoint (Press F9) on the line that begins with "sql =
". Then you simply F8 that line and then check it in the immediate window
to ensure the SQL syntax has been built the way you wanted it to be.

In addition to the problem that you are trying to solve, I would venture a
guess that such code is vulnerable to SQL Injection attacks. Consider this:

"SELECT Stuff FROM ThisTable WHERE ThisColumn = " & TextBox1.Value

Suppose a badguy put the following in your text box:

""; DELETE * FROM MSysObjects

Learn how to use what Access calls "Parameter Querys". Only then will you
be a powerful enough Jedi to defeat Lord Vader

Also, in my humble opinion (IMHO) instead of using SetWarnings, you should
use the full-blown error handling syntax like this:

Public Sub DoStuff()

On Error Goto DoStuffError

' whole bunch of code here

DoStuffExit:
Exit Sub ' (or function)

DoStuffError:
' inform the user? log the error? say bad things to your mom? you
decide.
Resume DoStuffExit

End Sub

For everyone -- The first request for my full blown error logging code
scores a post of the source code that you can use pretty much any place.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei