Writing from form/sub-form to a table

[XYZ0]

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:

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;
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.

I also tried to add a check box in the subform for each product so when I check it to do an afterUpdate and write the data to the table but culdnt figure out how to select the main form items in the VBA code. Anyhow, the first solution would be best because this one would require to check every product in the subform.

If more details needed please let me know.
Thanks!

[jforbes]

Are your Purchase Orders and Purchase Order Details Forms Bound to a Table or Query or are they Unbound?

[XYZ0]

Yes they are, to tables.

[jforbes]

You'll probably want to try something like this instead. The code was created freehand and will not work without you tweaking it and getting it right for your environment:

Private Sub cmdOrder_Click()
 
    Dim strSQL As String
 
    strSQL = ""
    strSQL = strSQL & "INSERT INTO StockMovement ( "
    strSQL = strSQL & "  ID_Product "
    strSQL = strSQL & ", Status "
    strSQL = strSQL & ", Quantity "
    strSQL = strSQL & ", ID_PurchaseOrder "
    strSQL = strSQL & ") SELECT "
    strSQL = strSQL & "  PurchaseOrderDetails.ID_Product "
    strSQL = strSQL & ", PurchaseOrder.Status "
    strSQL = strSQL & ", PurchaseOrderDetails.Quantity "
    strSQL = strSQL & ", PurchaseOrder.ID_PurchaseOrder "
    strSQL = strSQL & "FROM PurchaseOrder "
    strSQL = strSQL & "LEFT JOIN PurchaseOrderDetails "
    strSQL = strSQL & "ON PurchaseOrder.ID_PurchaseOrder= PurchaseOrderDetails.ID_PurchaseOrder "
    strSQL = strSQL & "WHERE ID_PurchaseOrder=" & Me!ID_PurchaseOrder
 
    CurrentDB.Execute strSQL, dbFailOnError + dbSeeChanges
    Me.Requery
 
End Sub

The version you have will only insert one record no matter what. The supplied version will insert a record for each Detail Record. If you need to debug this, put a break on the .Execute line and enter "?strSQL" into the Immediate Window and hit enter. It will display the SQL to you so that you can copy and paste it into a new Query to debug. ...How to Debug SQL String

[XYZ0]

Thanks a lot for your time. I tried the above code but I get an error "The specified field ID_PurchaseOrder could refer to more than one table listed in the FROM clause of your SQL statement (Run-time error 3079). I assume PurchaseOrder and PurchasOrderDetails refer to the tables and not the form? (sry for the noob question).
Back to the error, I have only one table named PurchaseOrder and the ID_PurchaseOrder appears only in the PurchaseOrdersDetails table as a foreign key and to the StockMovements table, also as foreig key.

Edit: I have included next :

strSQL = strSQL & "FROM PurchaseOrder, [PurchaseOrderDetails] "

But now I get a syntax error in JOIN operation

[jforbes]

I really didn't expect the code to work as it is. It's an example of how you can structure a SQL Insert Statement to do what you are attempting.

Yes, PurchaseOrder and PurchasOrderDetails refer to the tables. When using SQL, it's much easier to stick to SQL as much as possible. In fact in the example, the only reference to the Form is the line:

strSQL = strSQL & "WHERE ID_PurchaseOrder=" & Me!ID_PurchaseOrder

where it gets the current ID from the Form and uses it to determine what records to insert. Which should probably be:

strSQL = strSQL & "WHERE PurchaseOrder.ID_PurchaseOrder=" & Me!ID_PurchaseOrder
OR
strSQL = strSQL & "WHERE PurchaseOrder.ID=" & Me!ID_PurchaseOrder

I put a lot of assumptions into the example code, like Table Names, Field Names, and table relationships. If the field ID_PurchaseOrder only appears in the PurchaseOrdersDetails, then what is the corresponding field in PurchaseOrders? The JOIN will need to be updated to reflect this, as I assumed they were the same in both Tables.

[XYZ0]

