You can make the agents a separate table and relationally join it (via the autonumber or other ID field in the main table). Just add the corresponding ID field as an integer in the agents table.
Or you can make 2 agent fields in your main table (although this wouldn't be ideal - if a third agent is added, then you must do some work (ie. add fields/revise form) whereas setting it up relationally, you don't need to do anything)
As far as designing the form with a relational setup, it's a simple subform (probably set to continous). Try a couple examples of these (after you've setup proper relationships) and you'll get the hang of it. I personally like to put the ID field (for the agents relational table) in the subform and set the default value of it: =Forms!MyMainFormName!MyAutoNumberField. The subform wizard works well in MSAccess for setting up subforms once your relationships are established.
If you need more help, pages 6 or 7 in the code bank have some helpful tips on design.
You'll need to come up with a table structure design first though (we don't know all your requirements), then send a picture of it with your relationships and we can then offer you advice. Providing the table structure is setup correctly, form design then becomes fairly easy.
As far as advice on the table structure, if there's information which would cause unneccessary duplication in your main data tables (such as agent), that information should most likely go into a relational table linked by an ID (or primary key) field to the main data table.
It sounds like you've got a general idea. Just create the table relationships and post a snapshot of them. If you need further help, MSAccess 2007 has a lot of great examples. Otherwise 2000/2003 has the Northwinds database to look at for relationships.
Last edited by pkstormy; 03-27-10 at 03:22.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)