"Duplicate" comboboxes on one form

I have a form that I am trying to use data from the same table to populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am trying to
make them operate independent of each other. I have created two queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change as
well. If I don't leave them unbound and use the afterUpdate Event to assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine which
is the best way to approach this.

Thank you, Robin

If I understand it correctly you are using the same field in the table as
the source for both combos.

What you need to do is bind the two combos to fields in a different table to
the one you are using as the source.

"Robin" wrote:

I have a form that I am trying to use data from the same table to populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am trying to
make them operate independent of each other. I have created two queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change as
well. If I don't leave them unbound and use the afterUpdate Event to assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine which
is the best way to approach this.

Thank you, Robin

Both combo boxes get their Row Source from a separate Clients table? OK.
Are the two combo boxes bound to different fields in the table upon which
your form is based? That is, there needs to be separate fields for Current
Client and Referring Client. OK so far? If so, where are you entering the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't leave
them unbound." What is the difference between binding them and not leaving
them unbound? Is that a typo? Please be clear in what you are saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am trying
to
make them operate independent of each other. I have created two queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change as
well. If I don't leave them unbound and use the afterUpdate Event to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine which
is the best way to approach this.

Thank you, Robin

I apologize for the lack of clarity, it was late and even now all the details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table information
to say: this client (with the address info) gave the referral on another
section of the same form.

This information was on the form originally but only the client information
was using a combo box, the referral information was entered manually. It
turns out we are duplicating our efforts as most referrals come from past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on the
same form using comboxes so we won't have to maintain two separate client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same client
info table,tblClient1. I created and unbound combobox,Combo2, recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name, but
cannot seem to make the address info that I would expect from the query to
follow. In some instances a change in Combo1 affects the address in both
ClientAddress and RefAddress. And other times a change in Combo2 will only
change the client name and not the Client Address. Then I get errors about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property in
the AfterUpdate event to assign the values to address,city,state,etc. fields
programatically to fill the fields on my form but now seem to lack a way to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table? OK.
Are the two combo boxes bound to different fields in the table upon which
your form is based? That is, there needs to be separate fields for Current
Client and Referring Client. OK so far? If so, where are you entering the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't leave
them unbound." What is the difference between binding them and not leaving
them unbound? Is that a typo? Please be clear in what you are saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am trying
to
make them operate independent of each other. I have created two queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change as
well. If I don't leave them unbound and use the afterUpdate Event to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine which
is the best way to approach this.

Thank you, Robin

It would help if you could describe the purpose of the form onto which you
wish to enter the Client information. Clients are entities, so they should
have their own table (one table only). Similarly, the data on the form in
question seems to be an entity, so it too should have its own table. For
instance, if you are trying to enter information about quotes, you would
have a quote table separate from the Client table. The Quote record has
information about the client for whom the quote is being made and the client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts would be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered manually. It
turns out we are duplicating our efforts as most referrals come from past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on
the
same form using comboxes so we won't have to maintain two separate client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name, but
cannot seem to make the address info that I would expect from the query to
follow. In some instances a change in Combo1 affects the address in both
ClientAddress and RefAddress. And other times a change in Combo2 will
only
change the client name and not the Client Address. Then I get errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property in
the AfterUpdate event to assign the values to address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a way
to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table? OK.
Are the two combo boxes bound to different fields in the table upon which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you entering
the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change
as
well. If I don't leave them unbound and use the afterUpdate Event to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine
which
is the best way to approach this.

Thank you, Robin

The purpose of the form is to create an Assignment Sheet for one of our
employees. There are several tables which contain data separated as you
suggest. I have Company Info, Client Info, InvoiceInfo, PaymentsInfo,
TransactionDetails, etc. all in separate tables and use queries to provide a
source for the forms.

On this particular form there is data from many of the tables listed in a
query.
This query has the following original fields all on the form.

Form Start:
control, control, etc, etc, etc

[Referingclientname] [Referingclientaddress] [Referingclientstate], etc.

control, control, etc, etc, etc.

combobox selecting by [ClientID] providing [ClientName] [ClientAddress]
[ClientState], etc. accomplished through form requery.

Form End

Now, I'd like to have the [ReferingClientName] [Referingclientaddress], etc
to receive the information from a second combo box. Rather than manual input
as was the case on the orignal form.

All of the above control fields are present to receive information in the
forms underlying query.






"BruceM" wrote:

