Sorry about that. I didn't want to waste your time (and my fingers) if you
already knew about that
A foreign key field is a field which is the Primary Key of another table
For tables to be linked, the tables need a Primary key field
When tables are linked, any data in one table is available to the other
So if I had a Customer Table with CustomerID (An Autonumber Primary Key)
CustomerName
CustomerAddress
And if I had a Sales table with
SalesID (An Autonumber Primary Key Field)
CustomerID (A number field - the Foreign Key field, linked in the Access
Relationships Window from CustomerID in the Customer Table)
I have immediate access in the Sales table to the customer's address.
It saves me re-typing all the name and address details for that customer
each time I sell him something (with the risk of entering it wrongly)
You are using text data as your primary key by the sound of it. It will
almost certainly be easier, in the long run, to use the more usual
structure - it will make later coding easier and save the risk of mistyping
a primary key. It also leaves you free to change eg the word Fire Hazard to
Fire Risk if you wanted to- with your current structure it would be very
difficult.
It makes more work having this structure initially but will save work in the
long run
the sort of structure that would work, would be:
TblDomain
DomainID (autonumber primary key)
Domain (eg Cr, SR)
Any fields only about the Domain itself and nothing to do with the risk -
you could have a field with CR and SR in 'long hand'
(note that each value is only entered once)
TblRisk
RiskID (autonumber primary key)
Risk (each risk entered only once eg Fire Hazard )
Any fields about the risk alone and nothing to do with the Domain
TblMainInfo
CatID (autonumber primary key)
DomainID (Number field) (Foreign Key linked from TblDomain)
RiskID (Number field) (Foreign Key linked from TblRisk)
Any fields about that risk in that particular domain
Click on TblDomain in the Database window
Go to Insert Form
Choose a Columnar (single) form)
Open your new form in Design View
Drag TblMainInfo into the form.
The wizard will kick in and suggest a link to DomainID
Save and Close
Open your newly created subform in Design View
Add a combo box to it based on TblRisk
choose to Store the Value in RiskID
Using the combo, add the risks that apply to that Domain
If you wanted to see the Domains in a list under each risk, then you could
use the same method to create a mainform based on TblRisk a Subform still
based on TblMainInfo and a combo box based on TblDomain
As you turn the pages of your main form, you will only see the domains
listed that apply to that risk.
Evi
Cathy said:
Hi Evi
Thanks for replying but as a novice this is gobbledygook to me, I was lost
when you started talking about a foreign key!
Basically I have three tables: called Risk (Pick List) text only, Domain
(Pick List) text only and Main Info Sheet.
The Domain (Pick List) consist of the following
Domain
SDR
CR
OR
The Risk (Pick List) consists of the following columns:
Category Domain
Fire Advice SDR
Security Advice SDR
Social CR
Political CR
Technical Advice SDR
Environmental CR
The Main Info Sheet gathers all the information entered from the Input Risk
Form and it's on this form that I have combo boxes that read from the pick
lists. This is where I need it to filter so that if you pick SDR only those
with the domain SDR will show.
Hopefully this is a bit clearer, It's hard to explain yourself when you're
not sure of the Access terminology.
Thanks for your help.
Evi said:
Assuming that your Risk Table has a foreign key link from the Domain table
(I'll call it DomainID)
Call the combo
cboRisk and cboDomain
cboDomain has the fields DomainID (hidden) and Domain
We'll call the form YourForm
Open Properties in the form's Design View
in the After Update Event of cboDomain put
Me.cboRisk.Requery
Click next to RowSource of cboRisk to open the query on which it is based.
In the criteria row under DomainID put
[Forms].[YourForm].[cboDomain]
Please say if any of this is gobbledygook to you. It will help if you give
me the name of your form, the name of the field you want to filter by
(stating if it is text or number) and the name of your combo
Evi
Help, I'm stuck.
How do get a combo box on a form to filter information. Let me explain.
I'm
creating a 'Risk' Database and need to break a list of risks into domains.
There are three domains: SDR, CR & OR which all have a list of potential
risks attached to them. I need the form to be able to sort them into their
individual domains, for instance when someone clicks on the Domain combo
box
they get the above three options, when they choose one of these I then
need
the 'Risk' combo box to only show the risk descriptions allocated to that
domain.
Example: Fire Advice, Security Advice and Technical Advices come under the
SDR domain, while Social, Environmental and Political come under the CR
domain, (at the moment they are all on the same table with their
individual
domain codes next to them in a separate column), so when someone chooses a
CR
domain they only see the Social, Environmental & Political risks.
I hope this makes sense to someone, thanks for any help given.
