Combo Box that will update several fields in a form

[njosgood via AccessMonster.com]

I would appreciate any help.

I have an unbound combo box that I need to look up an Employee name, when I
click on the desired name I would like it to update the following fields on
my form. Employee Id, Employee Last Name, Employee First Name.

Combo box 10

Row Source: SELECT [tbl_Employee].[EmployeeID], [tbl_Employee].
[EmployeeLastName], [tbl_Employee].[EmployeeFirstName] FROM tbl_Employee
ORDER BY [tbl_Employee].[EmployeeLastName], [tbl_Employee].[EmployeeFirstName]
;

Bound column 1

AfterUpdate:
Private Sub Combo10_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MemberID] = " & Str(Me![Combo10])
Me.Bookmark = rs.Bookmark
End Sub

 

[Pat Hartman\(MVP\)]

1. It isn't necessary to actually copy the columns from the employee table
to the table your form is based on. We talk a lot about normalization and
the proper way of splitting data into tables but we don't talk much about
how to turn that separate data back into information. To do what you need
doesn't require any code at all. You simply need to create a query that
joins the form's main table to the employee table and use that query as the
RecordSource for the form. You will be able to choose the employee fields
from the form's field list just as you choose fields from the main table.
Just a caution though - when employing this technique it is recommended that
you set the locked property to Yes for the fields from the employee table so
that they are not updated accidentally from this form.
2. Rather than combo box 10, why not change the Name of the control before
you add any event code. It is far easier to work with code that has
meaningful names. How about cboEmployeeID?
3. It is not necessary to use DAO/ADO to update fields of the current
record. You only have to reference them:
Me.EmployeeFirstName = Me.cboEmployeeID.Column(2)
Me.EmployeeLastName = Me.cboEmployeeID.Column(1)
etc.
But, in this case, use suggestion #1. I only included this suggestion to
further your knowledge and also because there are some cases where you
actually need to copy the information. One frequent case is copying the
price from the product table and storing it with the item in the OrderDetail
table. This is necessary because prices change over time and you want
always want the price that was in effect when the order was placed rather
than the current price. In your case you always want the current values.
Employees occasionally change their names so if you store an employee's last
name and she gets married and changes her name, you'll have what we call an
update anomaly. Data which should be the same is different.

 

[njosgood via AccessMonster.com]

1. It isn't necessary to actually copy the columns from the employee table
to the table your form is based on. We talk a lot about normalization and
the proper way of splitting data into tables but we don't talk much about
how to turn that separate data back into information. To do what you need
doesn't require any code at all. You simply need to create a query that
joins the form's main table to the employee table and use that query as the
RecordSource for the form. You will be able to choose the employee fields
from the form's field list just as you choose fields from the main table.
Just a caution though - when employing this technique it is recommended that
you set the locked property to Yes for the fields from the employee table so
that they are not updated accidentally from this form.
2. Rather than combo box 10, why not change the Name of the control before
you add any event code. It is far easier to work with code that has
meaningful names. How about cboEmployeeID?
3. It is not necessary to use DAO/ADO to update fields of the current
record. You only have to reference them:
Me.EmployeeFirstName = Me.cboEmployeeID.Column(2)
Me.EmployeeLastName = Me.cboEmployeeID.Column(1)
etc.
But, in this case, use suggestion #1. I only included this suggestion to
further your knowledge and also because there are some cases where you
actually need to copy the information. One frequent case is copying the
price from the product table and storing it with the item in the OrderDetail
table. This is necessary because prices change over time and you want
always want the price that was in effect when the order was placed rather
than the current price. In your case you always want the current values.
Employees occasionally change their names so if you store an employee's last
name and she gets married and changes her name, you'll have what we call an
update anomaly. Data which should be the same is different.

I would appreciate any help.

[quoted text clipped - 23 lines]

Me.Bookmark = rs.Bookmark
End Sub

Please bare with me I'm rather new with access. I'm not sure that this will
make a differance to your reply but I will try and give you more information
on what I am trying to acomplish. I'm trying to design a data base to track
all cash register sales by employee by day.
I have several tables. tbl_Employee, Tbl_Employee_Daily_Financial,
Tbl_Register_one_Bar_Sales, tbl_Register_One_Ticket_Sales,
tbl_Register_One_Non_Cash_Sales, tbl_Register_Two_Non_Cash_Sales

I have the same amount of forms. The tbl_employee is for all employee
information only, address, Phone ect
the tbl_Employee_Daily_Financial is for tracking all of the other data.
On the frm_Employee it will be one form to put in information about the
employee.
On the Frm_Employee_Daily _Financial I will Be adding subforms to the main
form to track sales by employee. I wanted all the information for sales on
one form.

tbl Employees primary key is employee ID. one to many relationship
tbl Employee Daily Financial primary key is date ID and Employee ID. One to
many relationship.
all of my other tables Primary key are date ID. one to one relationship based
on their only being one record for that specific date.

I wanted an easy way of looking up and adding the employee information. ID ,
LastName, FirstName to my EmployeeDaily FinancialForm.

Its very likely that this is not the best way to set up my data base but ther
are not to many people around the area were i live that know access and I
have only had up to access 2000 with visual basic and none of the visual
basic so I have been trying to learn as I go. I,m greatfull for this website
and I would like to thank everyone for their support ,I just wish I would
have found it sooner. THANK YOU

 

[Pat Hartman\(MVP\)]

The additional information doesn't change anything I've said. Employee
first and last names do not need to be stored in the related tables. Only
the employee ID does. The employeeID allows the tables to be joined in a
query so that the first and last name are available for all forms/reports
regardless of which is their primary table. You simply use a query rather
than a table as the form/report's RecordSource.

