Problem with INSERT INTO code

[Simon]

Can any one see whats wrong with this code

strQty = InputBox("How many to buy:", "Purchase Product")


If Len(strQty) > 0 Then
' confirm that valid number entered
If IsNumeric(strQty) Then
If IsNumeric(strQty) Then

strSQL = "INSERT INTO tblOrderProduct (ProductID,
OrderNumber, Qty) VALUES (Me.ProductID, 900, strQty)"

cmd.CommandText = strSQL
cmd.Execute


Else
MsgBox "Invalid quantity entered.", vbExclamation,
"Warning"
End If
Else
' user pressed Cancel button or entered no quantity in input
box
MsgBox "Purchase Cancelled.", vbInformation, "Purchase
Product"

End If

 

[Douglas J. Steele]

It would help to know what problem you're encountering...

 

[Maurice]

You are testing for a IsNumeric but an inputbox will always return a
stringvalue so you insert statement will never get fired. You can use the
conversion to get an numerical value to test it again.

Place this line before the len test: strQty=Cint(strQty)
see if that helps. Remember though that Cint rounds numbers and you might
have to do some error handling when text is being entered becaus cint will
trip on that...

hth

 

[Simon]

It would help to know what problem you're encountering...

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)












- Show quoted text -

I get now error messages, just not date is added to the table

 

[Noel]

Yes, I can see what is wrong with it, you have included the name of the
variable strQty in the string that you are puttin in strSQL - but you need to
put the contents of that variable into the string; thus:

strSQL = "INSERT INTO tblOrderProduct (ProductID, OrderNumber, Qty) VALUES
(Me.ProductID, 900, " & strQty & ")"

That only takes care of the Qty value, I cannot speak to the first value,
Me.ProductID, as I do not know what that is.

Cheers,

Noel

 

[John Spencer]

strQty = InputBox("How many to buy:", "Purchase Product")

If IsNumeric(strQty) Then
'<<<<<<< StrSQL is improperly constructed >>>>>>>>
'Assumption is that ProductID is a number field.
'If it is a string field then you need to add quote marks into
'the sql string you are building.

strSQL = "INSERT INTO tblOrderProduct " & _
"(ProductID, OrderNumber, Qty)" & _
" VALUES (" & Me.ProductID & ", 900," & strQty & ")"

cmd.CommandText = strSQL
cmd.Execute


ElseIf StrQty <> "" then
MsgBox "Invalid quantity entered.", vbExclamation,"Warning"

Else
' user pressed Cancel button or entered no quantity in inputbox
MsgBox "Purchase Cancelled.", vbInformation, "Purchase Product"
End If

Hopefully you have error trapping in this routine to catch any problems.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================