Inserting multiple records from form into table

[illusionek]

Hello!

I have a tabular form with many rows of records. Users add some additional information and now I would like to insert it into a new table.

I tried to use below code and it works but it inserts only first row out of many. So I just wonder how to amend it to insert all data?

Pls help

Many thanks

CurrentDb.Execute "INSERT INTO [PO Lines - Table] ([SKU], [SKU Description], [Barcode], [Qty], [Unit Price], [PO Number], [Range])" & _
" VALUES ('" & Me.SKU & "','" & Me.SKU_Description & "',''" & Me.Barcode & "''," & Me.Qty & "," & Me.Unit_Price & ",'" & Me.PONumber & "','" & Me.Range & "')"

 

[BlueIshDan]

Check to see if you have more than one record being parsed
- What event are you using to trigger this code and how are you doing so.
(show us the rest of your code)

Thanks for using code braces

 

[pr2-eugin]

Any reason why this form is not set to be bound to the table? Why the complication of unbound forms and coding to add records?

 

[illusionek]

Hi,

Thanks so much for your prompt response.

I use command button to trigger this on the form. And above is just the whole code.

I know for sure that I will have more than one record each time. I tried to loop it but without any luck ;/

It is unbound form because some data are retrieved from the existing table but then user needs to input some other information i.e. order number and dates and then I want based on that create another table that would have all records for this particular order.

 

[pr2-eugin]

It is unbound form because some data are retrieved from the existing table but then user needs to input some other information i.e. order number and dates and then I want based on that create another table that would have all records for this particular order.

Sounds like a perfect recipe for disaster ! You only need one table, and that it should be bound to the form ! You only need to navigate to the actual record and update it.

 

[illusionek]

If I have done it this way the table would be really big. I had to split data into two forms. I create a Sales Order Template. So there is some data at the line level like product codes, prices, quantities etc but you have also data at header level, like supplier name, delivery address etc. So if I used bound form I would be repeating header data at line level without any need.

My code above works fine for the header table but I need to be able to loop it for line level. Can you help with this? I will be linking data from both tables by order number. So all is fine, I cannot just work out the right code for inserting multiple lines into a table.

 

[jdraw]

illusionek,

Do you have a clear statement describing WHAT your database is about? You really need a clear statement of your "business" and the associated rules. Then a model of your tables and relationships that support the business and rules.

It appears to me that you may have jumped into building forms before clearly identifying the business.

Good luck with your project.

 

[illusionek]

Is there anyone who would be able to help me with my problem? I know exactly what I need to do. I have only a problem with this step of my design.

 

[jdraw]

Can you please tell us?

I know exactly what I need to do.

 

[illusionek]

I have 3 tables: 1. with orders from customers, 2. with details about products like prices etc, 3. with supplier details like addresses etc

I set up relationship beetween them and now I have query that links them both. I have created a form that submits parameters to the query, so I can get orders just for a particular customer.
This is where I got so far.

Now I need to do a form which will be taking some data from mentioned above query like details about the order etc but also at the same time, user has to input additional data like order number, delivery date etc. Once user entered this data, I have all details for the order. I would like to add everything to a new table and based on that table I will be doing a sales orders, which is basicially a report.

I cannot do a bound form to the existing tables because these tables are imported from the Excel files, so they will constantly change.

The reason I need to submit data from form to new table is that if I need to do changes to the existing order, I would like to deveop another form to do so, which in this case would be a bound form.


Any suggestions ?

 

[GinaWhipp]

Thinking out loud...

Why not base the Form on a Temp Table. Then, after all the additions are done append the Temp Table to the Live Table. And, you could also make it so you have a Complete (Yes/No) field which you could use a trigger to only APPEND the ones that are done, as well as, deleting the ones that are done.

 

[illusionek]

Hi Gina,

Many thanks for your suggestion, it sounds like a good idea. I know how to do most of it but I am not sure about Complete (Yes/No) field and how to use it as trigger to either append or delete records? Would you be able to explain a little bit more please?


Many thanks.

 

[GinaWhipp]

In your query to APPEND records to the Live Table...

INSERT INTO YourLiveTable
SELECT YourTempTableFields, etc...
FROM YourTempTable
WHERE (((YourTempTable.Complete)=True))

Then delete those same records...

DELETE YourTempTable.*
FROM YourTempTable
WHERE (((YourTempTable.Complete)=True))

....so the only ones left are the ones still waiting for information. This way if any records are not completed, they can stay there until done. Make better sense now?