Why cant we just get along...

[mcclunyboy]

I like Access, I am always dabbling with existing databases. I like to think I am not a complete novice.

However whenever I go to create my own DB i always get stuck trying to make something look like something on another DB. Generally speaking it is when I try and link tables and forms.

A common example:

Simple DB of applications in use with my authority - 4 tables;
Apps, Backups, suppliers, servers

I would like 1 form which would enable you to enter application details, then a button to allow you to open a 2nd form to enter server details linked to that application, then a 3rd form to allow you enter backup(maintenance) plans for that server...

It never works - what should I be doing to achieve this?

 

[gemma-the-husky]

the first thing is to get your data structure correct.

can you post the field details you are using within your tables

 

[mcclunyboy]

ok say I did:

tblApps
id
app name(pkey)
suppliername(fkey)
server(fkey)

tblsuppliers
id
suppliername(pkey)

tblservers
id
servername(pkey)
backups(fkey)

tblBackups
id
backups(pkey)

Then for ease of use create a form to enter an application, all fields can be displayed. The user types in app name, then server name - ideally once they have typed the server name in I would want a 2nd form to appear with the server name populated so i could enter further details...

What is general procedure to achieve this?

 

[The_Doc_Man]

First, perhaps you should use a different ID field name for each table. Like AppID, SrvID, SupID, BckID... because when you have a JOIN query that common field name will force you to do a LOT more typing. Access is smart enough to not qualify a field name by its table when the field name is unequivocal.

Second, if I read your intent correctly, read up on the subject of "Cascading Combo Boxes" as a way to make those things work like you want. Also, it is eminently possible to create sub-forms that link to some common field using parent/child linkage. Or you can put a filter on a form before you open it.