Automatically Fill In Fields

[Karen]

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

Any help is appreciated...

 

[Jim Allensworth]

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

Sure.

Add the employee names to the Row Source of the combo box.
Change the column count to include those. (That would be 3 columns.)
Set the Column widths for those to 0. For example: 1";0";0"

Set the Control Source for the text boxes to the hidden columns of the
combo box.
Like: =[cboMyCombo].[Column](1) for the first name and
=[cboMyCombo].[Column](2) for the last name - assuming you have them
in that order in the row source.

Note that columns are zero base so 1 is the second column.

That should do it.

- Jim

 

[Karen]

so do I have two add the First_Name and Last_Name fields
to my form and modify their properties or do I have to add
new text boxes or do I do everything from my employee
field properties?

could you be a little more detailed, if possible. Sorry
I'm very new at this and need a little more explanation..

Thanks.

-----Original Message-----

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

Sure.

Add the employee names to the Row Source of the combo box.
Change the column count to include those. (That would be 3 columns.)
Set the Column widths for those to 0. For example: 1";0";0"

Set the Control Source for the text boxes to the hidden columns of the
combo box.
Like: =[cboMyCombo].[Column](1) for the first name and
=[cboMyCombo].[Column](2) for the last name - assuming you have them
in that order in the row source.

Note that columns are zero base so 1 is the second column.

That should do it.

- Jim

.

 

[Jim Allensworth]

Everything would propagate from the data in the combo box.

Assuming that the combo with the emp# has a Row Source Type of Query;
add the names to the query (Row Source) - select the Row Source in the
properties of the combo box and click the elipsis button [...] to the
right to modify the query.

Modify the query so that the names follow the emp#. After that select
the Column Count on the Format tab and enter 3. Then select the Column
Widths property and enter 1";0";0".

Then just add the Control Sources to the textboxes where you want the
names to appear - as I mentioned previously.

- Jim

so do I have two add the First_Name and Last_Name fields
to my form and modify their properties or do I have to add
new text boxes or do I do everything from my employee
field properties?

could you be a little more detailed, if possible. Sorry
I'm very new at this and need a little more explanation..

Thanks.

-----Original Message-----

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

Sure.

Add the employee names to the Row Source of the combo box.
Change the column count to include those. (That would be 3 columns.)
Set the Column widths for those to 0. For example: 1";0";0"

Set the Control Source for the text boxes to the hidden columns of the
combo box.
Like: =[cboMyCombo].[Column](1) for the first name and
=[cboMyCombo].[Column](2) for the last name - assuming you have them
in that order in the row source.

Note that columns are zero base so 1 is the second column.

That should do it.

- Jim

.

 

[Mike Painter]

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

In general you should not fill fields with information from other tables.
The table you have the Emp # number in should be related to the employee
information with a query and that should be used in forms and reports.
No coding is required and when Mary Jane Smith gets married you will not
have to go through all the other tables and change her last name to Smyth.

 

[John Vinson]

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a combo
box value list of emp #'s I would like that person's first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that the
user is re-assured that they chose the right employee.

Is this possible??

Possible and pretty easy. I'd suggest using a Combo Box to enter the
employee number; this combo should be based on a Query returning the
fields that you want to see.

On the form put textboxes with Control Sources

=comboboxname.Column(n)

where (n) is the zero based subscript of the field in the query - that
is, if the LastName is the second column, use (1).

 

[Mary]

you can use a Dlookup statement in code on the after
update event. I can do this in Access 97, but am not sure
how it works in later versions. Would you still be
interested in the code?>-----Original Message-----

 

[Karen]

Yes, I would be interested in the code please...

Thanks..

 

[Karen]

Yes, I would be interested in the code please...

Thanks..

 

[Jim Allensworth]

Hello Jim,

I did what you said but still not able to see the first
and last names on my form.

My query for the field emp # combo box (in the Row Source)
is SELECT [EMPLOYEE].[PRI_SN], [EMPLOYEE].[Surname],
[EMPLOYEE].[Given_Name] FROM EMPLOYEE;

