DLOOKUP Save to Table

[Chris]

I am helping the owner of a table who inherited a database that needs to be
re-disigned in the future. For now, she has a form where the user selects the
employee name from a drop-down list. (The values are from a separate table.)
When the name is displayed, a separate field displays the associated
employee number (dlookup is used to display the number). The employee name is
saved to a table but they also want to save the employee number. (I know this
isn't good database design, but it isn't my db.) What is the best way to
save the emp number that is populated using the dlookup?

 

[Mr. B]

Chris,

Actually, if this is susposed to work as it would appear from your
description, it is not bad database design. What this sounds like to me is
that the table to which the data is being written is a table the holds many
records that may be related to one employee and all they are trying to do is
to select the employee from a combo box and save the foreign key from the
Employee table to a field in the current table.

If this is the case, all that needs to happen is that the record source for
the combo box needs to show the Primary Key field (employee ID number) and
the Employee Name for the combo box. Then set the "Control Source" to the
Foreign Key field that needs to hold the Employee ID value. Set the column
count of the combo box to 2 and set the Column Widths to "0; 2" (that is a
zero and another value like one or two for the width of the display area for
the Employee Nam) This will cause the combo box to show the employee name
but store the Employee ID value in the control and in the field in the table.


-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[John W. Vinson]

I am helping the owner of a table who inherited a database that needs to be
re-disigned in the future. For now, she has a form where the user selects the
employee name from a drop-down list. (The values are from a separate table.)
When the name is displayed, a separate field displays the associated
employee number (dlookup is used to display the number). The employee name is
saved to a table but they also want to save the employee number. (I know this
isn't good database design, but it isn't my db.) What is the best way to
save the emp number that is populated using the dlookup?

Are either of these "Lookup Fields" in the table?

You're on the right track of course - the table should include only the
employee number (as the bound column/control source of the combo box) and
should not contain the employee name at all.

If they are emphatic and determined to do it wrong, to store both fields
redundantly, and to accept the price that they will (not may!) at some point
have inconsistancies between the employee name/number pairing in the employee
table and the pairing in the other table... then you can put code in the
AfterUpdate event of the combo box to "push" the other value into a bound
textbox:

Private Sub cboEmployee_AfterUpdate()
Me!txtEmployeeNo = Me!cboEmployee.Column(n)
End Sub

where n is the zero based index of the column that you want to store.