multiple table form based on a query

[Deus402]

Here is my table design:

tblEmployers
EmployerID autonum (primary key)
EmployerName text

tblLocations
LocationID autonum (primary key)
EmployerID longint (foreign key)
LocationAdress text
LocationCity text
Location ...
...
...

what i am trying to do is have a form where you can enter a new
location, and when you type in the employer name it will either look up
the EmployerID in the tblEmployers table and insert it into the
tblLocations table, or if it doesn't exist, create a new entry in the
tblEmployers Table and insert the new EmployerID into the tblLocations
table. I am not sure exactly how to do this... right now i have a
query that just includes all fields from both tables, and a form based
on that query. in the tblLocations.EmployerID field i have the control
source set to tblLocations_EmployerID and the row source set to
tblEmployers.EmployerID. this allows me to enter a new employer,
putting the right data in both tables EmployerID, but it will create a
new entry in the tblEmployers table even if the EmployerName already
exists.

Am i on the right track, or is there a better way to do this?

[lesperancer]

create a form based on employers

create a continuous form based on locations

open the employers form and drag the locations form to it... creating a
subform, linked by employerId

now you can add a employer and one-or-more locations
or you can search for any existing employer and add/delete locations

Deus402 wrote:

Here is my table design:

tblEmployers
EmployerID autonum (primary key)
EmployerName text

tblLocations
LocationID autonum (primary key)
EmployerID longint (foreign key)
LocationAdress text
LocationCity text
Location ...
...
...

what i am trying to do is have a form where you can enter a new
location, and when you type in the employer name it will either look up
the EmployerID in the tblEmployers table and insert it into the
tblLocations table, or if it doesn't exist, create a new entry in the
tblEmployers Table and insert the new EmployerID into the tblLocations
table. I am not sure exactly how to do this... right now i have a
query that just includes all fields from both tables, and a form based
on that query. in the tblLocations.EmployerID field i have the control
source set to tblLocations_EmployerID and the row source set to
tblEmployers.EmployerID. this allows me to enter a new employer,
putting the right data in both tables EmployerID, but it will create a
new entry in the tblEmployers table even if the EmployerName already
exists.

Am i on the right track, or is there a better way to do this?

[Deus402]

thats not exactly what i am looking for...

what i am looking for is the ability to enter all the data for one
location and employer in one shot, when a new employer is added, but
prevent the user from entering employer as new if it already exists.
even with your subform design, you can still enter a employer name that
already exists and it creates a new key for it. should i just chnge my
table so the primary key is the employer name? it seems kind of silly
to have a table with only one column. maybe i should just have the
employer name in the locations table and not worry about normalization?
the ultimate goal here is to have a link on the main switchboard for
new data entry, because most employers will only have one location when
they are newly entered. i have a separate form for adding a location to
an existing employer... which is almost working like i want it to, but
thats another story.

i appreciate the help, i took an access class at the local university,
but it was so basic that it didn't really help...

le*********@natpro.com wrote:

create a form based on employers

create a continuous form based on locations

open the employers form and drag the locations form to it... creating a
subform, linked by employerId

now you can add a employer and one-or-more locations
or you can search for any existing employer and add/delete locations

Deus402 wrote:

Here is my table design:

tblEmployers
EmployerID autonum (primary key)
EmployerName text

tblLocations
LocationID autonum (primary key)
EmployerID longint (foreign key)
LocationAdress text
LocationCity text
Location ...
...
...

what i am trying to do is have a form where you can enter a new
location, and when you type in the employer name it will either look up
the EmployerID in the tblEmployers table and insert it into the
tblLocations table, or if it doesn't exist, create a new entry in the
tblEmployers Table and insert the new EmployerID into the tblLocations
table. I am not sure exactly how to do this... right now i have a
query that just includes all fields from both tables, and a form based
on that query. in the tblLocations.EmployerID field i have the control
source set to tblLocations_EmployerID and the row source set to
tblEmployers.EmployerID. this allows me to enter a new employer,
putting the right data in both tables EmployerID, but it will create a
new entry in the tblEmployers table even if the EmployerName already
exists.