It would help if you could describe the purpose of the form onto which you
wish to enter the Client information. Clients are entities, so they should
have their own table (one table only). Similarly, the data on the form in
question seems to be an entity, so it too should have its own table. For
instance, if you are trying to enter information about quotes, you would
have a quote table separate from the Client table. The Quote record has
information about the client for whom the quote is being made and the client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts would be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered manually. It
turns out we are duplicating our efforts as most referrals come from past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on
the
same form using comboxes so we won't have to maintain two separate client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name, but
cannot seem to make the address info that I would expect from the query to
follow. In some instances a change in Combo1 affects the address in both
ClientAddress and RefAddress. And other times a change in Combo2 will
only
change the client name and not the Client Address. Then I get errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property in
the AfterUpdate event to assign the values to address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a way
to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table? OK.
Are the two combo boxes bound to different fields in the table upon which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you entering
the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change
as
well. If I don't leave them unbound and use the afterUpdate Event to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine
which
is the best way to approach this.

Thank you, Robin

One more note: I will never have use to know which client refered the other,
requiring two separate tables. I just want to use the existing client table
to populate the refering client fields.

Thanks, Robin

"Robin" wrote:

The purpose of the form is to create an Assignment Sheet for one of our
employees. There are several tables which contain data separated as you
suggest. I have Company Info, Client Info, InvoiceInfo, PaymentsInfo,
TransactionDetails, etc. all in separate tables and use queries to provide a
source for the forms.

On this particular form there is data from many of the tables listed in a
query.
This query has the following original fields all on the form.

Form Start:
control, control, etc, etc, etc

[Referingclientname] [Referingclientaddress] [Referingclientstate], etc.

control, control, etc, etc, etc.

combobox selecting by [ClientID] providing [ClientName] [ClientAddress]
[ClientState], etc. accomplished through form requery.

Form End

Now, I'd like to have the [ReferingClientName] [Referingclientaddress], etc
to receive the information from a second combo box. Rather than manual input
as was the case on the orignal form.

All of the above control fields are present to receive information in the
forms underlying query.






"BruceM" wrote:

It would help if you could describe the purpose of the form onto which you
wish to enter the Client information. Clients are entities, so they should
have their own table (one table only). Similarly, the data on the form in
question seems to be an entity, so it too should have its own table. For
instance, if you are trying to enter information about quotes, you would
have a quote table separate from the Client table. The Quote record has
information about the client for whom the quote is being made and the client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts would be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered manually. It
turns out we are duplicating our efforts as most referrals come from past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on
the
same form using comboxes so we won't have to maintain two separate client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name, but
cannot seem to make the address info that I would expect from the query to
follow. In some instances a change in Combo1 affects the address in both
ClientAddress and RefAddress. And other times a change in Combo2 will
only
change the client name and not the Client Address. Then I get errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property in
the AfterUpdate event to assign the values to address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a way
to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table? OK.
Are the two combo boxes bound to different fields in the table upon which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you entering
the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields change
as
well. If I don't leave them unbound and use the afterUpdate Event to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine
which
is the best way to approach this.

Thank you, Robin

If you have a table for ClientInfo (I will call it tblClientInfo) that is a
good thing. However, if your AssignmentSheet query includes tblClientInfo,
and if you are selecting two clients, you could have some problems getting
the query to know what other client information (address, etc.) to use.
A better solution would be to use subforms for the Client information. I
will use the name ClientID for the ID field in tblClientInfo. In
tblAssignment (or wherever the Assignment information is stored) I will use
the names ClientID_Main and ClientID_Ref for the main and referring client
fields. Make a query that has the ClientId and the ClientName, and use this
query as the Row Source for the combo boxes. The combo box wizard can help
you set this up. Use the option to look up the values. Hide the ID field.
Make a query based on tblClientInfo that includes ClientID, along with
ClientName, ClientAddress, ClientState, and other fields you want to
display. Make a form (frmClientDetails) that includes text boxes for
ClientAddress, ClientState, etc. Use this query as its record source.
Use the toolbox to add a subform control to the main form. Select
tblClientDetails as its Source Object; select ClientID_Main as the Link
Parent Field, and ClientID as the Link Child Field. If you click next to
Link Parent Field on the Property Sheet Access will probably set this up for
you.
Repeat with another subform for ClientID_Ref.
All you are storing on the main form is ClientID_Main and ClientID_Ref. The
subforms link to those fields, so should display the correct Client address,
etc. information.

Another approach is to include the fields you want in the combo box Row
Source. Store the ClientID in the ClientID_Main and ClientID_Ref fields, as
described. Suppose the Row Source has the following fields:
ClientID, ClientName, ClientAddress
The combo box (I will call it cboClient_Main) Bound Column is 1 (set this in
the property sheet). The Column Count is 4, and the Column Widths are 0";
1.5"; 0"; 0". Add an unbound text box to the main form (no subforms with
this approach). Set its Control Source to:
=cboClient_Main.Column(2)
This should display ClientAddress when a client has been selected from
cboClient_Main. The number 2 is because the columns are counted starting
from 0 with this method. It is really the third column. Repeat for
Column(3) in another text box. Do the same for the cboClient_Ref combo box.

