Inserting data into multiple tables via access form.

[Shabalaka]

Hi guys

First Off , My apologies if this is in the wrong "section", i wasnt sure what it came under.

Im really new to Access but quite familiar with MYSQL but am really struggling to use Access as a front end to enter data into tables.

Im trying to create a small "test" database using Foreign Keys and Primary keys.

My DB Structure looks like this.

Users :
UserID - AutoIncrement Number (Primary)
Username - Text
Relation - Text

Addresses :
AddressID - AutoIncrement Number (Primary)
Address - Text

Users_Addresses (Relational Table 1 Address can have more than 1 User)
UserID - Number (Foreign)
AddressID - Number (Foreign)

Example Scenario.
Mr Joe Bloggs and Mrs Amanda Bloggs and they have a child named Adam Bloggs live at the same address but use different usernames to log in. (Not allowing them to have the same username)

Now i have managed to create a single form to enter this data but then end up with 3 rows of the address duplication, So to fix this i would like to enter only the addressID and the UserID into the User_Addresses table, I've been completely unsuccessful in using the Form Wizard to create 1 Form to allow me to be able to insert a new user and a new address and then enter the generated keys into the users_address table.

So the final entered data would look like this

Users Table
UserID Username Relation
1 Joe Father
2 Amanda Mother
3 John Son

Address Table
Address ID Address
1 16 Acacia Avenue

User_Addresses
UserAddressID UserID AddressID
1 1 1
2 2 3
3 3 2


If anyone is able to point me to a good tutorial or show me how this is achieved , the help is greatly appreciated.

Sorry for the long post but thought i should try to make it as clear as possible as to what i am trying to achieve .

Many thanks Shab.

 

[r.harrison]

Not quite sure if this is what you were looking for, but I would probably put the addressID in the Users table and then not have the User_Addresses table. Then create the relationship between those 2 tables.

 

[Shabalaka]

Many thanks for the reply r.harrison

Sounds like a better idea thank you , But how would i go about creating a form which would auto insert the Address ID into the Users table ?

Would i need to handwrite a SQL Query to perform this a bit like MYSQL_LAST_INSERT_ID? or something?

So the Form would have Username and Address field , I click create new User i enter a username and a address for this user then i need to insert into the Address table the address string and then insert into the users table the username and the newly inserted address ID? Or can access do this using the relationshp facility ? IE Update all related records?

Many Thanks Shab

 

[r.harrison]

This is only a quick solution but should point you in the right direction.

The Form has bound data to the Users Table, and 1 unbound textbox for the address.

When the Address is changed, the code will search for the address and update the address_ID

 

[Shabalaka]

Many thanks for the reply again

I,ve just taken a look at the database example and that is exactly what i am thinking of

Was the database created with Access 2007 ? Because i see no Data Entry form or any relationship layout in the relationships panel

Edit : I just tried to add a relationship and create a form through the wizard adding the address, relation and username textbox areas to it. When i go to create a new username i get this message.

'Cannot add record(s) join key of table tbl_users not in recordset'

I have looked around for an explanation of this message but there but i couldnt find any explanation which said "Its caused because...", So any help is appreciated

Thanks for your patience with me.


Many thanks

Shab