Dynamic Fields in Access Form

[VBABasix]

I have a table that has a row for each order number. I would like to display in a form the customer and all orders that have been placed and the date of the order. How do I create the fields in the form dynamically?


Thanks

 

[VBABasix]

There is one main table that contains the a line item for each piece of equipment that the cusotmer has ordered. So if a customer has 3 pieces of equipment there would be 3 lines.

 

[Norie]

So what's actually in this main table?

Is it the only table?

 

[VBABasix]

The table identified is the primary table, meaning that it includes all the customer information such as account number, equipment ordered, date ordered, date shipped, sales information, etc. This is then tied to the equipment value table, and the state table and the correspondance type table and the sales assignment table.
I then have seperate tables to identify the value of the equipment, the user that updates the table, and another table that identifies correspondance types, a table for the states, and then 2 tables used to itentify sales assignment.

Thanks

 

[Norie]

I think you really need to consider restructuring your data.

I'm still a little unclear of your setup but it sounds to me as though you are repeating a lot of (customer?) data in your 'main' table.

 

[VBABasix]

Thanks for your help. I will go back and start over the main problem I am facing is that the main table data is coming from an external source.

Thanks again!

 

[VBABasix]

I broke the main table down into 2 tables as follows:

Cust_mast table (Contains cust number address etc)
Equip Table Contains a primary id, cust number from the cust_mast table and the equipment information.

This leads me back to the orignal problem. In my form I have a main form for the customer info and a subform for the equipment info. In the report I am trying to output the cust info with all equipment associated. If I add the ID from the sub form to the main form in order to pass it to the report, it only picks up the first ID in the subform and not the multiple ID.

Thanks

 

[SydneyGeek]

About the tables:

As a minimum, you will need --

Customers
CustomerID
and all cusotmer-related fields

Items
ItemID
and all item-related fields

Orders
OrderID
CustomerID
ItemID
and date, etc.

Then, relate Customers to Orders and Items to Orders.

What you have described is a many-to-many situation (one customer could place many orders, and one item could appera on many orders). The above is the standard way to deal with many-to-many: you MUST have a linking table, otherwise the setup just won't work.
If you have the Northwind sample database on your system, go take a look. Also, go to the Microsoft Knowledge Base and run a search on Understanding Relational Design. It shows how to set up the table structure, based on Northwind.

Denis

 

[VBABasix]

Thaks for all the help. I have gone back and am re-designing the database. THe main issues that I am facing is that the data is coming from an external report and so I have to go through several stept to break the data out into multiple tables.

Thanks again and Happy Holidays

 

[Norie]

You should be able to automate splitting the table up with code.

It might be hard to implement at first but once done will save you a lot of work.

Post back if you need more information.