Lookup field on form changes data in table

  • Thread starter Thread starter Karen Raemer
  • Start date Start date
K

Karen Raemer

I'm new to access! On my employee form I have a combo box
to enter the last name to bring up the entire record from
my employee table. When I type in the last name the
record appears correctly. However, now the social
security number of the current record is entered into the
last name field of my table. How do I protect the data in
my table (last name and SS#), and still be able to "look
up" employees (rather than scroll through the entire
table).
 
Hi Karen,

It sounds as though you are trying to use a data bound control as a record
selector control. In other words, the control into which you are entering
Last Name is actually the LastName field of the current record - Essentially
your form is staying on the same record. Typically we use a separate control
to allow the user to find a specific record - this should be an unbound
control, often put into the form header. If the recordsource of the main
form is the Employee table and you want to add a control that allows you to
select the Employee whose record you want to view/edit, then you should have
an unbound combo. Unbound simply means that the ControlSource property of
the control is empty and that what you put into that control is not saved
anywhere after the form closes.

In your case, the combo you describe is probably bound to a primary key
field (Employeeid?). I would suggest that you move it to the header of the
form and then clear the ControlSource property of the existing control. Also
rename the combo to something other than the field name (ie if the field
name is Employeeid I would name the unbound combo cboEmployeeid). You will
need to add some code to the AfterUpdate event of the combo so that record
navigation will occur to display the record which has the Employeeid
selected in the now unbound combo.

Here's some sample code:

In the AfterUpdate event of the combo/listbox put the following code:

with me.recordsetclone
.findfirst "Employeeid =" & me.cboEmployeeid
if not .nomatch then
me.bookmark=.bookmark
endif
end with


Finally, add a textbox control to the form and bind it to the Employeeid
field.
 
Back
Top