In the Column Count (of the emp # field) I added 3 to the
Format tab. Then entered the Column

Widths property of 1";0";0".

Then I added two text fields to my form and in the Control
Source of one I added =[cboMyCombo].[Column](1)and to the
other one I added =[cboMyCombo].[Column](2).

On my form I am able to select the emp # but in the other
two text boxes all I see is #Name? and nothing is being
entered after I select the emp #.

Any ideas why?

Karen, substitute the name of your combo box for cboMyCombo - I doubt
that is what yours is named ;-)

Otherwise it looks fine.

- Jim

 

[Karen]

Thanks Jim that worked great...

-----Original Message-----

Hello Jim,

I did what you said but still not able to see the first
and last names on my form.

My query for the field emp # combo box (in the Row Source)
is SELECT [EMPLOYEE].[PRI_SN], [EMPLOYEE].[Surname],
[EMPLOYEE].[Given_Name] FROM EMPLOYEE;

In the Column Count (of the emp # field) I added 3 to the
Format tab. Then entered the Column

Widths property of 1";0";0".

Then I added two text fields to my form and in the Control
Source of one I added =[cboMyCombo].[Column](1)and to the
other one I added =[cboMyCombo].[Column](2).

On my form I am able to select the emp # but in the other
two text boxes all I see is #Name? and nothing is being
entered after I select the emp #.

Any ideas why?

Karen, substitute the name of your combo box for cboMyCombo - I doubt
that is what yours is named ;-)

Otherwise it looks fine.

- Jim
.

 

[Karen]

Jim,

Is there any way to have the emp # appear as the first and
last names instead. So all that the form would display
would be a combo box for the first and last name and
whoever's name the user chose it would add thier PRI to
the table but dispaly there name on the form?

-----Original Message-----
Thanks Jim that worked great...

-----Original Message-----

Hello Jim,

I did what you said but still not able to see the first
and last names on my form.

My query for the field emp # combo box (in the Row Source)
is SELECT [EMPLOYEE].[PRI_SN], [EMPLOYEE].[Surname],
[EMPLOYEE].[Given_Name] FROM EMPLOYEE;

In the Column Count (of the emp # field) I added 3 to the
Format tab. Then entered the Column
Widths property of 1";0";0".

Then I added two text fields to my form and in the Control
Source of one I added =[cboMyCombo].[Column](1)and to the
other one I added =[cboMyCombo].[Column](2).

On my form I am able to select the emp # but in the other
two text boxes all I see is #Name? and nothing is being
entered after I select the emp #.

Any ideas why?

Karen, substitute the name of your combo box for cboMyCombo - I doubt
that is what yours is named ;-)

Otherwise it looks fine.

- Jim
.

.

 

[Jim Allensworth]

Yes.
Actually, IMO that is a better UI design. The user is more likely to
know the employee's name than their #.

So, just skip the 2 textboxes that show the names. Change your Row
Source to:

SELECT [PRI_SN], [Surname] & ", " & [Given_Name] AS EmpName
FROM EMPLOYEE;

This will return two columns:
PRI and EmpName (last, first concatenated)

Now set the combo box's Column Count to 2 and the Column Widths to
0";1.5"
(Of course adjust that last width to whatever works best for you)

I'm not clear on what you mean when you say "add their PRI to the
table". Are you trying to look up a record for an employee or to add a
new record using that employee?

At any rate you would use the combo box's value to do it in the After
Update event of the combo.

- Jim

Jim,

Is there any way to have the emp # appear as the first and
last names instead. So all that the form would display
would be a combo box for the first and last name and
whoever's name the user chose it would add thier PRI to
the table but dispaly there name on the form?

-----Original Message-----
Thanks Jim that worked great...

-----Original Message-----
On Fri, 21 Nov 2003 07:16:33 -0800, "Karen"

Hello Jim,

I did what you said but still not able to see the first
and last names on my form.

My query for the field emp # combo box (in the Row Source)
is SELECT [EMPLOYEE].[PRI_SN], [EMPLOYEE].[Surname],
[EMPLOYEE].[Given_Name] FROM EMPLOYEE;