Am i on the right track, or is there a better way to do this?

[Deus402]

Nevermind, i went with a different design.
Deus402 wrote:

thats not exactly what i am looking for...

what i am looking for is the ability to enter all the data for one
location and employer in one shot, when a new employer is added, but
prevent the user from entering employer as new if it already exists.
even with your subform design, you can still enter a employer name that
already exists and it creates a new key for it. should i just chnge my
table so the primary key is the employer name? it seems kind of silly
to have a table with only one column. maybe i should just have the
employer name in the locations table and not worry about normalization?
the ultimate goal here is to have a link on the main switchboard for
new data entry, because most employers will only have one location when
they are newly entered. i have a separate form for adding a location to
an existing employer... which is almost working like i want it to, but
thats another story.

i appreciate the help, i took an access class at the local university,
but it was so basic that it didn't really help...

le*********@natpro.com wrote:

create a form based on employers

create a continuous form based on locations

open the employers form and drag the locations form to it... creating a
subform, linked by employerId

now you can add a employer and one-or-more locations
or you can search for any existing employer and add/delete locations

Deus402 wrote:

Here is my table design:
>
tblEmployers
EmployerID autonum (primary key)
EmployerName text
>
tblLocations
LocationID autonum (primary key)
EmployerID longint (foreign key)
LocationAdress text
LocationCity text
Location ...
...
...
>
what i am trying to do is have a form where you can enter a new
location, and when you type in the employer name it will either look up
the EmployerID in the tblEmployers table and insert it into the
tblLocations table, or if it doesn't exist, create a new entry in the
tblEmployers Table and insert the new EmployerID into the tblLocations
table. I am not sure exactly how to do this... right now i have a
query that just includes all fields from both tables, and a form based
on that query. in the tblLocations.EmployerID field i have the control
source set to tblLocations_EmployerID and the row source set to
tblEmployers.EmployerID. this allows me to enter a new employer,
putting the right data in both tables EmployerID, but it will create a
new entry in the tblEmployers table even if the EmployerName already
exists.
>
Am i on the right track, or is there a better way to do this?

[Kc-Mass]

Just make it an index - no duplicates

Kevin C
"Deus402" <dp****@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

thats not exactly what i am looking for...

what i am looking for is the ability to enter all the data for one
location and employer in one shot, when a new employer is added, but
prevent the user from entering employer as new if it already exists.
even with your subform design, you can still enter a employer name that
already exists and it creates a new key for it. should i just chnge my
table so the primary key is the employer name? it seems kind of silly
to have a table with only one column. maybe i should just have the
employer name in the locations table and not worry about normalization?
the ultimate goal here is to have a link on the main switchboard for
new data entry, because most employers will only have one location when
they are newly entered. i have a separate form for adding a location to
an existing employer... which is almost working like i want it to, but
thats another story.

i appreciate the help, i took an access class at the local university,
but it was so basic that it didn't really help...

le*********@natpro.com wrote:

>create a form based on employers

create a continuous form based on locations

open the employers form and drag the locations form to it... creating a
subform, linked by employerId

now you can add a employer and one-or-more locations
or you can search for any existing employer and add/delete locations

Deus402 wrote:

Here is my table design:

tblEmployers
EmployerID autonum (primary key)
EmployerName text

tblLocations
LocationID autonum (primary key)
EmployerID longint (foreign key)
LocationAdress text
LocationCity text
Location ...
...
...

what i am trying to do is have a form where you can enter a new
location, and when you type in the employer name it will either look up
the EmployerID in the tblEmployers table and insert it into the
tblLocations table, or if it doesn't exist, create a new entry in the
tblEmployers Table and insert the new EmployerID into the tblLocations
table. I am not sure exactly how to do this... right now i have a
query that just includes all fields from both tables, and a form based
on that query. in the tblLocations.EmployerID field i have the control
source set to tblLocations_EmployerID and the row source set to
tblEmployers.EmployerID. this allows me to enter a new employer,
putting the right data in both tables EmployerID, but it will create a
new entry in the tblEmployers table even if the EmployerName already
exists.

Am i on the right track, or is there a better way to do this?