M
matthew.schrock
I am very new to access and I am trying to create a database to track
vendor information. Currently, I have a table of general vendor
contact information called vendors with primary key of countyID. I
have a liked table called county detail with combo key of countyID and
county. I also have a table named states with a list of all possible
us states, and a table called counties with a list of all state-county
combos. When a user wants to enter data for a new vendor, I would like
to provide them with a list of counties based on the state they select
from a drop down and then let them select all the counties that vendor
services (could be 1 could be 100). I would like to store the vendor
contact info in the vendors table and the related counties in the
linked county detail table. I created a query to pull the related
county informatino by the state selection call counties by state and
here is the code:
SELECT Counties.County
FROM Counties
WHERE (((Counties.State)=[Forms]![Vendors]![State])) OR
(((IsNull([Forms]![Vendors]![State]))<>False));
This seems to work ok.
Originally, I had a main vendor form with contact information and a
county detail subform to enter multiple counties. I was having trouble
because the databse would not let me enter data into the subform until
the main form data was written to the table - gave me a key
relationship error. I have added a command buttom to the main form to
refresh the data and make the county detail subform visible for data
entry. However, now I am having trouble figuring out how to pull
records from the query (code above) but write back to the county detail
table so that the appropriate relationship exists.
Is there a way to pull from a query and then write to another table? Am
I going about this the correct way or is there a better way to do
this????
Thanks
vendor information. Currently, I have a table of general vendor
contact information called vendors with primary key of countyID. I
have a liked table called county detail with combo key of countyID and
county. I also have a table named states with a list of all possible
us states, and a table called counties with a list of all state-county
combos. When a user wants to enter data for a new vendor, I would like
to provide them with a list of counties based on the state they select
from a drop down and then let them select all the counties that vendor
services (could be 1 could be 100). I would like to store the vendor
contact info in the vendors table and the related counties in the
linked county detail table. I created a query to pull the related
county informatino by the state selection call counties by state and
here is the code:
SELECT Counties.County
FROM Counties
WHERE (((Counties.State)=[Forms]![Vendors]![State])) OR
(((IsNull([Forms]![Vendors]![State]))<>False));
This seems to work ok.
Originally, I had a main vendor form with contact information and a
county detail subform to enter multiple counties. I was having trouble
because the databse would not let me enter data into the subform until
the main form data was written to the table - gave me a key
relationship error. I have added a command buttom to the main form to
refresh the data and make the county detail subform visible for data
entry. However, now I am having trouble figuring out how to pull
records from the query (code above) but write back to the county detail
table so that the appropriate relationship exists.
Is there a way to pull from a query and then write to another table? Am
I going about this the correct way or is there a better way to do
this????
Thanks
have an awesome day