Creating a semi auto fill field

[Jeff]

Hi,

I have 2 Access 2003 databases will call A (client) and B (physician). Each
of these have 2 main tables, Table A and Table B. I have linked the 2
tables. What I would like to do is on the client data entry screen, when
they get to the physician question, that when they start enter the last name
of the physician, that this field access the physician db(Table B), and help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on the LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on physician.

How can I get both last and first name to appear? Is there a way to make it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName, but I
could not figure out when you enter a last name that the first name would
also populate.

Any help would be appreciated.

 

[Duane Hookom]

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

 

[Jeff]

If I do that in a form, do I need to do anything to Table A (client). If the
information that is created by the data entry person on a client, where is
that stored?

When I go to create reports I want to include the docs name, I need it to
refer to a field that would have the combo name, where would I point it. Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

 

[Duane Hookom]

You can use the combo box wizard to store the docs' IDs in the client table.
You do not need to (nor should you) store the doc names in the client table.

Any report where you want to see doc names, add the doc table to the
report's record source and join the doc ID fields.

--
Duane Hookom
MS Access MVP
--

If I do that in a form, do I need to do anything to Table A (client). If
the
information that is created by the data entry person on a client, where is
that stored?

When I go to create reports I want to include the docs name, I need it to
refer to a field that would have the combo name, where would I point it.
Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

--
Duane Hookom
MS Access MVP
--

Hi,

I have 2 Access 2003 databases will call A (client) and B (physician).
Each
of these have 2 main tables, Table A and Table B. I have linked the 2
tables. What I would like to do is on the client data entry screen,
when
they get to the physician question, that when they start enter the last
name
of the physician, that this field access the physician db(Table B), and
help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName
which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on the
LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the
DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on
physician.

How can I get both last and first name to appear? Is there a way to
make
it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName, but I
could not figure out when you enter a last name that the first name
would
also populate.

Any help would be appreciated.

 

[Jeff]

Thank you Duane. I think I have it set up correctly. The way it is now, you
have to enter the Docs ID number and the name will appear, but it does not
work in the reverse, you cannot enter a name. Is it suppose to work both
ways?

--
Thanks
Jeff

You can use the combo box wizard to store the docs' IDs in the client table.
You do not need to (nor should you) store the doc names in the client table.

Any report where you want to see doc names, add the doc table to the
report's record source and join the doc ID fields.

--
Duane Hookom
MS Access MVP
--

If I do that in a form, do I need to do anything to Table A (client). If
the
information that is created by the data entry person on a client, where is
that stored?

When I go to create reports I want to include the docs name, I need it to
refer to a field that would have the combo name, where would I point it.
Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

--
Duane Hookom
MS Access MVP
--

Hi,

I have 2 Access 2003 databases will call A (client) and B (physician).
Each
of these have 2 main tables, Table A and Table B. I have linked the 2
tables. What I would like to do is on the client data entry screen,
when
they get to the physician question, that when they start enter the last
name
of the physician, that this field access the physician db(Table B), and
help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName
which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on the
LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the
DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on
physician.

How can I get both last and first name to appear? Is there a way to
make
it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName, but I
could not figure out when you enter a last name that the first name
would
also populate.

Any help would be appreciated.

 

[Jeff]

Thanks for your help. I think I have it close to correct. Now if you enter
the id number the name appears, but you cannot enter it by name. Is this
suppose to work both ways or by ID number only?
--
Thanks
Jeff

You can use the combo box wizard to store the docs' IDs in the client table.
You do not need to (nor should you) store the doc names in the client table.

Any report where you want to see doc names, add the doc table to the
report's record source and join the doc ID fields.

--
Duane Hookom
MS Access MVP
--

If I do that in a form, do I need to do anything to Table A (client). If
the
information that is created by the data entry person on a client, where is
that stored?

When I go to create reports I want to include the docs name, I need it to
refer to a field that would have the combo name, where would I point it.
Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

--
Duane Hookom
MS Access MVP
--

Hi,

I have 2 Access 2003 databases will call A (client) and B (physician).
Each
of these have 2 main tables, Table A and Table B. I have linked the 2
tables. What I would like to do is on the client data entry screen,
when
they get to the physician question, that when they start enter the last
name
of the physician, that this field access the physician db(Table B), and
help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName
which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on the
LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the
DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on
physician.

How can I get both last and first name to appear? Is there a way to
make
it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName, but I
could not figure out when you enter a last name that the first name
would
also populate.

Any help would be appreciated.

 

[Duane Hookom]

You can set the first column width to 0" in order to only see the names.
When I create apps like this, the user rarely sees the ID.

--
Duane Hookom
MS Access MVP
--

