Combo Box Query

[Guest]

I hope this is explained well.

I have created a Customer Contact database. I have 3 specific tables that I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields from the
table "tblCustomerInformation". I added 2 subforms "frmLocations" and
"frmAuthorizedContacts". When I select a customer, their specific information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers offices.
On the Authorized Contacts form I have a drop down "PrimaryLocation". The
values of the drop down should only list the locations from the locations
table for that specific customer. Instead it displays all locations. How can
I fix this?

Any help would be appreciated.

Doug

 

[tina]

your tblLocations should have a foreign key field that holds the primary key
value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the locations
based on the customer ID of the current record on frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth

 

[Guest]

Thanks for the response. I followed your suggestion however, I still get the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

 

[tina]

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of them - so
let's just wait and see!)

hth

Thanks for the response. I followed your suggestion however, I still get the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

your tblLocations should have a foreign key field that holds the primary key
value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the locations
based on the customer ID of the current record on frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


that
I How
can

 

[Guest]

Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of them - so
let's just wait and see!)

hth

Thanks for the response. I followed your suggestion however, I still get the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

your tblLocations should have a foreign key field that holds the primary key
value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the locations
based on the customer ID of the current record on frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific tables that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields from the
table "tblCustomerInformation". I added 2 subforms "frmLocations" and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers
offices.
On the Authorized Contacts form I have a drop down "PrimaryLocation". The
values of the drop down should only list the locations from the locations
table for that specific customer. Instead it displays all locations. How
can
I fix this?

Any help would be appreciated.

Doug

 

[tina]

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the combo box's
Enter event, as i suggested?

hth

Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of them - so
let's just wait and see!)

hth

Thanks for the response. I followed your suggestion however, I still

get
the

entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the

primary
key

value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the locations
based on the customer ID of the current record on frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific

tables
that

I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields

from
the

table "tblCustomerInformation". I added 2 subforms "frmLocations" and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers
offices.
On the Authorized Contacts form I have a drop down

"PrimaryLocation".
The

values of the drop down should only list the locations from the locations
table for that specific customer. Instead it displays all

locations.
How

can
I fix this?

Any help would be appreciated.

Doug

 

[Guest]

Thanks Tina. I forgot to apply the Requery action on the combo box. That
worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I click on an
Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the combo box's
Enter event, as i suggested?

hth

Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of them - so
let's just wait and see!)

hth


message Thanks for the response. I followed your suggestion however, I still get
the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the primary
key
value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the
locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields from
the
table "tblCustomerInformation". I added 2 subforms "frmLocations" and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers
offices.
On the Authorized Contacts form I have a drop down "PrimaryLocation".
The
values of the drop down should only list the locations from the
locations
table for that specific customer. Instead it displays all locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[tina]

When I click on an Authorized Contact or Location

well, i don't know what you mean by the above. what specifically are you
doing? and where specifically? and did you apply the Requery action to the
combo box's Enter event, rather than to an event of the subform object or
the subform control?

hth

Thanks Tina. I forgot to apply the Requery action on the combo box. That
worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I click on an
Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the combo box's
Enter event, as i suggested?

hth

Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now

matter
what

customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

:

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event,

so
the

list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of

them -
so

let's just wait and see!)

hth


message Thanks for the response. I followed your suggestion however, I

still
get

the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the primary
key
value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the
locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


"Douglas Merrill" <[email protected]>

wrote
in

message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields from
the
table "tblCustomerInformation". I added 2 subforms

"frmLocations"
and

"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers
offices.
On the Authorized Contacts form I have a drop down "PrimaryLocation".
The
values of the drop down should only list the locations from the
locations
table for that specific customer. Instead it displays all locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[Guest]

I hope this better explains:

I named the location combo box on the Authorized Contact form
"PrimaryLocation". The "PrimaryLocation" combo box values are based on the
"LocationNames" from the Location table.

Originally the “PrimaryLocation†combo box would display all location names
records instead of the locations specific for the current customer record.

I applied the WHERE statement (from your previous post) to the combo box so
the values listed would pertain to the current customer record. As a result
when I would select a different customer the values in the combo box were for
the first customer and not the current.

Then I applied the Requery action for "On Enter" to the "PrimaryLocation"
combo box on the Authorized Contact Form. Now when I select a customer record
it displays the location names that are specific to that customer.

I have no problem adding a customer location, but when I go to the
Authorized Contact subform to add an authorized contact and select their
"PrimaryLocation" I get the error "You can't use the ApplyFilter action in
this window".

Again thank you for your time and help - it is very much appreciated.

When I click on an Authorized Contact or Location

