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 said:
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 said:
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.
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
:
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.
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