Adding to a Lookup Table

[Guest]

I have two tables that are used as lookup tables by queries and forms
throughout the database.

tblVendors = PK VendorID
tblSites = PK SiteID, FK VendorID

I also have a table = tblFullAddress w/ fields FullAddress and FK SiteID to
connect it to tblSites. In tblFullAddress the address and city from tblSites
are concatenated into the FullAddress field, e.g. 101 Prospect, Hartford.
When users do a site visit, they pull down a list of sites that are these
concatenated fulladdress records then enter other data via a subform to the
tblSites.

This works great so far. Now I am trying to make a form to add a new site
to an existing Vendor. I have a dropdown of existing Vendor names and a
subform that lets them input into the tblSites. The trouble I have is when
they put in the new address and city to the tblSites it does not
automatically populate the concatenated address and city into tblFullAddress
for future use in all the drop downs I use in other forms.

If you can understand my long explaination - can you help?

 

[Amy Blankenship]

I have two tables that are used as lookup tables by queries and forms
throughout the database.

tblVendors = PK VendorID
tblSites = PK SiteID, FK VendorID

I also have a table = tblFullAddress w/ fields FullAddress and FK SiteID
to
connect it to tblSites. In tblFullAddress the address and city from
tblSites
are concatenated into the FullAddress field, e.g. 101 Prospect, Hartford.
When users do a site visit, they pull down a list of sites that are these
concatenated fulladdress records then enter other data via a subform to
the
tblSites.

This works great so far. Now I am trying to make a form to add a new site
to an existing Vendor. I have a dropdown of existing Vendor names and a
subform that lets them input into the tblSites. The trouble I have is
when
they put in the new address and city to the tblSites it does not
automatically populate the concatenated address and city into
tblFullAddress
for future use in all the drop downs I use in other forms.

If you can understand my long explaination - can you help?

Instead of having a separate table for FullAddress, concatenate the address
in the query that makes the row source.

HTH;

Amy

 

[Guest]

Could you give me an example of the language I might use in the criteria in
the query?

 

[Amy Blankenship]

Could you give me an example of the language I might use in the criteria
in
the query?

Select [Street] & ", " & [City] AS FullAddress FROM tblSites WHERE
tblSites.VendorID = Forms!Vendor!SiteID

Something like that.

HTH;

Amy