well, i don't know what you mean by the above. what specifically are you
doing? and where specifically? and did you apply the Requery action to the
combo box's Enter event, rather than to an event of the subform object or
the subform control?

hth

Thanks Tina. I forgot to apply the Requery action on the combo box. That
worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I click on an
Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the combo box's
Enter event, as i suggested?

hth


message Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter
what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

:

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so
the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are
correct,
the above should work. (on the other hand, seems like the SQL i gave
should
have erred out and not returned any records, rather than all of them -
so
let's just wait and see!)

hth


message Thanks for the response. I followed your suggestion however, I still
get
the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the
primary
key
value of the related CustomerInformation record. i'll call that
field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the
locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the
CustomerInformation
table is a Number or Autonumber data type.)

hth


in
message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific
tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields
from
the
table "tblCustomerInformation". I added 2 subforms "frmLocations"
and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is
displayed.

The locations sub-form is used to add each location of my
customers
offices.
On the Authorized Contacts form I have a drop down
"PrimaryLocation".
The
values of the drop down should only list the locations from the
locations
table for that specific customer. Instead it displays all
locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[tina]

well, sounds like one of those things that make you tear your hair out
finding the problem, which then turns out to be a simple problem/fix.

i'm stumped. if your db is Access97 or newer, i'll take a quick look at your
setup if you want to send me a copy of the db. if so, make a COPY of your db
and do the following to the COPY:

1. delete any proprietary data (enter some dummy data instead, so i have
something to test against).
2. compact the db.
3. if necessary, zip to under 1 MB. i have WinZip at this end to unzip it.
4. post your email address. make sure you disguise it to avoid the spammers,
something like
myemailaddressDELETECAPITALLETTERS at somewheredotcom

i'll email you and you can send the db copy to me. if you do it today, i
might be able to find and fix the problem and mail it back to you this
evening so
you can look at my solution.

hth

I hope this better explains:

I named the location combo box on the Authorized Contact form
"PrimaryLocation". The "PrimaryLocation" combo box values are based on the
"LocationNames" from the Location table.

Originally the "PrimaryLocation" combo box would display all location names
records instead of the locations specific for the current customer record.

I applied the WHERE statement (from your previous post) to the combo box so
the values listed would pertain to the current customer record. As a result
when I would select a different customer the values in the combo box were for
the first customer and not the current.

Then I applied the Requery action for "On Enter" to the "PrimaryLocation"
combo box on the Authorized Contact Form. Now when I select a customer record
it displays the location names that are specific to that customer.

I have no problem adding a customer location, but when I go to the
Authorized Contact subform to add an authorized contact and select their
"PrimaryLocation" I get the error "You can't use the ApplyFilter action in
this window".

Again thank you for your time and help - it is very much appreciated.

When I click on an Authorized Contact or Location

well, i don't know what you mean by the above. what specifically are you
doing? and where specifically? and did you apply the Requery action to the
combo box's Enter event, rather than to an event of the subform object or
the subform control?

hth

Thanks Tina. I forgot to apply the Requery action on the combo box. That
worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I click

on
an

Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

:

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the

combo
box's

Enter event, as i suggested?

hth


message Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter
what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know

if
this

has any effect. I apologize for not mentioning and if this is a problem.

:

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter

event,
so

the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are
correct,
the above should work. (on the other hand, seems like the SQL i gave
should
have erred out and not returned any records, rather than all of them -
so
let's just wait and see!)

hth


"Douglas Merrill" <[email protected]>

wrote
in

message Thanks for the response. I followed your suggestion however, I still
get
the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID

&
";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the
primary
key
value of the related CustomerInformation record. i'll call that
field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the
locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the
CustomerInformation
table is a Number or Autonumber data type.)

hth



I hope this is explained well.

I have created a Customer Contact database. I have 3 specific
tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields
from
the
table "tblCustomerInformation". I added 2 subforms "frmLocations"
and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is
displayed.

The locations sub-form is used to add each location of my
customers
offices.
On the Authorized Contacts form I have a drop down
"PrimaryLocation".
The
values of the drop down should only list the locations

from
the

locations
table for that specific customer. Instead it displays all
locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[Guest]

douglas.merrill DELETECAPITALLETTERS at workplaceintegration dotcom

well, sounds like one of those things that make you tear your hair out
finding the problem, which then turns out to be a simple problem/fix.

i'm stumped. if your db is Access97 or newer, i'll take a quick look at your
setup if you want to send me a copy of the db. if so, make a COPY of your db
and do the following to the COPY:

1. delete any proprietary data (enter some dummy data instead, so i have
something to test against).
2. compact the db.
3. if necessary, zip to under 1 MB. i have WinZip at this end to unzip it.
4. post your email address. make sure you disguise it to avoid the spammers,
something like
myemailaddressDELETECAPITALLETTERS at somewheredotcom

