MS Access VBA Writing to a table from a form

Question

• I have a form (Purchase Orders) and a sub-form in it (Purchase Order Details). The Main form contains Number of the PO (text box), Supplier (combo-box), Employee (combo-box), Status (combo-box) which contains 2 records (New and Done) and a date box (when the PO was created). The Sub-form (datasheet) contains Product (combo-box), Quantity and a Price field.

• What I want to do is to add a button on the main form which will do next.

• When the button is pressed a VBA code should be executed and do next. Take data from the Main form (Number of the PO, Status and the date) and the Sub-form (Product, Quantity and Price) and put all that into a table (StockMovements).

I managed to do that with the next code:

Private Sub cmdOrder_Click()
Dim strSQL As String

    strSQL = "INSERT INTO StockMovement (ID_Product, Status, Quantity, ID_PurchaseOrder) VALUES (" & _
               Me.frmPurchaseOrderDetails_Subform.Form!comboboxProduct & ", '" & _
               Me!txtStatus & "', " & _
               Me.frmPurchaseOrderDetails_Subform.Form!txtQuantity & ", " & _
               Me!txtID_PurchaseOrder & ");"
    DoCmd.RunSQL strSQL
    Me.Requery

End Sub

However, there are 2 problems:

1. As you can see I didnt add the date field because I get an error, cant remember which one it was exactly but I think 2075;
2. The code works without the date, but only adds one Product to the table, the first one. And in a Purchase Order there are usually more than one products.

Because Im totally new in VBA, I would kindly ask you to treat me like a newbie and explain more detailed, if possible. (Fixed the code, forgot to change the language. I mean I pasted the wrong one, now its the right one but still not working of course :))

Thanks!

Answer 1

The date can be inserted this way:

"INSERT INTO StockMovement ([DateField]) " & _
"VALUES (#" & Format(Me!DateField.Value, "yyyy\/mm\/dd") & "#)"

That said, your code will insert one record only. To insert multiple records you need a select query or - much faster - use DAO to open the target table as a recordset and then, as source, loop the RecordsetClone of your subform and copy the records to the target table one by one.

Answer 2

1) If you only have a problem with one specific field, I would check whats special with that one. Probably the input is formatted as a string and the field as Date/Time. In that case try to use the CDate()-Function. I could imagine, that a .Value at the end could solve the problem, too.

Dim datDate as Date

datDate = CDate(Me!txtDate.Value)

2) That your code inserts only one row is absoluterly correct. Remember that e.g. Me!txtStatus.Value is a textbox that contains only one single piece of data. The rows of data are saved in a table and depending on the row you have selected with a main-form (= one row), the corresponding value is shown in the textbox.

INSERT INTO table (field1,field2) VALUES (value1,value2) 

An INSERT INTO inserts one row every time it's executed. So the SQL in the code you have mentioned needs to be repeated. You could do so using loops.

Dim strSQL As String

strSQL = ""

 For Each Item In Group
    strSQL = strSQL & "INSERT INTO table (field1,field2) VALUES (value1,value2)"
 Next

In my opinion, copying data that way is absolutely annoying with VBA. You need to create recordsets, modify and merge them. I would try to solve as much as possible with Access Non-VBA-Solutions.

My question to you: Did you think about linking the form (and sub-form) directly to the table(s)?