DoCmd.RunSql Insert Into

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the syntax to insert a new record into a Table. The table has nine
fields, some text and some numeric: eg a,b and c are text and v,w,x,y and z
are numeric.
I am using Access 2003
Thank You
dhowe
 
1. Create a new query.

2. Change it to an Append query (Append on Query menu).

3. Type sample values into the Field row for each field, and add the name of
the field below in the Append To row.

4. When you have it set up as a complete sample, switch to SQL View (View
menu). Copy what you see there.
 
Thanks for your advice but I still can't get it to work. This is the closest
I have been able to get to it working

Private Sub Price_AfterUpdate()
Dim vty As Integer, grd As Integer, sze As Integer, blk As Integer
Dim pNo As Integer
Dim ctleft As Single
vty = Int(DLookup("ProductVarietyID", "ProductVarieties",
"ProductVarietyName = """ & Me.ProductVarietyName & """"))
grd = DLookup("ProductGradeID", "ProductGrade", "ProductGradeName=""" &
Me.ProductGradeName & """")
sze = DLookup("ProductSizeID", "ProductSize", "ProductSize=""" &
Me.ProductSize & """")
blk = DLookup("BlockDetailsID", "ProductionBatch", "PalletNoID=" &
Me.PalletNoID & "and ProductVarietyID =" & vty & "and ProductGradeID =" & grd
& "and ProductSizeID =" & sze & "and Price = " & Me.Price.OldValue)
pNo = Me.PalletNoID

If CountSold < Count_prod Then
ctleft = Count_prod - CountSold
DoCmd.RunSQL "INSERT INTO ProductionBatch ( PalletNoID ,
BlockDetailsID , ProductVarietyID , ProductSizeID, ProductGradeID, [Count],
Price, CountSold, FullySold ) Values", (pNo) & "," & Int((blk)) & "," &
([vty]) & "," & ([sze]) & "," & ([grd]) & "," & ([ctleft]) & "," & 0 & "," &
0 & "," & No

End If
End Sub

It keeps getting values for "PalletNoID","BlockDetailsID"..etc from the
Form. pNo, blk, vty...etc are all showing the correct values. These are
either default zeros or are found from other tables using DLookUp or from
the form.
The fields in tblProductionBatch are Integer, Integer, Integer, Integer,
Integer, Single, Single, Single, Y/N

Hope to hear from you soon
Regards Dennis
 
Try an alias on the literal numeric values:

"INSERT INTO ProductionBatch ( PalletNoID , BlockDetailsID ,
ProductVarietyID , ProductSizeID, ProductGradeID, [Count], Price, CountSold,
FullySold ) Values " & pNo & " AS p, " & Int(blk) & " AS b," & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dhowe said:
Thanks for your advice but I still can't get it to work. This is the
closest
I have been able to get to it working

Private Sub Price_AfterUpdate()
Dim vty As Integer, grd As Integer, sze As Integer, blk As Integer
Dim pNo As Integer
Dim ctleft As Single
vty = Int(DLookup("ProductVarietyID", "ProductVarieties",
"ProductVarietyName = """ & Me.ProductVarietyName & """"))
grd = DLookup("ProductGradeID", "ProductGrade", "ProductGradeName=""" &
Me.ProductGradeName & """")
sze = DLookup("ProductSizeID", "ProductSize", "ProductSize=""" &
Me.ProductSize & """")
blk = DLookup("BlockDetailsID", "ProductionBatch", "PalletNoID=" &
Me.PalletNoID & "and ProductVarietyID =" & vty & "and ProductGradeID =" &
grd
& "and ProductSizeID =" & sze & "and Price = " & Me.Price.OldValue)
pNo = Me.PalletNoID

If CountSold < Count_prod Then
ctleft = Count_prod - CountSold
DoCmd.RunSQL "INSERT INTO ProductionBatch ( PalletNoID ,
BlockDetailsID , ProductVarietyID , ProductSizeID, ProductGradeID,
[Count],
Price, CountSold, FullySold ) Values", (pNo) & "," & Int((blk)) & "," &
([vty]) & "," & ([sze]) & "," & ([grd]) & "," & ([ctleft]) & "," & 0 & ","
&
0 & "," & No

End If
End Sub

It keeps getting values for "PalletNoID","BlockDetailsID"..etc from the
Form. pNo, blk, vty...etc are all showing the correct values. These are
either default zeros or are found from other tables using DLookUp or from
the form.
The fields in tblProductionBatch are Integer, Integer, Integer, Integer,
Integer, Single, Single, Single, Y/N

Hope to hear from you soon
Regards Dennis

dhowe said:
What is the syntax to insert a new record into a Table. The table has
nine
fields, some text and some numeric: eg a,b and c are text and v,w,x,y and
z
are numeric.
I am using Access 2003
Thank You
dhowe
Click to expand...
Click to expand...
 
It still wont work. The destination records (those wth matching fields)
keeps getting values from the Form and the others say there is a data
mismatch or a syntax problem.

Allen Browne said:
Try an alias on the literal numeric values:

"INSERT INTO ProductionBatch ( PalletNoID , BlockDetailsID ,
ProductVarietyID , ProductSizeID, ProductGradeID, [Count], Price, CountSold,
FullySold ) Values " & pNo & " AS p, " & Int(blk) & " AS b," & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dhowe said:
Thanks for your advice but I still can't get it to work. This is the
closest
I have been able to get to it working

Private Sub Price_AfterUpdate()
Dim vty As Integer, grd As Integer, sze As Integer, blk As Integer
Dim pNo As Integer
Dim ctleft As Single
vty = Int(DLookup("ProductVarietyID", "ProductVarieties",
"ProductVarietyName = """ & Me.ProductVarietyName & """"))
grd = DLookup("ProductGradeID", "ProductGrade", "ProductGradeName=""" &
Me.ProductGradeName & """")
sze = DLookup("ProductSizeID", "ProductSize", "ProductSize=""" &
Me.ProductSize & """")
blk = DLookup("BlockDetailsID", "ProductionBatch", "PalletNoID=" &
Me.PalletNoID & "and ProductVarietyID =" & vty & "and ProductGradeID =" &
grd
& "and ProductSizeID =" & sze & "and Price = " & Me.Price.OldValue)
pNo = Me.PalletNoID

If CountSold < Count_prod Then
ctleft = Count_prod - CountSold
DoCmd.RunSQL "INSERT INTO ProductionBatch ( PalletNoID ,
BlockDetailsID , ProductVarietyID , ProductSizeID, ProductGradeID,
[Count],
Price, CountSold, FullySold ) Values", (pNo) & "," & Int((blk)) & "," &
([vty]) & "," & ([sze]) & "," & ([grd]) & "," & ([ctleft]) & "," & 0 & ","
&
0 & "," & No

End If
End Sub

It keeps getting values for "PalletNoID","BlockDetailsID"..etc from the
Form. pNo, blk, vty...etc are all showing the correct values. These are
either default zeros or are found from other tables using DLookUp or from
the form.
The fields in tblProductionBatch are Integer, Integer, Integer, Integer,
Integer, Single, Single, Single, Y/N

Hope to hear from you soon
Regards Dennis

dhowe said:
What is the syntax to insert a new record into a Table. The table has
nine
fields, some text and some numeric: eg a,b and c are text and v,w,x,y and
z
are numeric.
I am using Access 2003
Thank You
dhowe
Click to expand...
Click to expand...
Click to expand...
 
Go back to the query design window.

When you create one that works, switch to SQL view and you have an exact
template of something that works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dhowe said:
It still wont work. The destination records (those wth matching fields)
keeps getting values from the Form and the others say there is a data
mismatch or a syntax problem.

Allen Browne said:
Try an alias on the literal numeric values:

"INSERT INTO ProductionBatch ( PalletNoID , BlockDetailsID ,
ProductVarietyID , ProductSizeID, ProductGradeID, [Count], Price,
CountSold,
FullySold ) Values " & pNo & " AS p, " & Int(blk) & " AS b," & ...


dhowe said:
Thanks for your advice but I still can't get it to work. This is the
closest
I have been able to get to it working

Private Sub Price_AfterUpdate()
Dim vty As Integer, grd As Integer, sze As Integer, blk As Integer
Dim pNo As Integer
Dim ctleft As Single
vty = Int(DLookup("ProductVarietyID", "ProductVarieties",
"ProductVarietyName = """ & Me.ProductVarietyName & """"))
grd = DLookup("ProductGradeID", "ProductGrade",
"ProductGradeName=""" &
Me.ProductGradeName & """")
sze = DLookup("ProductSizeID", "ProductSize", "ProductSize=""" &
Me.ProductSize & """")
blk = DLookup("BlockDetailsID", "ProductionBatch", "PalletNoID=" &
Me.PalletNoID & "and ProductVarietyID =" & vty & "and ProductGradeID ="
&
grd
& "and ProductSizeID =" & sze & "and Price = " & Me.Price.OldValue)
pNo = Me.PalletNoID

If CountSold < Count_prod Then
ctleft = Count_prod - CountSold
DoCmd.RunSQL "INSERT INTO ProductionBatch ( PalletNoID ,
BlockDetailsID , ProductVarietyID , ProductSizeID, ProductGradeID,
[Count],
Price, CountSold, FullySold ) Values", (pNo) & "," & Int((blk)) & "," &
([vty]) & "," & ([sze]) & "," & ([grd]) & "," & ([ctleft]) & "," & 0 &
","
&
0 & "," & No

End If
End Sub

It keeps getting values for "PalletNoID","BlockDetailsID"..etc from the
Form. pNo, blk, vty...etc are all showing the correct values. These are
either default zeros or are found from other tables using DLookUp or
from
the form.
The fields in tblProductionBatch are Integer, Integer, Integer,
Integer,
Integer, Single, Single, Single, Y/N

Hope to hear from you soon
Regards Dennis

:

What is the syntax to insert a new record into a Table. The table has
nine
fields, some text and some numeric: eg a,b and c are text and v,w,x,y
and
z
are numeric.
I am using Access 2003
Thank You
dhowe
Click to expand...
Click to expand...
Click to expand...
 
Back
Top