"Robin" wrote in message
...
The purpose of the form is to create an Assignment Sheet for one of our
employees. There are several tables which contain data separated as you
suggest. I have Company Info, Client Info, InvoiceInfo, PaymentsInfo,
TransactionDetails, etc. all in separate tables and use queries to provide
a
source for the forms.

On this particular form there is data from many of the tables listed in a
query.
This query has the following original fields all on the form.

Form Start:
control, control, etc, etc, etc

[Referingclientname] [Referingclientaddress] [Referingclientstate],
etc.

control, control, etc, etc, etc.

combobox selecting by [ClientID] providing [ClientName] [ClientAddress]
[ClientState], etc. accomplished through form requery.

Form End

Now, I'd like to have the [ReferingClientName] [Referingclientaddress],
etc
to receive the information from a second combo box. Rather than manual
input
as was the case on the orignal form.

All of the above control fields are present to receive information in the
forms underlying query.






"BruceM" wrote:

It would help if you could describe the purpose of the form onto which
you
wish to enter the Client information. Clients are entities, so they
should
have their own table (one table only). Similarly, the data on the form
in
question seems to be an entity, so it too should have its own table. For
instance, if you are trying to enter information about quotes, you would
have a quote table separate from the Client table. The Quote record has
information about the client for whom the quote is being made and the
client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts would
be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on
another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered manually.
It
turns out we are duplicating our efforts as most referrals come from
past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on
the
same form using comboxes so we won't have to maintain two separate
client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name,
but
cannot seem to make the address info that I would expect from the query
to
follow. In some instances a change in Combo1 affects the address in
both
ClientAddress and RefAddress. And other times a change in Combo2 will
only
change the client name and not the Client Address. Then I get errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property
in
the AfterUpdate event to assign the values to address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a
way
to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table?
OK.
Are the two combo boxes bound to different fields in the table upon
which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you
entering
the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are
saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields
change
as
well. If I don't leave them unbound and use the afterUpdate Event
to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine
which
is the best way to approach this.

Thank you, Robin

[BruceM] = "Genius"
Thanks for your help, my Ibuprofen supply is safe again.
Robin

"BruceM" wrote:

If you have a table for ClientInfo (I will call it tblClientInfo) that is a
good thing. However, if your AssignmentSheet query includes tblClientInfo,
and if you are selecting two clients, you could have some problems getting
the query to know what other client information (address, etc.) to use.
A better solution would be to use subforms for the Client information. I
will use the name ClientID for the ID field in tblClientInfo. In
tblAssignment (or wherever the Assignment information is stored) I will use
the names ClientID_Main and ClientID_Ref for the main and referring client
fields. Make a query that has the ClientId and the ClientName, and use this
query as the Row Source for the combo boxes. The combo box wizard can help
you set this up. Use the option to look up the values. Hide the ID field.
Make a query based on tblClientInfo that includes ClientID, along with
ClientName, ClientAddress, ClientState, and other fields you want to
display. Make a form (frmClientDetails) that includes text boxes for
ClientAddress, ClientState, etc. Use this query as its record source.
Use the toolbox to add a subform control to the main form. Select
tblClientDetails as its Source Object; select ClientID_Main as the Link
Parent Field, and ClientID as the Link Child Field. If you click next to
Link Parent Field on the Property Sheet Access will probably set this up for
you.
Repeat with another subform for ClientID_Ref.
All you are storing on the main form is ClientID_Main and ClientID_Ref. The
subforms link to those fields, so should display the correct Client address,
etc. information.

Another approach is to include the fields you want in the combo box Row
Source. Store the ClientID in the ClientID_Main and ClientID_Ref fields, as
described. Suppose the Row Source has the following fields:
ClientID, ClientName, ClientAddress
The combo box (I will call it cboClient_Main) Bound Column is 1 (set this in
the property sheet). The Column Count is 4, and the Column Widths are 0";
1.5"; 0"; 0". Add an unbound text box to the main form (no subforms with
this approach). Set its Control Source to:
=cboClient_Main.Column(2)
This should display ClientAddress when a client has been selected from
cboClient_Main. The number 2 is because the columns are counted starting
from 0 with this method. It is really the third column. Repeat for
Column(3) in another text box. Do the same for the cboClient_Ref combo box.

