combo box populate text boxes

[Guest]

Hello,
I have two tables:
tblClientID
ClientID(pkey)
SSN
LName
FName

tblNom
ClientID(fkey)
NomYear

How do I set up a SSN combo box that displays SSN and FullName (FullName:
[LastName] & ", " & [FirstName])? After selection, LName and FName text
boxes are auto populated? Do I create a query first -> create an unbound
combo box on the form -> then select the query? I can't get all the fields
in my combo box to appear and the other text boxes populated.
I also want to create a tab control on the same form where page 1 contains
tblNom data and tblClient data on page 2.

TIA,
Lynn

 

[Allen Browne]

Set the combo's properties so it has the extra fields you need.
Something like this:
Row Source: SELECT ClientID,
Trim([LastName] & ", " & [FirstName]) As
FullName,
SSN, LName, FName
FROM tblClient
ORDER BY LName, FName;
Column Count 5
Column Widths 0;2;0;0;0

Now set the Control Source of the box where you want to display the SSN to:
=ClientID.Column(2)
and so on for the other columns of the combo.

Note that it is really important not to *store* the SSN, LName, and FName as
well as the ClientID in the other table. I don't think you are making that
mistake, as you didn't show those fields in tblNom.)

 

[Guest]

one more thing... I've created a unique index for tblNom's ClientID and
NomYear.
So if I the combined SSN and NomYear exists, I want to display an errmsg.
Thanks again!

 

[Guest]

Allen,
Thanks for responding so quickly, you were too quick... I added the
following addendum:

I've created a unique index for tblNom's ClientID and NomYear.
So if I the combined SSN and NomYear exists, I want to display an errmsg.
Thanks again!

Set the combo's properties so it has the extra fields you need.
Something like this:
Row Source: SELECT ClientID,
Trim([LastName] & ", " & [FirstName]) As
FullName,
SSN, LName, FName
FROM tblClient
ORDER BY LName, FName;
Column Count 5
Column Widths 0;2;0;0;0

Now set the Control Source of the box where you want to display the SSN to:
=ClientID.Column(2)
and so on for the other columns of the combo.

Note that it is really important not to *store* the SSN, LName, and FName as
well as the ClientID in the other table. I don't think you are making that
mistake, as you didn't show those fields in tblNom.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello,
I have two tables:
tblClientID
ClientID(pkey)
SSN
LName
FName

tblNom
ClientID(fkey)
NomYear

How do I set up a SSN combo box that displays SSN and FullName (FullName:
[LastName] & ", " & [FirstName])? After selection, LName and FName text
boxes are auto populated? Do I create a query first -> create an unbound
combo box on the form -> then select the query? I can't get all the
fields
in my combo box to appear and the other text boxes populated.
I also want to create a tab control on the same form where page 1 contains
tblNom data and tblClient data on page 2.

TIA,
Lynn

 

[Allen Browne]

The fact that you added a unique index makes no difference to the way you
display the contents of the combo's columns in other text boxes.

If you added the unique index on the combination of fields in table design,
then Access will give you an error message and refuse to save the record if
you enter a duplicate. You don't have to do anything else.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks for responding so quickly, you were too quick... I added the
following addendum:

I've created a unique index for tblNom's ClientID and NomYear.
So if I the combined SSN and NomYear exists, I want to display an errmsg.
Thanks again!

Set the combo's properties so it has the extra fields you need.
Something like this:
Row Source: SELECT ClientID,
Trim([LastName] & ", " & [FirstName]) As
FullName,
SSN, LName, FName
FROM tblClient
ORDER BY LName, FName;
Column Count 5
Column Widths 0;2;0;0;0

Now set the Control Source of the box where you want to display the SSN
to:
=ClientID.Column(2)
and so on for the other columns of the combo.

Note that it is really important not to *store* the SSN, LName, and FName
as
well as the ClientID in the other table. I don't think you are making
that
mistake, as you didn't show those fields in tblNom.)

Hello,
I have two tables:
tblClientID
ClientID(pkey)
SSN
LName
FName

tblNom
ClientID(fkey)
NomYear

How do I set up a SSN combo box that displays SSN and FullName
(FullName:
[LastName] & ", " & [FirstName])? After selection, LName and FName
text
boxes are auto populated? Do I create a query first -> create an
unbound
combo box on the form -> then select the query? I can't get all the
fields
in my combo box to appear and the other text boxes populated.
I also want to create a tab control on the same form where page 1
contains
tblNom data and tblClient data on page 2.

TIA,
Lynn

 

[Allen Browne]

I'm confused.

You have a tblClient with fields:
ClientID AutoNumber primary key
SSN Text
LastName Text
...

You have a combo named SSN.
Its Control Source is ClientID.
But you also have another field named SSN.
I'm surprised Access isn't already confused at this point and giving errors.
It usually doesn't cope with having a control bound to one field but with
the same name as another.

If the combo is bound to ClientID, then any other compound index (such as
SSN + NomYear) will have no effect on this field, since its ControlSource is
neither SSN nor NomYear.

However, if ClientID is primary key, then it will be unique. You will
therefore not be able to have 2 records with the same ClientID + anything
else, because you can't have 2 records with the same ClientID.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