Thank you Duane. I think I have it set up correctly. The way it is now,
you
have to enter the Docs ID number and the name will appear, but it does not
work in the reverse, you cannot enter a name. Is it suppose to work both
ways?

--
Thanks
Jeff

You can use the combo box wizard to store the docs' IDs in the client
table.
You do not need to (nor should you) store the doc names in the client
table.

Any report where you want to see doc names, add the doc table to the
report's record source and join the doc ID fields.

--
Duane Hookom
MS Access MVP
--

If I do that in a form, do I need to do anything to Table A (client).
If
the
information that is created by the data entry person on a client, where
is
that stored?

When I go to create reports I want to include the docs name, I need it
to
refer to a field that would have the combo name, where would I point
it.
Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff


:

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

--
Duane Hookom
MS Access MVP
--

Hi,

I have 2 Access 2003 databases will call A (client) and B
(physician).
Each
of these have 2 main tables, Table A and Table B. I have linked the
2
tables. What I would like to do is on the client data entry screen,
when
they get to the physician question, that when they start enter the
last
name
of the physician, that this field access the physician db(Table B),
and
help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName
which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on
the
LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the
DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on
physician.

How can I get both last and first name to appear? Is there a way to
make
it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName,
but I
could not figure out when you enter a last name that the first name
would
also populate.

Any help would be appreciated.

 

[Jeff]

Hi Duane,

I have most of it, but still having some issues. Are you available for
phone help, I can call you or you can call me . Please let me know.

I have 2 data entry screens. One adds new clients to the table and the
other is used for edits & updates. I still can't get either to enter by
name.

On the Add Clients, if i enter a ID number the physicians name pops right in.

On the Edit Clients, if I enter a ID number it changes, but no update to
physicians name, but if you switch to another client then go back, it updates
the name.

I also found out some of our clients have multiple docs listed, so I entered
a Second Physician ID Number in Table B. Then on each entry screen I made a
second set of fields as I did with the first, but the result is the fields
are populated with the information from the first set of fields. So for now
I just removed them until I figure out how to have 2 physicians names.

Thanks again Jeff
--
Thanks
Jeff

Thanks for your help. I think I have it close to correct. Now if you enter
the id number the name appears, but you cannot enter it by name. Is this
suppose to work both ways or by ID number only?
--
Thanks
Jeff

You can use the combo box wizard to store the docs' IDs in the client table.
You do not need to (nor should you) store the doc names in the client table.

Any report where you want to see doc names, add the doc table to the
report's record source and join the doc ID fields.

--
Duane Hookom
MS Access MVP
--

If I do that in a form, do I need to do anything to Table A (client). If
the
information that is created by the data entry person on a client, where is
that stored?

When I go to create reports I want to include the docs name, I need it to
refer to a field that would have the combo name, where would I point it.
Do
I need to create a seperate table that has client ID and DocsID?

Sorry ask so many questions, still trying to understand this new thing.
--
Thanks
Jeff


:

If you want to see the docs' full names use
SELECT ID, DocsLastName & ", " & DocsFirstName
FROM [Table B]
ORDER BY DocsLastName & ", " & DocsFirstName;

You now have only 2 columns so adjust properties accordingly.

This is being done in a form and not in your table design, correct?
http://www.mvps.org/access/lookupfields.htm

--
Duane Hookom
MS Access MVP
--

Hi,

I have 2 Access 2003 databases will call A (client) and B (physician).
Each
of these have 2 main tables, Table A and Table B. I have linked the 2
tables. What I would like to do is on the client data entry screen,
when
they get to the physician question, that when they start enter the last
name
of the physician, that this field access the physician db(Table B), and
help
auto fill the field in Table A.

The first 3 fields in Table B are ID, DocsLastName & DocsFirstName
which I
believe are the needed fields to make this work.

In Table A I created a new field called DocsName, then clicked on the
LookUp
tab in Design view and entered the following:

Display Control - List Box
Row Source Type - Table/Query
Row Souce - Table B
Bound Column - 1
Column Count - 3
Column Heads - No
Column Widths - 0";2";2"

On the General Tab the only fields filled are:
Field Size - 50
Required - No
Allow Zero Length - No
Indexed - Yes (Duplicates OK)
Unicode Compression - Yes
IME Control - No Control
IME Sentence Mode - None

I then saved the table, then went to the Datasheet view. In the
DocsName
field, there is now a pull down list that has the DocsLastName &
DocsFirstName, but only the last name appears when you click on
physician.

How can I get both last and first name to appear? Is there a way to
make
it
appear "DocsLastName, DocsFirstName"?

Am I going about this the wrong way or is there a solution?

I also tried just using the 2 fields DocsLastName, DocsFirstName, but I
could not figure out when you enter a last name that the first name
would
also populate.

Any help would be appreciated.