"Robin" wrote in message
...
The purpose of the form is to create an Assignment Sheet for one of our
employees. There are several tables which contain data separated as you
suggest. I have Company Info, Client Info, InvoiceInfo, PaymentsInfo,
TransactionDetails, etc. all in separate tables and use queries to provide
a
source for the forms.

On this particular form there is data from many of the tables listed in a
query.
This query has the following original fields all on the form.

Form Start:
control, control, etc, etc, etc

[Referingclientname] [Referingclientaddress] [Referingclientstate],
etc.

control, control, etc, etc, etc.

combobox selecting by [ClientID] providing [ClientName] [ClientAddress]
[ClientState], etc. accomplished through form requery.

Form End

Now, I'd like to have the [ReferingClientName] [Referingclientaddress],
etc
to receive the information from a second combo box. Rather than manual
input
as was the case on the orignal form.

All of the above control fields are present to receive information in the
forms underlying query.






"BruceM" wrote:

It would help if you could describe the purpose of the form onto which
you
wish to enter the Client information. Clients are entities, so they
should
have their own table (one table only). Similarly, the data on the form
in
question seems to be an entity, so it too should have its own table. For
instance, if you are trying to enter information about quotes, you would
have a quote table separate from the Client table. The Quote record has
information about the client for whom the quote is being made and the
client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts would
be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use this
information to populate comboboxes to say: this is the client (with the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on
another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered manually.
It
turns out we are duplicating our efforts as most referrals come from
past
clients. So as I try to create the second combobox to use the existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice on
the
same form using comboxes so we won't have to maintain two separate
client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client name,
but
cannot seem to make the address info that I would expect from the query
to
follow. In some instances a change in Combo1 affects the address in
both
ClientAddress and RefAddress. And other times a change in Combo2 will
only
change the client name and not the Client Address. Then I get errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n) property
in
the AfterUpdate event to assign the values to address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a
way
to
make changes to the tblClient1 if the addresses and such need changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients table?
OK.
Are the two combo boxes bound to different fields in the table upon
which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you
entering
the
date? Or did you mean "data"? In any case, what other fields change?
On the one hand you say "If I bind them...". Then you say "If I don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are
saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields. These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields. These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields
change
as
well. If I don't leave them unbound and use the afterUpdate Event
to
assign
the values I receive error messages about cancel and undo warnings.

I know I'm missing something at the design state but can't determine
which
is the best way to approach this.

Thank you, Robin

I'm curious as to which option you used. In any case, I am just passing
along what I have learned here. Glad to be of help, as others have helped
me.


"Robin" wrote in message
...
[BruceM] = "Genius"
Thanks for your help, my Ibuprofen supply is safe again.
Robin

"BruceM" wrote:

If you have a table for ClientInfo (I will call it tblClientInfo) that is
a
good thing. However, if your AssignmentSheet query includes
tblClientInfo,
and if you are selecting two clients, you could have some problems
getting
the query to know what other client information (address, etc.) to use.
A better solution would be to use subforms for the Client information. I
will use the name ClientID for the ID field in tblClientInfo. In
tblAssignment (or wherever the Assignment information is stored) I will
use
the names ClientID_Main and ClientID_Ref for the main and referring
client
fields. Make a query that has the ClientId and the ClientName, and use
this
query as the Row Source for the combo boxes. The combo box wizard can
help
you set this up. Use the option to look up the values. Hide the ID
field.
Make a query based on tblClientInfo that includes ClientID, along with
ClientName, ClientAddress, ClientState, and other fields you want to
display. Make a form (frmClientDetails) that includes text boxes for
ClientAddress, ClientState, etc. Use this query as its record source.
Use the toolbox to add a subform control to the main form. Select
tblClientDetails as its Source Object; select ClientID_Main as the Link
Parent Field, and ClientID as the Link Child Field. If you click next to
Link Parent Field on the Property Sheet Access will probably set this up
for
you.
Repeat with another subform for ClientID_Ref.
All you are storing on the main form is ClientID_Main and ClientID_Ref.
The
subforms link to those fields, so should display the correct Client
address,
etc. information.

Another approach is to include the fields you want in the combo box Row
Source. Store the ClientID in the ClientID_Main and ClientID_Ref fields,
as
described. Suppose the Row Source has the following fields:
ClientID, ClientName, ClientAddress
The combo box (I will call it cboClient_Main) Bound Column is 1 (set this
in
the property sheet). The Column Count is 4, and the Column Widths are
0";
1.5"; 0"; 0". Add an unbound text box to the main form (no subforms with
this approach). Set its Control Source to:
=cboClient_Main.Column(2)
This should display ClientAddress when a client has been selected from
cboClient_Main. The number 2 is because the columns are counted starting
from 0 with this method. It is really the third column. Repeat for
Column(3) in another text box. Do the same for the cboClient_Ref combo
box.

