Linking Forms

[hitsupport]

Hi ya,

I would like to link my forms so that as i move through them the foreign key will automatically update in each.
I know that the access wizard can do this automatically with 2 tables in the form wizard if you create a normal for with a subform. But I can't link multiple forms that are dependent on each other. Also, I know that I can link forms when I create a new form button but it won't jump to the next form unless a record (foreign key) already exists.

I have previously been given some vba code to link the forms but it won't work for me.

This is a link to a sceenshot of my database relationship/ table structures and vba code: http://www.waynedougan.me.uk/access_help.GIF

Please help!

Wayne

 

[MStef]

Hello Wayne!

1) I think you have to learn something about DATABASE NORMALIZATION.
You have to Normalize your tables. You don't need the same data in two
tables, (redundance).
2) After that make "frmContact" as a subform in "frmCompany".

 

[hitsupport]

Re:

Hi MStef

I should of explained my project a little better. My normalization has been completed. I do have more than just 2 tables.

The database will also hold information about servers and connected hardware devices.

My main tables/ relationships include:

tblCompany - 1:N - tblContact
- 1:N - tblServer - 1:N - tblWorkstation - 1:N - tblWsHardwareJunction - N:1 - tblHardware

1 Company has many Contacts
1 Company can have many Servers
1 Server can have many Workstations
Many workstations can have many peices of Hardware
Many pieces of hardware can be connected to many peices of hardware

Image of Table Structures/ Relationships
http://www.waynedougan.me.uk/relationships.GIF

tblCompany is made up to hold company information only. tblContact is used to hold company contacts information only. The reason I have structured my tables the way I have is because the companys we deal with are made up of many divisions and I feel that the structure is a good way to separate the data and make the database more functional. For example:

Microsoft has several departments with one main location.

Company Contacts
-------------------------------
Microsoft - 1:N - windows xp
- 1:N - windows server
- 1:N - crm
- 1:N - office

Table field names are duplicated but the actual data is not.
If i didn't set up the tables the way I have then I would be left with duplicate company names, addresses etc.

I want to link forms with the foreign key being automatically updated in each so that the flow between forms is simple for the end user. I know that if I add Contacts as a subform in Company this will work, but only because it has only one dependent (tblCompany). Unfortunitly my other tables are dependent on tblCompany also. tblHardware is dependent on tblWorkstation, tblWorkstation on tblServer, tblServer on tblCompany. I can only define what company a certain peice of hardware comes from if they are all linked.

All thoughts are welcome as I'm still learning. I would really like some code help coding my forms so that the foreign key is updated. If there is a better way to construct my database please note to me

Please help.

Regards,

Wayne

 

[Appie]

http://www.microsoft.com/downloads/...5e-a29b-4a43-8508-78e549ecd14c&DisplayLang=en