CODEWITHSUNDEEP
sqlms-access-2007
bikerben
bikerben

Reputation: 483

Appending Record from Form to Table with VB

I've read a few Q&A's regarding this and I think I understand it however in true amature style I think I'm missing something here.

What I'm trying to do is to display one record at a time in a form from table 'A' and give the user an option of pressing a button so they can append the currently viewed record to Table 'B' using the VB code below. however when I press the button nothing happens, could somebody please help me clarify whether I have understood what I have read correctly:

Private Sub Command31_Click()

Dim PartNo, TotalQty, Description, Manufacturer, TotalCost, Category, Warehouse, BinLoc, OrderNum, DateRec, UnitCost, ReturnCode, Batch, bookinsql
PartNo = Form_LiveQfrm.[PartNo]
TotalQty = Form_LiveQfrm.[TotalQty]
Description = Form_LiveQfrm.[Description]
Manufacturer = Form_LiveQfrm.[Manufacturer]
TotalCost = Form_LiveQfrm.[TotalCost]
Category = Form_LiveQfrm.[Category]
Warehouse = Form_LiveQfrm.[Warehouse]
BinLoc = Form_LiveQfrm.[BinLoc]
OrderNum = Form_LiveQfrm.[OrderNum]
DateRec = Form_LiveQfrm.[DateRec]
UnitCost = Form_LiveQfrm.[UnitCost]
ReturnCode = Form_LiveQfrm.[ReturnCode]
Batch = Form_LiveQfrm.[Batch]
bookinsql = "INSERT INTO QStore (TextField, NumericField, TextField, TextField, NumericField, TextField, TextField, TextField, TextField, DateField, NumericField, TextField, NumericField ) VALUES ('" & Form_LiveQfrm.PartNo & "', " & Form_LiveQfrm.TotalQty & ", '" & Form_LiveQfrm.Description & "', '" & Form_LiveQfrm.Manufacturer & "', " & Form_LiveQfrm.TotalCost & ", '" & Form_LiveQfrm.Category & "', '" & Form_LiveQfrm.Warehouse & "', '" & Form_LiveQfrm.BinLoc & "', '" & Form_LiveQfrm.OrderNum & "', #" & Form_LiveQfrm.DateRec & "#, " & Form_LiveQfrm.UnitCost & ", '" & Form_LiveQfrm.ReturnCode & "', " & Form_LiveQfrm.Batch & ");"

DoCmd.RunSQL bookinsql

End Sub

My LiveQTemp Table is layed out like this:

ID - AutoNumber
PartNo - Text
TotalQty - Number
Description Text
Manufacturer - Text
TotalCost - Number
Category - Text
Warehouse - Text
BinLoc - Text
OrderNum - Text
DateRec - Date/Time
UnitCost - Number
ReturnCode - Text
Batch - Number

Upvotes: 0

Views: 76

Answers (1)

ObieMD5
ObieMD5

Reputation: 2657

You have an extra comma in your statement just before the word VALUES in your column specifications.

Change this:

bookinsql = "INSERT INTO LiveQTemp (TextField, NumericField, TextField, TextField, NumericField, TextField, TextField, TextField, NumericField, DateField, NumericField, TextField, NumericField,  ) VALUES (" & PartNo & ", '" & TotalQty & "', " & Description & ", " & Manufacturer & ", '" & TotalCost & "', " & Category & ", " & Warehouse & ", " & BinLoc & ", '" & OrderNum & "', '" & DateRec & "', '" & UnitCost & "', " & ReturnCode & ", '" & OrderNum & "');"

To this:

bookinsql = "INSERT INTO LiveQTemp (TextField, NumericField, TextField, TextField, NumericField, TextField, TextField, TextField, NumericField, DateField, NumericField, TextField, NumericField) VALUES ('" & PartNo & "', " & TotalQty & ", '" & Description & "','" & Manufacturer & "', " & TotalCost & ", '" & Category & "','" & Warehouse & "','" & BinLoc & "',' " & OrderNum & "', #" & DateRec & "#, " & UnitCost & ",'" & ReturnCode & "', " & Batch & ");"

Also if you are not getting errors, it is probably because warning are set to off via: docmd.setwarnings = false and you should turn them back on after running statments via docmd.setwarnings = true. If you are testing something new I would leave them on until it is validated and then turn it to false during the statment, just make sure you turn it back on afterwards.

Edit:

You keep changing your orginal question with updated information and you should not do that, you should only add new information it confuses other and the people trying to help.

Here is the entire function you need:

Private Sub Command31_Click()

    Dim PartNo, TotalQty, Description, Manufacturer, TotalCost, Category, Warehouse, BinLoc, OrderNum, DateRec, UnitCost, ReturnCode, Batch, bookinsql
    PartNo = Me.[PartNo]
    TotalQty = Me.[TotalQty]
    Description = Me.[Description]
    Manufacturer = Me.[Manufacturer]
    TotalCost = Me.[TotalCost]
    Category = Me.[Category]
    Warehouse = Me.[Warehouse]
    BinLoc = Me.[BinLoc]
    OrderNum = Me.[OrderNum]
    DateRec = Me.[DateRec]
    UnitCost = Me.[UnitCost]
    ReturnCode = Me.[ReturnCode]
    Batch = Me.[Batch]
    bookinsql = "INSERT INTO LiveQTemp (PartNo , TotalQty , Description , Manufacturer , TotalCost , Category , Warehouse , BinLoc , OrderNum , DateRec , UnitCost , ReturnCode , Batch ) VALUES ('" & PartNo & "', " & TotalQty & ", '" & Description & "', '" & Manufacturer & "', " & TotalCost & ", '" & Category & "', '" & Warehouse & "', '" & BinLoc & "', '" & OrderNum & "', #" & DateRec & "#, " & UnitCost & ", '" & ReturnCode & "', " & Batch & ");"
  '                              ^
  '                              |
  '                             Change this table to whatever table you are trying to put it in, you have listed two different ones

    DoCmd.RunSQL bookinsql

End Sub

Upvotes: 2

Related Questions