relationship troubles

[raistlin]

here is what i have:

tables - tblAssets, tblSites, tblManufacturers, tblModels

i have 1-to-many relationships set up as follows; tblManufacturers --> tblAssets, tblManufacturers --> tblModels, tblModels --> tblAssets, tblSites --> tblAssets.

what i would like to happen is if i add a new site in tblAssets it will add the site in tblSites as well. if i have "enforce referential integrity" checked then it will only let me insert sites that are on the tblSites table, and if i uncheck the box then it will allow me to put in a site that's not on the table, but it doesn't add it to the table.

i only have 1 form and it's just linked to the tblAssets table. please forgive my noobness, i've been having a rough time trying to understand relationships even though i've read about everything i could find on them, so could someone please point me in the right direction?

thanks

 

[KeithG]

You should only be storing info about a site in tblsites. In tblAssets you should be using the PK from tblSites to associate your asset to a site.

 

[The_Doc_Man]

To help understand this better, it would help if you indicate at least something about the keys used to support the relationships.

1. tblManufacturers --> tblAssets

2. tblManufacturers --> tblModels

3. tblModels --> tblAssets

4. tblSites --> tblAssets

It looks to me, in the absence of knowledge about keys, that the #1 relationship is redundant because of #2 and #3 being able to supply this information.

If you have tblManufacturers : tblModels :: 1 : many, this makes sense

But if you want relational integrity to kick in for tblSites, (1) the arrows are not right and (2) there appears to be inadequate information or relationships for tblSites to know where to form the link.

EITHER your description is incomplete for us to be able to advise you OR your design is deficient in what the Sites/Assets relationship really means.

 

[raistlin]

thank you for the quick responses. here is my db so you can see what i'm talking about.

 

[KeithG]

Can you convert your db to the 2002-2003 format? I don't have access 2007

 

[raistlin]

my appologies. here's the file with both formats.

 

[KeithG]

Check this out. Is this what you want?

 

[raistlin]

that's exactly what i want it to do. thank you very much

 

[raistlin]

i have another question on the same topic. i took the "On Not in List" event data from the Site combo box, which is;
-------------------------------------------------------------------------
Private Sub cboSite_NotInList(NewData As String, Response As Integer)

Dim lngAns
Response = acDataErrAdded
lngAns = MsgBox("Site " & NewData & "is currently not an option in the list. Would you like to add it?", vbYesNo)

If lngAns = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("Insert into tblsites(Site) Select '" & NewData & "'")
DoCmd.SetWarnings True
End If
End Sub
-------------------------------------------------------------------------

and i changed some of the names so it would work with other combo boxes. i got it to work for cboManufacturer, but when i try to do it with cboModel it keeps coming up with an error. it will add my new entry to the tblModels!Model, but since there isn't a Manufacturer name next to the new entry it still says i must chose something from the list.

is there a way i can have it enter the manufacturer name and the model number at the same time?

i've tried adding the line;
DoCmd.RunSQL ("Insert into tblModels(Manufacturer) Select '" & cboManufacturer.Value & "'")

and different variations of that line to try and get it to enter in a manufacturers name at the same time that it enters a new model but i'm not having much luck.

also, i have cboModel get its row source from an after update event with cboManufacturer, and this is the reason that it wants a manufacturers name next to the model number in the tblModels table, so i'm not even sure if this is possible.

Thanks again for your time