"Robin" wrote in message
...
The purpose of the form is to create an Assignment Sheet for one of our
employees. There are several tables which contain data separated as
you
suggest. I have Company Info, Client Info, InvoiceInfo, PaymentsInfo,
TransactionDetails, etc. all in separate tables and use queries to
provide
a
source for the forms.

On this particular form there is data from many of the tables listed in
a
query.
This query has the following original fields all on the form.

Form Start:
control, control, etc, etc, etc

[Referingclientname] [Referingclientaddress] [Referingclientstate],
etc.

control, control, etc, etc, etc.

combobox selecting by [ClientID] providing [ClientName]
[ClientAddress]
[ClientState], etc. accomplished through form requery.

Form End

Now, I'd like to have the [ReferingClientName] [Referingclientaddress],
etc
to receive the information from a second combo box. Rather than manual
input
as was the case on the orignal form.

All of the above control fields are present to receive information in
the
forms underlying query.






"BruceM" wrote:

It would help if you could describe the purpose of the form onto which
you
wish to enter the Client information. Clients are entities, so they
should
have their own table (one table only). Similarly, the data on the
form
in
question seems to be an entity, so it too should have its own table.
For
instance, if you are trying to enter information about quotes, you
would
have a quote table separate from the Client table. The Quote record
has
information about the client for whom the quote is being made and the
client
who referred them. Two entities (Clients and Quotes), so two tables.
A quick explanation of the real-world situation behind your efforts
would
be
of great value, I think.

"Robin" wrote in message
...
I apologize for the lack of clarity, it was late and even now all the
details
are dancing in my head.

What I have is Client information in a table. And I want to use
this
information to populate comboboxes to say: this is the client (with
the
address info) on one section of the form. And use the same table
information
to say: this client (with the address info) gave the referral on
another
section of the same form.

This information was on the form originally but only the client
information
was using a combo box, the referral information was entered
manually.
It
turns out we are duplicating our efforts as most referrals come from
past
clients. So as I try to create the second combobox to use the
existing
client list for this purpose my troubles begin.

I have tried so many different things it is not practical to list
them.
But this is where I'm at right now:

I have 1 table with client info, tblClient1. I want to use it twice
on
the
same form using comboxes so we won't have to maintain two separate
client
tables. I started with one combobox, Combo1 and stored the client
information in the forms underlying query, qryForm, all is well.

I needed to add a combobox, Combo2, to list client info from the
same
client
info table,tblClient1. I created and unbound combobox,Combo2,
recordsource
from a new query, qryReferralClient based on tblClient1.

I can get the comboboxes to keep track of their separate client
name,
but
cannot seem to make the address info that I would expect from the
query
to
follow. In some instances a change in Combo1 affects the address in
both
ClientAddress and RefAddress. And other times a change in Combo2
will
only
change the client name and not the Client Address. Then I get
errors
about
undo and cancel event, etc.

The closest I have come is to use the RecordsetClone.column(n)
property
in
the AfterUpdate event to assign the values to
address,city,state,etc.
fields
programatically to fill the fields on my form but now seem to lack a
way
to
make changes to the tblClient1 if the addresses and such need
changing.

I hope this clears things up a little.

Thank you very much,

Robin

"BruceM" wrote:

Both combo boxes get their Row Source from a separate Clients
table?
OK.
Are the two combo boxes bound to different fields in the table upon
which
your form is based? That is, there needs to be separate fields for
Current
Client and Referring Client. OK so far? If so, where are you
entering
the
date? Or did you mean "data"? In any case, what other fields
change?
On the one hand you say "If I bind them...". Then you say "If I
don't
leave
them unbound." What is the difference between binding them and not
leaving
them unbound? Is that a typo? Please be clear in what you are
saying.

"Robin" wrote in message
...
I have a form that I am trying to use data from the same table to
populate
two comboboxes. Access 2007

ComboOne looks up Client Name and supporting address fields.
These
fields
are used for Current Client uses.

ComboTwo looks up Client Name and supporting address fields.
These
fields
are used for Referring Client uses.

The source for both combo boxes is the from the same table. I am
trying
to
make them operate independent of each other. I have created two
queries,
one for each of the combo boxes.

If I Bind them, when date is entered in to one the other fields
change
as
well. If I don't leave them unbound and use the afterUpdate
Event
to
assign
the values I receive error messages about cancel and undo
warnings.

I know I'm missing something at the design state but can't
determine
which
is the best way to approach this.

Thank you, Robin