INSERT INTO

Prev: Finder
Next: Access 2003

From: Simon on 3 Jul 2008 18:17

---

What could be wrong with this, its not adding the produt to the table

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
If IsNumeric(strQty) Then
strSQL = "INSERT INTO tblOrderProduct (ProductID,
OrderNumber) VALUES (Me.ProductID, me.ordernumber)"


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
End If

From: John W. Vinson on 3 Jul 2008 19:20

---

On Thu, 3 Jul 2008 15:17:10 -0700 (PDT), Simon <S.Dickson(a)shos.co.uk> wrote:

>What could be wrong with this, its not adding the produt to the table
>
> 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
> If IsNumeric(strQty) Then
> strSQL = "INSERT INTO tblOrderProduct (ProductID,
>OrderNumber) VALUES (Me.ProductID, me.ordernumber)"
>
>
> 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
> End If

Well, the ' before cmd.Execute is making it into a comment. The query is never
being executed.
--

John W. Vinson [MVP]

From: Ken Snell (MVP) on 3 Jul 2008 19:24

---

You need to concatenate the actual values into the SQL string:

strSQL = "INSERT INTO tblOrderProduct (ProductID, OrderNumber)
VALUES (" & Me.ProductID & ", " & me.ordernumber & ")"
--

Ken Snell
<MS ACCESS MVP>




"Simon" <S.Dickson(a)shos.co.uk> wrote in message
news:2638073b-5a00-4474-a58a-fb858e580970(a)y21g2000hsf.googlegroups.com...
> What could be wrong with this, its not adding the produt to the table
>
> 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
> If IsNumeric(strQty) Then
> strSQL = "INSERT INTO tblOrderProduct (ProductID,
> OrderNumber) VALUES (Me.ProductID, me.ordernumber)"
>
>
> 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
> End If

From: Jeff Boyce on 3 Jul 2008 19:26

---

Simon

Why are you testing for strQty twice in a row, inside nested If... Then
statements?

Have you tried setting a breakpoint near the top and stepping through,
inspecting values at each step?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Simon" <S.Dickson(a)shos.co.uk> wrote in message
news:2638073b-5a00-4474-a58a-fb858e580970(a)y21g2000hsf.googlegroups.com...
> What could be wrong with this, its not adding the produt to the table
>
> 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
> If IsNumeric(strQty) Then
> strSQL = "INSERT INTO tblOrderProduct (ProductID,
> OrderNumber) VALUES (Me.ProductID, me.ordernumber)"
>
>
> 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
> End If

 | 
Pages: 1
Prev: Finder
Next: Access 2003