Entering data in a Form - update Autonumber on two Tables? (1 Viewer)

[monacle]

Hello,

My first post is on something that is troubling me. I have a Form acting as the display and entry point for data for a contact list, which is composed of two Tables as follows:

Contact - (text fields including: first name, last name, phone number home, phone number work, etc)

Industry Role - (yes/no tick boxes including: film, photographer, audio engineer, producer, reporter, etc)

The two Tables have a one to one relationship based on the URN field which is an autonumber. My problem is that when someone enters say a name, and then ticks a box, the autonumber will add two entries because it seems to see the first table then the second tables as sequential, and not the same thing. How do i go about making a form that can enter new records the same autonumber for two connected Tables?

 

[Banana]

Two ways to do it:

1) Base your form on a query pulling both tables. Search the forums on recordsource query.

2) Use a subform. Search the forum about this as well.

 

[monacle]

I noticed you refer to subforms in your advice to another question when i did a search before asking. I will look into recordsource queries and subforms more then. Thanks.

Mostly its a simple database keeping people's details so im sure many others have come across this before. When i started i had one table, then it became apparent two relational tables would be wiser for the future ability to put some search queries into the form to give the user the ability to for instance look at the details of all the audio editors on our contact list (so beware future questions about cmd's in forms!!).

So the primary goal here is a form with text boxes recording new entries to a Contacts table and check boxes recording to a Roles table. Can one get away with simply going to the end of a form and adding new data, or should i set up an append query attached to a command button to handle this neater? Im familiar with aspects of queries and managing data in and out of tempalted databases but ive not built forms before so im tripping up on applying my knowledge to what is efficient for the user to enter new records and in the future call those records up based on criteria. Making a new entry write to two tables is the initial problem here...

EDIT: attached examples of the Form and the Relationship, which illustrates the fields and hopefully what im attempting and also, it shows the way the autonumber is adding one above what should be the same value shared between tables.

 

[Banana]

You have a normalization design problem.

You do not want your table to grow by adding more fields, but by adding more records. (e.g. in the view mode, you want it to grow downward, not to the right).

Therefore- your tables only need to be something like this:

tblArtist:
Name
Address
Street
RoleID (Foreign key related to RoleID in tblIndustryRole

tblIndustryRole
RoleID (Primary key)
Role
RoleComments

and in Role, you type up all positions you just put in the table.

Once you've done that, do a search on how to make a lookup combobox on form. (Do not use lookup tab in the table design; it's problematic.)

HTH.