gfernandez said:
I hv once done this but I cannot remember how. I want a lookup field with
records that are added one by one each time a new value appears. For
instance, I hv several contributors, each one belonging to one department. I
do not have the full list of departments right now, but each new department
added to the list I want it to be available for the next record added. Can
somebody help me?
I've assumed your table is called 'tblStaff', with two fields,
'FullName' and 'Department'.
First, open the Table in Design View, select the 'Department' field
and click the 'Lookup' tab. Change the 'Display Control' to 'Listbox',
and change the 'RowSource' to "SELECT DISTINCT Department FROM
tblStaff". (nb. RowSource won't appear until you select Listbox!)
You should now be able to add data to the form, although if you want
the dropdown list to update you have to click Records/Refresh after
each new Department entry. If you want it to update automatically, you
need to use a Form.
From the Forms window, click 'Create form by using wizard'. Select
'tblStaff' from the Tables/Queries list, then click the button with
two arrows on it [>>] to copy all the fields into the 'Selected
Fields' list. Click 'Next', then choose 'Datasheet' and click
'Finish'.
At this point, you can only choose departments that already exist in
the table, so close the form, and then rename it to 'frmStaff' (this
will save any grief later!). Open the form in Design View, right-click
the 'Department' field and click 'Change To' then 'Combo Box'.
If you don't have the 'Properties' window open, right-click on
'Department' again and choose 'Properties'. Click the 'Event' tab, and
click the drop-down against 'After Update' so that '[Eventt Procedure]
appears, then click the button with three dots on it.
You should open a code window: edit it to read this:
Private Sub Department_AfterUpdate()
Me.Refresh
End Sub
Close the form - it should prompt you to save it.
Open the form and - fingers crossed - it should now be working...
--
Neil Sunderland
Braunton, Devon
Please observe the Reply-To address
