Adding multiple records from 1 form

[New2VB]

Hi gurus,

I'm a bit stumped on this one. I am trying to add multiple records from 1 form to 1 table. If you open the "Form1" form from the attached test02.mdb you will probably see what I mean.

The requirement is to add changed prices to each company on a daily basis. I could do this through the "Company" from using the Pricessubform but I would ideally like to be able to launch the "Prices" form, select a company from the combobox, add the new price from the textbox and use the command buttton to programmatically add the records to the table.

The current code works but is a little wieldy (the production db has many more companies than this test db) but I was wondering if there is a more efficient way to do this i.e. some form of loop?

Thanks for you time.

 

[MarkK]

The bigger problem is that the prices table has columns called Tea, Coffee, Sugar and so on. That specific kind of information is data, not structure. You need a Product table so products can be handled with a greater degree of abstraction, not 'hard-coded' into the structure.
As your table design gets clearer and more focussed, the more specific issues of how to update data in tables will get easier.
HTH

 

[New2VB]

Hi lagbolt & thanks for the quick reply.

OK, I have created a Products table and populated it with Tea, Coffee, Milk & Sugar.

I am guessing that I now have to change the code in the OnClick event on the Form1 command button to add the Products table as well...

Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("prices")

how would I include the Products table in the above code?

Also, the production db only has 4 fields that require updating
1. Date- this is set as a default value of Date() -1 as records are updated as of yesterdays date
2. Status- which is just a lookup to a Yes/No option
3. Notes- for entering any relevant information

Therefore, in the production db, there is no hard coding just a table containing the above with the CompanyID field of course. Test02.mdb is just an example to demonstrate what I am trying to achieve without any of the "formal/best-practise" procedures. All I am trying to do is get some sort of loop going so that I don't have to recode the command button on-click event for every record that gets added to the form.

Thanks for the help