i'll email you and you can send the db copy to me. if you do it today, i
might be able to find and fix the problem and mail it back to you this
evening so
you can look at my solution.

hth

I hope this better explains:

I named the location combo box on the Authorized Contact form
"PrimaryLocation". The "PrimaryLocation" combo box values are based on the
"LocationNames" from the Location table.

Originally the "PrimaryLocation" combo box would display all location names
records instead of the locations specific for the current customer record.

I applied the WHERE statement (from your previous post) to the combo box so
the values listed would pertain to the current customer record. As a result
when I would select a different customer the values in the combo box were for
the first customer and not the current.

Then I applied the Requery action for "On Enter" to the "PrimaryLocation"
combo box on the Authorized Contact Form. Now when I select a customer record
it displays the location names that are specific to that customer.

I have no problem adding a customer location, but when I go to the
Authorized Contact subform to add an authorized contact and select their
"PrimaryLocation" I get the error "You can't use the ApplyFilter action in
this window".

Again thank you for your time and help - it is very much appreciated.

When I click on an Authorized Contact or Location

well, i don't know what you mean by the above. what specifically are you
doing? and where specifically? and did you apply the Requery action to the
combo box's Enter event, rather than to an event of the subform object or
the subform control?

hth


message Thanks Tina. I forgot to apply the Requery action on the combo box. That
worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I click on
an
Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

:

no, tabs should not make a difference in this situation - but thanks for
mentioning it (when in doubt, it's always better to include info we
don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when the form
opens, but not subsequently. did you set a Requery action on the combo
box's
Enter event, as i suggested?

hth


message Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now
matter
what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if
this
has any effect. I apologize for not mentioning and if this is a
problem.

:

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event,
so
the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are
correct,
the above should work. (on the other hand, seems like the SQL i gave
should
have erred out and not returned any records, rather than all of
them -
so
let's just wait and see!)

hth


in
message Thanks for the response. I followed your suggestion however, I
still
get
the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID &
";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the
primary
key
value of the related CustomerInformation record. i'll call that
field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the
locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the
CustomerInformation
table is a Number or Autonumber data type.)

hth


"Douglas Merrill" <[email protected]>
wrote
in
message
I hope this is explained well.

I have created a Customer Contact database. I have 3 specific
tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields
from
the
table "tblCustomerInformation". I added 2 subforms
"frmLocations"
and
"frmAuthorizedContacts". When I select a customer, their
specific
information
(CustomerInformation, Locations and Authorized Contacts) is
displayed.

The locations sub-form is used to add each location of my
customers
offices.
On the Authorized Contacts form I have a drop down
"PrimaryLocation".
The
values of the drop down should only list the locations from
the
locations
table for that specific customer. Instead it displays all
locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[tina]

email on it's way.

douglas.merrill DELETECAPITALLETTERS at workplaceintegration dotcom

well, sounds like one of those things that make you tear your hair out
finding the problem, which then turns out to be a simple problem/fix.

i'm stumped. if your db is Access97 or newer, i'll take a quick look at your
setup if you want to send me a copy of the db. if so, make a COPY of your db
and do the following to the COPY:

1. delete any proprietary data (enter some dummy data instead, so i have
something to test against).
2. compact the db.
3. if necessary, zip to under 1 MB. i have WinZip at this end to unzip it.
4. post your email address. make sure you disguise it to avoid the spammers,
something like
myemailaddressDELETECAPITALLETTERS at somewheredotcom

i'll email you and you can send the db copy to me. if you do it today, i
might be able to find and fix the problem and mail it back to you this
evening so
you can look at my solution.

hth

I hope this better explains:

I named the location combo box on the Authorized Contact form
"PrimaryLocation". The "PrimaryLocation" combo box values are based on the
"LocationNames" from the Location table.

Originally the "PrimaryLocation" combo box would display all location names
records instead of the locations specific for the current customer record.

I applied the WHERE statement (from your previous post) to the combo

box
so

the values listed would pertain to the current customer record. As a result
when I would select a different customer the values in the combo box

were
for

the first customer and not the current.

Then I applied the Requery action for "On Enter" to the "PrimaryLocation"
combo box on the Authorized Contact Form. Now when I select a customer record
it displays the location names that are specific to that customer.

I have no problem adding a customer location, but when I go to the
Authorized Contact subform to add an authorized contact and select their
"PrimaryLocation" I get the error "You can't use the ApplyFilter action in
this window".

Again thank you for your time and help - it is very much appreciated.


:

When I click on an Authorized Contact or Location

well, i don't know what you mean by the above. what specifically are you
doing? and where specifically? and did you apply the Requery action

to
the

combo box's Enter event, rather than to an event of the subform

object
or

the subform control?

hth


message Thanks Tina. I forgot to apply the Requery action on the combo

box.
That

worked great. The records displayed are now specific for that customer.

I am having a new issue since adding the Requery action. When I

click
on

an
Authorized Contact or Location I get the following error message:

"You can't use the ApplyFilter action on this window"

Any thoughts or ideas?

:

no, tabs should not make a difference in this situation - but

thanks
for

mentioning it (when in doubt, it's always better to include info we
don't
need, than to leave out info we *do* need).

sounds like the combo box on the subform is being filtered when

the
form

opens, but not subsequently. did you set a Requery action on the combo
box's
Enter event, as i suggested?

hth


"Douglas Merrill" <[email protected]>

wrote
in

message Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now
matter
what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't

know
if

this
has any effect. I apologize for not mentioning and if this is a
problem.

:

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM
Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event,
so
the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are
correct,
the above should work. (on the other hand, seems like the

SQL i
gave

should
have erred out and not returned any records, rather than all of
them -
so
let's just wait and see!)

hth

in
message

Thanks for the response. I followed your suggestion however, I
still
get
the
entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID],

[Locations].[lLocationName]
FROM

Locations
WHERE CustomerID = " &

Forms!frmCustomerInformation!CustomerID
&

";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that

holds
the

primary
key
value of the related CustomerInformation record. i'll

call
that

field
fkCustInfoID

in your combo box's RowSource, add a Where clause to

filter
the

locations
based on the customer ID of the current record on
frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the
CustomerInformation
table is a Number or Autonumber data type.)