Ok Im new in SQL, I read the code like this. Hey code in the table StockMovements in her fields ID_Product, Status, Quantity and ID_PurchaseOrder insert PurchaseOrderDetails.ID_Product, PurchaseOrder.Status, PurchaseOrderDetails.Quantity, PurchaseOrder.ID_PurchaseOrder FROM the tables PurchaseOrder and PurchaseOrderDetails and use a LEFT JOIN which will return all rows from the left table (PurchaseOrder), with the matching rows in the right table (PurchaseOrderDetails) and to do it some compere the ID_PurchaseOrder from the first table with the ID_PurchaseOrder from the second table WHERE the ID_PurchaseOrder is same as the one in the main form (didnt quite understand the where statement yet).
Now, I don understand the error I get, the code looks fine :(

[jforbes]

There are two types of SQL Insert Statements that are used the most often.
The first is what you were using: SQL INSERT INTO Statement If you read the article, you should notice that it only inserts one record into the Target Table.
The second is what I recommend you try to use: SQL INSERT INTO SELECT Statement Hopefully, you'll notice that the records that are INSERTED are from the SELECT statement, which is not limited to one record.

The key to making a SQL INSERT INTO SELECT Statement work is to have a well written SELECT statement. You can develop the SELECT Statement by itself in the Query Editor without the INSERT statement. You can then add the INSERT portion in the Query Editor or you can copy-and-paste in into VBA and get it working in Code. Regardless, getting the SELECT statement correct tends to be the biggest obstacle with this statement.

[XYZ0]

I did it. Opened the query builder, added the 2 tables, PurchaseOrders and PurchaseOrdersDetails and the selected the needed fields. The SQL text was like you wrote it, just instead the LEFT JOIN it was an INNER JOIN, however, I still get the same error "Syntax error in FROM clause 3131" when I added the [PurchaseOrdersDetails] in the FROM line then I get the syntax error in JOIN operation (3135). I read the link you posted but it states the situation when u take data from one table and copy to another but not when u take data from 2 or more tables and then copy to another one. I researched a bit fro 2 tables or more into another and found to use the UNION, but not quite sure how to do it in VBA.

[jforbes]

A UNION will merge two Sets of data. Usually the two sets of data are similar in structure, and typically have the same columns. I'm pretty sure this isn't what you are after.

A SQL JOIN will link two tables together on a Key column(s) so that they can be worked with as one Set.

On the surface the two may sound similar, but they aren't.

If you want to post your SQL, we can look at it see if it can be helped.

[XYZ0]

I thought so :D
I did read all the JOINs in w3school and the union.
The VBA/SQL is exactly the same as you wrote in the first post, just different names because I use another language, and not english unfortunately.
Would you like to take a look into the database? Just the language is different and probably it will confuse you :/

[jforbes]

Whatever you want to do. I'm not concerned about a different language as there should be enough keywords to figure out what is going on.

[XYZ0]

https://1drv.ms/u/s!AgExhJ1MpmCGiHJG8Fh2pBs_in_g
There you go. Here some help...
frmNarudzbenice = frmPurchaseOrders
frmNarudznebiceDetalji = frmPurchaseOrdersDetails
Proizvod = Product
Kolicina = Quantity
In the form frmNarudzbenice the button "Naruci" triggers the code on click..
Ask if you need something else to translate :D
And big thanks!

[jforbes]

If you don't mind, would you Zip the file and attach it here?

If you use the Advanced Editor, there is an Additional Options section that allows you to Attach Documents.

[XYZ0]

For some reason it fails to upload, and the zip has only 2MBs and max is 5MBs. Where else could I upload it? is mediafire or dropbox fine?

[jforbes]

Maybe a public DropBox link?

[XYZ0]

https://dl.dropboxusercontent.com/u/.../Prodaja_1.zip

[jforbes]

Here you go:

Private Sub cmdNaruci_Click()
 
    Dim strSQL As String
 
    strSQL = ""
    strSQL = strSQL & "INSERT INTO KretanjeRobe ( "
    strSQL = strSQL & "  ID_Proizvoda "
    strSQL = strSQL & ", VrstaKretanja "
    strSQL = strSQL & ", Kolicina "
    strSQL = strSQL & ", ID_Narudzbenice "
    strSQL = strSQL & ") SELECT "
    strSQL = strSQL & "  NarudzbenicaDetalji.ID_Proizvoda "
    strSQL = strSQL & ", Narudzbenica.Vrsta "
    strSQL = strSQL & ", NarudzbenicaDetalji.Kolicina "
    strSQL = strSQL & ", Narudzbenica.ID_Narudzbenice "
    strSQL = strSQL & "FROM Narudzbenica "
    strSQL = strSQL & "INNER JOIN NarudzbenicaDetalji "
    strSQL = strSQL & "ON Narudzbenica.ID_Narudzbenice = NarudzbenicaDetalji.ID_Narudzbenice "
    strSQL = strSQL & "WHERE Narudzbenica.ID_Narudzbenice = " & Me!ID_Narudzbenice
 
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
    Me.Requery
 
End Sub

There were two typos, I'll let you figure them out. =)

I included this image of the Debugging so that it may help you in the future:

By putting a break in the Code, the SQL String can be printed out in the Immediate Window, then the SQL can be Copied and Pasted into a Query Editor so that the SQL can be tweaked without running the code a bunch of times.

[XYZ0]

strSQL = strSQL & "WHERE Narudzbenica.ID_Narudzbenice = " & Me!ID_Narudzbenice

I figured out only this typo, cant find the other one. Will need to see how to enable the immediate window.
Dam* so much time wasted for ta typo -_- lol
Thanks a lot, u rock :D
Btw. im curious what was the second typo xD

One more quick thing. Can I add some code to gray out the button after its pressed, but guess I can google that

[jforbes]

Here is the other one:

strSQL = strSQL & "FROM Narudzbenica, [NarudzbenicaDetalji]"

Glad your up and running. If you get stuck on the Enabling/Disabling of the Button, start another question and include what you've done and we'll help.