In the Column Count (of the emp # field) I added 3 to the
Format tab. Then entered the Column
Widths property of 1";0";0".

Then I added two text fields to my form and in the Control
Source of one I added =[cboMyCombo].[Column](1)and to the
other one I added =[cboMyCombo].[Column](2).

On my form I am able to select the emp # but in the other
two text boxes all I see is #Name? and nothing is being
entered after I select the emp #.

Any ideas why?
Karen, substitute the name of your combo box for cboMyCombo - I doubt
that is what yours is named ;-)

Otherwise it looks fine.

- Jim
.

.

 

[Mike Painter]

I'm not sure what I missed in this exchange that justifies the copying of
data in one table to another.
I repeat that it is rarely a good idea and always requires a lot more work
than doing it the way it *should* be done in a relational database.

If the employee names change over time for a given ID and you must have the
original persons name there then it *might* be justified.

If not this is the wrong way to do it and requires a lot more work.
No code, one query and one wizard built combo.

Hello Jim,

I did what you said but still not able to see the first
and last names on my form.

My query for the field emp # combo box (in the Row Source)
is SELECT [EMPLOYEE].[PRI_SN], [EMPLOYEE].[Surname],
[EMPLOYEE].[Given_Name] FROM EMPLOYEE;

In the Column Count (of the emp # field) I added 3 to the
Format tab. Then entered the Column

Widths property of 1";0";0".

Then I added two text fields to my form and in the Control
Source of one I added =[cboMyCombo].[Column](1)and to the
other one I added =[cboMyCombo].[Column](2).

On my form I am able to select the emp # but in the other
two text boxes all I see is #Name? and nothing is being
entered after I select the emp #.

Any ideas why?

-----Original Message-----
Everything would propagate from the data in the combo box.

Assuming that the combo with the emp# has a Row Source Type of Query;
add the names to the query (Row Source) - select the Row Source in the
properties of the combo box and click the elipsis button [...] to the
right to modify the query.

Modify the query so that the names follow the emp#. After that select
the Column Count on the Format tab and enter 3. Then select the Column
Widths property and enter 1";0";0".

Then just add the Control Sources to the textboxes where you want the
names to appear - as I mentioned previously.

- Jim

so do I have two add the First_Name and Last_Name fields
to my form and modify their properties or do I have to add
new text boxes or do I do everything from my employee
field properties?

could you be a little more detailed, if possible. Sorry
I'm very new at this and need a little more explanation..

Thanks.
-----Original Message-----
On Thu, 20 Nov 2003 11:32:44 -0800, "Karen"

Hello,

I am creating a form and was wondering if there was any
way to automatically fill in fields with data from one
of
my tables.

I have an emp #: field on my form, what I would like to
have done is when a user populates the field from a
combo
box value list of emp #'s I would like that person's
first
and last name (which are seperate fields in my employee
table) to appear on the form in another field so that
the
user is re-assured that they chose the right employee.

Is this possible??

Sure.

Add the employee names to the Row Source of the combo
box.
Change the column count to include those. (That would be
3 columns.)
Set the Column widths for those to 0. For example:
1";0";0"

Set the Control Source for the text boxes to the hidden
columns of the
combo box.
Like: =[cboMyCombo].[Column](1) for the first name and
=[cboMyCombo].[Column](2) for the last name - assuming
you have them
in that order in the row source.

Note that columns are zero base so 1 is the second column.

That should do it.

- Jim

.

.

 

[Jim Allensworth]

I'm not sure what I missed in this exchange that justifies the copying of
data in one table to another.
I repeat that it is rarely a good idea and always requires a lot more work
than doing it the way it *should* be done in a relational database.

If the employee names change over time for a given ID and you must have the
original persons name there then it *might* be justified.

If not this is the wrong way to do it and requires a lot more work.
No code, one query and one wizard built combo.

Well, Mike I think what you missed is the point.

When Karen mentions a "field" on a form - like a lot of other people -
she really is referring to a control. If you go back and re-read it
I'm sure you will see that.

It really isn't a normalization issue at all.

And, watch those wizards. ;-)

- Jim