Insert Query Not Working in VBA - Error 13 (1 Viewer)

[Manning]

Hello,

Without getting into great detail, I have a query that worked when I constructed it in MS Access Queries (static data)but now that I am trying to get it to work in VBA (dynamic data) it claims that I have a Datatype Mis-Match (Error 13).

I have ensured all my fields are of the same datatype and match up perfectly. (I am going to check, again, after typing this post.)

I have tested small Insert statements on other tables within VBA and they worked fine within the same code.

I have inserted a MsgBox to make sure the SQL string has been constructed properly and looks identical to the one in Access.

What gives?

Any help is appreciated.

 

[reclusivemonkey]

Your code might help :-o

 

[Manning]

Details it is then...

Private Sub btnSaveRecount_Click()
On Error GoTo Err_btnSaveRecount_Click

Dim currentCount As Long
Dim newCount As Long
Dim diffCount As Long
Dim strSQL As String
Dim inventoryID As Long

'Encountered a problem when QuantityOnHand is Null
If IsNull(Form_sfrmCurrentCount.QuantityOnHand.Value) Then
currentCount = 0
Else
currentCount = Form_sfrmCurrentCount.QuantityOnHand.Value
End If

newCount = Me.txtNewCount.Value
inventoryID = Me.cboInventoryList.Column(0)

diffCount = newCount - currentCount

strSQL = "INSERT INTO InventoryTransactions (itPurchaseOrderID, itShipmentID, itInventoryID, itQuantity, itTransactionTypeID, itPricePerUnit, itReceived)" & _
" VALUES (Null, Null, " & inventoryID & ", " & diffCount & ", 3, 0, Yes);"

'Ensures the string looks right
MsgBox strSQL

DoCmd.RunSQL strSQL

Me.Refresh

Exit_btnSaveRecount_Click:
Exit Sub

Err_btnSaveRecount_Click:
If Err = 2046 Then 'The command or action Undo is not available now
Exit Sub
Else
MsgBox Err.Number, Err.Description
Resume Exit_btnSaveRecount_Click
End If

End Sub

What I am trying to do: I want to put a record into my transaction table for when inventory recounts are done.

Table:
InventoryTransactions

Fields:
itTransactionID (autonumber) -- not shown, taken care of
itPurchaseOrderID - null
itShipmentID - null
*itInventoryID - pulled from drop down list, long integer
*itQuantity - long integer, diffCount as calculated above
itTransactionTypeID - 3 (hard coded)
itPricePerUnit - 0 (irrelevant)
itReceived - Yes (hard coded)

Here is the query created in Access:

INSERT INTO InventoryTransactions ( itPurchaseOrderID, itShipmentID, itInventoryID, itQuantity, itTransactionTypeID, itPricePerUnit, itReceived )
VALUES (Null, Null, 6, 1, 3, 0, Yes);

This worked fine and is practically identical with exception of itInventoryID and itQuantity. I should look there again since it is the only real change.

Thanks for your help.

 

[Manning]

I have tried the query developed in Access with all hardcoded data that worked perfectly, in VBA, it still gives me the Run-Time Error '13' Type Mismatch.

How can it work one place and not another???

 

[reclusivemonkey]

Details it is then...

[... snip...]

Thanks for your help.

I'd wait 'til I've actually helped ;-)

Can't see anything obviously wrong. What are

txtNewCount

and

cboInventoryList

's data types? Have you tried changing your "Long"s to "Variants" just to see if you can get it to work that way?

 

[Manning]

Changed long to variant, didn't work.

I am still playing with the code, something might pan out.

txtNewCount and cboInventoryList are from the Form I am pulling the data from. When I pull up the locals window in VB they are getting their data into the right variables.

I'll keep at it.

 

[reclusivemonkey]

txtNewCount and cboInventoryList are from the Form I am pulling the data from. When I pull up the locals window in VB they are getting their data into the right variables.

Yes, but are they the right type for the variable? What is each field's data type? The error message would seem to point to it being a problem there, but sometimes thats just a red herring...

 

[Manning]

I am pretty sure they are, I thought I made sure by transferring the data into known variables before inserting them into the table. I'll continue hacking away at it and see what I come up with.

Thansk again.

 

[Manning]

Seems to be that I am slow.

The Mismatch data type is for the itPricePerUnit field. It is defined as Currency and can't seem to accpet 0 as a value (where is does if you create a query in Access). I'll post my solution when I've got one.