Shop ordering system

[Simon]

I have a continiums form with lots of products on with a buy it
button next to them, what i would like to code is the following

If i press buy it brings up a message saying 'How many to buy' then i
can entrer 1, 2 3 ect and then click OK, it will then add the Product
Name, Code and Price and Quantity i have just typed into the,
tblOrderProduct


can any one help with the code


Thansk

 

[Ken Sheridan]

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strQty As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

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

If Len(strQty) > 0 Then
' confirm that valid number entered
If IsNumeric(strQty) Then
strSQL = "INSERT INTO tblOrderProduct & _
"(Code, Price, Quantity) " & _
"VALUES(" & Me.Code & "," & _
Me.Price & "," & 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

Note that the SQL statement does not insert the name of the product. I'm
assuming that Code is the primary key of the products table, so having a name
column in tblOrderProduct introduces redundancy and the possibility of
inconsistent data. The name can be pulled in from the products table
whenever necessary by joining it to the tblOrderProduct table on the Code
columns in a query.

I've also assumed that Code is a number data type. If it’s a text data type
then amend the SQL statement as follows:

strSQL = "INSERT INTO tblOrderProduct & _
"(Code, Price, Quantity) " & _
"VALUES(""" & Me.Code & """," & _
Me.Price & "," & strQty & ")"

Ken Sheridan
Stafford, England