Link to home
Start Free TrialLog in
Avatar of Dalexan
DalexanFlag for Afghanistan

asked on

Update order records with item information

I have two tables one containing order information and another containing item information. one order to many items. I need to update the order table with the item information. Please post the VBA on how to loop through the item table and update the order table.

Order table fields look like this:
Name,qty1,qty2,qty3,qty4,qty5,qty6,sku1,sku2,sku3,sku4,sku5,sku6,uniqueid

item table fields look like this:
uniqueid,sku,qty

within the item table there could be up to 6 item records per uniqueid.
Avatar of Sheils
Sheils
Flag of Australia image

Usually a simply form/subform set should do the job. No VBA required. Noting the field names qty1,qty2,qty3.... I suspect that the problem is with your table design. Can you explain what are these fields used for. Even better if you could post a sample of you DB

Avatar of Dalexan

ASKER

db1.mdb

I have uploaded a sample database to be used to show an example of what the tables look like. They are actually much bigger with many more fields. I need to know how to update the order records with the item information using VBA. The actual tables are linked tables to a larger mysql remote database.
As I mentioned in the previous post, I think that there is a problem with your table design.  Instead of developing complex code to cater for poor design it's better to fix the design, which if not fix will give you a lot of problem as you progress with the development.

So Please answer the following question

why do you have qty1,qty2,qty3.... and sku1,sku2,sku3.... in your order table.

I am stressing on this because I would normally expect something like

tblOrder
--------
OrderID, Name,Date

tblItem
ItemID,OrderID,sku,qty
Avatar of Dalexan

ASKER

These are linked tables in a database that I cannot modify. I have to loop through the order table get the uniqueid, match it to the corresponding uniqueid in the items table and update the order records qty and sku values with the values from the item table.

I want to stress again that I cannot modify the table structure, these tables are part of a much larger complex system and are used for other process's. My task is to update the quantity's 1 through 6 and sku's 1 through 6 in the order table.
Avatar of Jeffrey Coachman
Dalexan,

I agree with sb9.

While it may be true that you cannot modify the tables, it is also true that they (or at least this one table) are not "Normalized".

So the need here is for the original designer of these tables to "modify" them.

Left in this "un-normalized" state this table will forever require workarounds to get it to function like a true "Normalized" table.
(Like your situation here)

Other disadvantages of this design will be that it may have forced other tables to follow a similar design, and that getting summaries, or "Range Data" out of a table like this will be difficult.

JeffCoachman
I am not saying that this is impossible, or that we won't help you...

It's just unfortunate that you have been given an inefficient design and been asked to "make it work".
:-(

So sit tight, perhaps a workaround will be forthcoming...
;-)

Jeff
Avatar of Dalexan

ASKER

Thank you, I agree that is is very unfortunate that I have to work with this table structure. The short and simple explanation is that the order table is a temporary table that is used to export a FIXED FIELD text file that is imported into a mainframe system.....rex....or something like that. This may explain why this table is structured the way it is.

I'm just looking for some VBA code that will loop through the order record, get the unique id, then loop through the items matching that unique id and fill in qty1-6 and sku1-6 in the order record.

Thanks for your quick attention and comments.
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I had to convert the linking field to a Long, because it is text in one table and Long Integer in the other -- I assume you can't change that either.
If there could be more than 6 SKUs, you could add a few lines of code to stop at the 6th.
I have added a temporary table and a test form to the sample that you post. Clicking the button on the temp table will populate the temporary table. If the result in the temporary table is what you would like to insert in the item table we can proceed further with this approach.
db1.mdb
Avatar of Dalexan

ASKER

I will review this and get back tomorrow, thanks for your quick responses.
Avatar of Dalexan

ASKER

Thank you Helen, This is exactly what I was looking for. Works excellent.

Also thanks for the debug window prints. Makes it much easier to see whats happening when debugging.