hth


"Douglas Merrill"

wrote
in
message
I hope this is explained well.

I have created a Customer Contact database. I have 3 specific
tables
that
I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added

the
fields

from
the
table "tblCustomerInformation". I added 2 subforms
"frmLocations"
and
"frmAuthorizedContacts". When I select a customer, their
specific
information
(CustomerInformation, Locations and Authorized

Contacts)
is

displayed.

The locations sub-form is used to add each location of my
customers
offices.
On the Authorized Contacts form I have a drop down
"PrimaryLocation".
The
values of the drop down should only list the locations from
the
locations
table for that specific customer. Instead it displays all
locations.
How
can
I fix this?

Any help would be appreciated.

Doug

 

[eyal]

1111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111111111111111111111111111111111111111111111111111111111
1111111111111111111111

Again thank you for all your help.

Getting Closer. The Locations displayed are for CustomerID=1 now matter what
customer is selected.

I forgot to mention that I am using tabs on my form. I don't know if this
has any effect. I apologize for not mentioning and if this is a problem.

well, i think i'm getting my syntax mixed up. let's try

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = Forms!frmCustomerInformation!CustomerID

and we probably want to requery the combo box on its' Enter event, so the
list will always be correct for the current record.

assuming that all the referenced names in your SQL statement are correct,
the above should work. (on the other hand, seems like the SQL i gave should
have erred out and not returned any records, rather than all of them - so
let's just wait and see!)

hth

Thanks for the response. I followed your suggestion however, I still

get
the

entire locations list. I have included the RowSource below.

The Primary Key on my tblCustomerInformation is "CustomerID".
The Primary Key on my tblLocations is "LocationID"

SELECT [Locations].[LocationID], [Locations].[lLocationName] FROM Locations
WHERE CustomerID = " & Forms!frmCustomerInformation!CustomerID & ";

Again, any help is very much appriciated.

:

your tblLocations should have a foreign key field that holds the

primary
key

value of the related CustomerInformation record. i'll call that field
fkCustInfoID

in your combo box's RowSource, add a Where clause to filter the locations
based on the customer ID of the current record on frmCustomerInformation.
something like

WHERE fkCustInfoID = " & Forms!frmCustomerInformation!CustInfoID

(the above assumes that the primary key field of the CustomerInformation
table is a Number or Autonumber data type.)

hth


message I hope this is explained well.

I have created a Customer Contact database. I have 3 specific

tables
that

I
am focused on.

Tables:
tblCustomerInformation
tblLocations
tblAuthorizedContacts

Sub-Forms:
frmLocations
frmAuthorizedContacts

I created a form (frmCustomerInformation) and added the fields

from
the

table "tblCustomerInformation". I added 2 subforms "frmLocations" and
"frmAuthorizedContacts". When I select a customer, their specific
information
(CustomerInformation, Locations and Authorized Contacts) is displayed.

The locations sub-form is used to add each location of my customers
offices.
On the Authorized Contacts form I have a drop down

"PrimaryLocation".
The

values of the drop down should only list the locations from the locations
table for that specific customer. Instead it displays all

locations.
How

can
I fix this?

Any help would be appreciated.

Doug