1. It isn't necessary to actually copy the columns from the employee table
to the table your form is based on. We talk a lot about normalization and
the proper way of splitting data into tables but we don't talk much about
how to turn that separate data back into information. To do what you need
doesn't require any code at all. You simply need to create a query that
joins the form's main table to the employee table and use that query as
the
RecordSource for the form. You will be able to choose the employee fields
from the form's field list just as you choose fields from the main table.
Just a caution though - when employing this technique it is recommended
that
you set the locked property to Yes for the fields from the employee table
so
that they are not updated accidentally from this form.
2. Rather than combo box 10, why not change the Name of the control before
you add any event code. It is far easier to work with code that has
meaningful names. How about cboEmployeeID?
3. It is not necessary to use DAO/ADO to update fields of the current
record. You only have to reference them:
Me.EmployeeFirstName = Me.cboEmployeeID.Column(2)
Me.EmployeeLastName = Me.cboEmployeeID.Column(1)
etc.
But, in this case, use suggestion #1. I only included this suggestion to
further your knowledge and also because there are some cases where you
actually need to copy the information. One frequent case is copying the
price from the product table and storing it with the item in the
OrderDetail
table. This is necessary because prices change over time and you want
always want the price that was in effect when the order was placed rather
than the current price. In your case you always want the current values.
Employees occasionally change their names so if you store an employee's
last
name and she gets married and changes her name, you'll have what we call
an
update anomaly. Data which should be the same is different.

I would appreciate any help.

[quoted text clipped - 23 lines]

Me.Bookmark = rs.Bookmark
End Sub

Please bare with me I'm rather new with access. I'm not sure that this
will
make a differance to your reply but I will try and give you more
information
on what I am trying to acomplish. I'm trying to design a data base to
track
all cash register sales by employee by day.
I have several tables. tbl_Employee, Tbl_Employee_Daily_Financial,
Tbl_Register_one_Bar_Sales, tbl_Register_One_Ticket_Sales,
tbl_Register_One_Non_Cash_Sales, tbl_Register_Two_Non_Cash_Sales

I have the same amount of forms. The tbl_employee is for all employee
information only, address, Phone ect
the tbl_Employee_Daily_Financial is for tracking all of the other data.
On the frm_Employee it will be one form to put in information about the
employee.
On the Frm_Employee_Daily _Financial I will Be adding subforms to the
main
form to track sales by employee. I wanted all the information for sales on
one form.

tbl Employees primary key is employee ID. one to many relationship
tbl Employee Daily Financial primary key is date ID and Employee ID. One
to
many relationship.
all of my other tables Primary key are date ID. one to one relationship
based
on their only being one record for that specific date.

I wanted an easy way of looking up and adding the employee information. ID
,
LastName, FirstName to my EmployeeDaily FinancialForm.

Its very likely that this is not the best way to set up my data base but
ther
are not to many people around the area were i live that know access and I
have only had up to access 2000 with visual basic and none of the visual
basic so I have been trying to learn as I go. I,m greatfull for this
website
and I would like to thank everyone for their support ,I just wish I would
have found it sooner. THANK YOU

 

[njosgood via AccessMonster.com]

The additional information doesn't change anything I've said. Employee
first and last names do not need to be stored in the related tables. Only
the employee ID does. The employeeID allows the tables to be joined in a
query so that the first and last name are available for all forms/reports
regardless of which is their primary table. You simply use a query rather
than a table as the form/report's RecordSource.
[quoted text clipped - 79 lines]

and I would like to thank everyone for their support ,I just wish I would
have found it sooner. THANK YOU

Pat thank you I'll give it a try.

 

[Scott Miller]

The additional information doesn't change anything I've said. Employee
first and last names do not need to be stored in the related tables. Only

[quoted text clipped - 8 lines]
Pat thank you I'll give it a try.

i believe Pat is mentioning a technique called autolookup query. I have used
this successfully quite a few times. If you do a search on autolookup query,
you will find more information. Long story short-- Make a query that uses the
FOREIGN KEY ( many side of the relationship) EMPLOYEEID from your
tbl_Employee_Daily_Financial and the whatever employee information you need
from the tbl_Employee ( name etc), the add to the query whatever fields you
wish to display from the tbl_Employee_Daily_Financial. If you make the
EmployeeID a combo box and set the source to the query you just made, it will
fill in the employee fields after you select the appropriate EmployeeID. Word
of caution, some people like to use social security numbers as EmployeeIDs,
please save yourself the Privacy Act violation hassle, and either 1) don't
use them as visible IDs, or 2) Use some SQL SELECT queries to display names
not the social security number.

I hope this helps.

Scott

 

[jro via AccessMonster.com]

[quoted text clipped - 3 lines]

Pat thank you I'll give it a try.

i believe Pat is mentioning a technique called autolookup query. I have used
this successfully quite a few times. If you do a search on autolookup query,
you will find more information. Long story short-- Make a query that uses the
FOREIGN KEY ( many side of the relationship) EMPLOYEEID from your
tbl_Employee_Daily_Financial and the whatever employee information you need
from the tbl_Employee ( name etc), the add to the query whatever fields you
wish to display from the tbl_Employee_Daily_Financial. If you make the
EmployeeID a combo box and set the source to the query you just made, it will
fill in the employee fields after you select the appropriate EmployeeID. Word
of caution, some people like to use social security numbers as EmployeeIDs,
please save yourself the Privacy Act violation hassle, and either 1) don't
use them as visible IDs, or 2) Use some SQL SELECT queries to display names
not the social security number.

I hope this helps.

Scott

I would like to Thank all of You for your advise