then something's wrong. The first line on my entry form is very simple:
SSN LASTNAME FIRSTNAME NOMYEAR

Doesn't the SSN combo need to be tied to tblNom.ClientID so that the
unique
index works? SSN is currently tied to tblClient.ClientID (note correction
to
table name).

Thanks again!

The fact that you added a unique index makes no difference to the way you
display the contents of the combo's columns in other text boxes.

If you added the unique index on the combination of fields in table
design,
then Access will give you an error message and refuse to save the record
if
you enter a duplicate. You don't have to do anything else.

Allen,
Thanks for responding so quickly, you were too quick... I added the
following addendum:

I've created a unique index for tblNom's ClientID and NomYear.
So if I the combined SSN and NomYear exists, I want to display an
errmsg.
Thanks again!


:

Set the combo's properties so it has the extra fields you need.
Something like this:
Row Source: SELECT ClientID,
Trim([LastName] & ", " & [FirstName])
As
FullName,
SSN, LName, FName
FROM tblClient
ORDER BY LName, FName;
Column Count 5
Column Widths 0;2;0;0;0

Now set the Control Source of the box where you want to display the
SSN
to:
=ClientID.Column(2)
and so on for the other columns of the combo.

Note that it is really important not to *store* the SSN, LName, and
FName
as
well as the ClientID in the other table. I don't think you are making
that
mistake, as you didn't show those fields in tblNom.)

Hello,
I have two tables:
tblClientID
ClientID(pkey)
SSN
LName
FName

tblNom
ClientID(fkey)
NomYear

How do I set up a SSN combo box that displays SSN and FullName
(FullName:
[LastName] & ", " & [FirstName])? After selection, LName and FName
text
boxes are auto populated? Do I create a query first -> create an
unbound
combo box on the form -> then select the query? I can't get all the
fields
in my combo box to appear and the other text boxes populated.
I also want to create a tab control on the same form where page 1
contains
tblNom data and tblClient data on page 2.

TIA,
Lynn

 

[Guest]

Hi Allen,
I got it to work!
The form is based on a query where I set the first field to tblNom:ClientID.
The combo box includes tblClient.ClientID, tblClient.SSN, tblClient.FullName
The combo box selection populates the next two text boxes: tblClientFName,
tblClientLName
I created a unqiue index for tblNom.ClientID and tblNom.NomYear.
When I enter an existing record, I get a duplicate value error, which is
what I want.

I'm confused.

You have a tblClient with fields:
ClientID AutoNumber primary key
SSN Text
LastName Text
...

You have a combo named SSN.
Its Control Source is ClientID.
But you also have another field named SSN.
I'm surprised Access isn't already confused at this point and giving errors.
It usually doesn't cope with having a control bound to one field but with
the same name as another.

If the combo is bound to ClientID, then any other compound index (such as
SSN + NomYear) will have no effect on this field, since its ControlSource is
neither SSN nor NomYear.

However, if ClientID is primary key, then it will be unique. You will
therefore not be able to have 2 records with the same ClientID + anything
else, because you can't have 2 records with the same ClientID.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

then something's wrong. The first line on my entry form is very simple:
SSN LASTNAME FIRSTNAME NOMYEAR

Doesn't the SSN combo need to be tied to tblNom.ClientID so that the
unique
index works? SSN is currently tied to tblClient.ClientID (note correction
to
table name).

Thanks again!

The fact that you added a unique index makes no difference to the way you
display the contents of the combo's columns in other text boxes.

If you added the unique index on the combination of fields in table
design,
then Access will give you an error message and refuse to save the record
if
you enter a duplicate. You don't have to do anything else.

Allen,
Thanks for responding so quickly, you were too quick... I added the
following addendum:

I've created a unique index for tblNom's ClientID and NomYear.
So if I the combined SSN and NomYear exists, I want to display an
errmsg.
Thanks again!


:

Set the combo's properties so it has the extra fields you need.
Something like this:
Row Source: SELECT ClientID,
Trim([LastName] & ", " & [FirstName])
As
FullName,
SSN, LName, FName
FROM tblClient
ORDER BY LName, FName;
Column Count 5
Column Widths 0;2;0;0;0

Now set the Control Source of the box where you want to display the
SSN
to:
=ClientID.Column(2)
and so on for the other columns of the combo.

Note that it is really important not to *store* the SSN, LName, and
FName
as
well as the ClientID in the other table. I don't think you are making
that
mistake, as you didn't show those fields in tblNom.)

Hello,
I have two tables:
tblClientID
ClientID(pkey)
SSN
LName
FName

tblNom
ClientID(fkey)
NomYear

How do I set up a SSN combo box that displays SSN and FullName
(FullName:
[LastName] & ", " & [FirstName])? After selection, LName and FName
text
boxes are auto populated? Do I create a query first -> create an
unbound
combo box on the form -> then select the query? I can't get all the
fields
in my combo box to appear and the other text boxes populated.
I also want to create a tab control on the same form where page 1
contains
tblNom data and tblClient data on page 2.

TIA,
Lynn