Select cbo value. Automate Recording of subsequent cbo Lkup field values into Main tb

[Martin Lea]

Hello Everyone
I have a main table tblJOBS. In this table I have the fields ‘F_Loc’ (Lookup), ‘Gender’ (Text) and ‘Facility’ (Text).
The ‘F_Loc’ field lookup table is called ‘tblLK_Location’. The tblLK_Location table has four (Text) fields as follows:
Location – This could be Laundry, Office, Library or Room Number etc.
Info – Provides brief help for the data imputer,
Gender – Only has ‘Male’, ‘Female’, ‘Male & Female’
Facility – Only has ‘Shower’, ‘Toilet’, ‘Bathroom’.
Where a room number has been recorded in the Location field, the attributes of that room are pre-recorded in the 3rd and 4th fields of the lookup table i.e.
LOCATION ‘RM123’, INFO “”, GENDER ‘Male’, FACILITY ‘Toilet’
LOCATION ‘Electrical’, INFO Select for E’ Workshop or E’ Room, GENDER “” FACILITY “”
LOCATION ‘RM234’, INFO “” GENDER ‘Female’, FACILITY ‘Shower’

I have an input form frmINPUT_JOBS; a ‘Single Form’. The record source is tblJOBS. So on my form I have ‘F_Loc’ with a drop down selection list. I present the first two fields to the imputer i.e. Location and Info. I have ‘Gender’ and ‘Facility’ fields on my main form from tblJOBS. When the data imputer selects ‘RM123’ from the ‘F_Loc’ drop down selection, I would like ‘Male’ to be automatically entered into the ‘Gender’ field and ‘Toilet’ to be automatically entered into the ‘Facility’ field.
REASON: Once tblJOBS becomes populated, these ‘F’ (Find) fields provide search criterion for recorded jobs. I want to make the data imputer’s work easier by automating these search tag entries enabling only one selection to be made. I am confident it requires VLookup but I have had no success with the WHERE part. I am using MS Access Office 2003. I would like to express my gratitude in advance to anyone who is able to assist me.

[Martin Lea]

Hello Folks
I resolved it. Thank you, those who took a look at my problem. Solution: I Run several update queries via a macro when the input form is closed. One update query (MS Access qbe) runs a query 'WHERE' RoomNo is 111 or 112 or 114 and updates the 'Gender' field to 'Ladies' for each of the rooms listed. Another update query is set to 'WHERE' RoomNo is 111 or 235 or 236. This query Updates the 'Facility' field to 'Toilets' for all of the rooms listed and so on. The data imputer only records the room number on the form. The macro will run the update queries on the 'form close' event. My queries do work. I have not set up the macro yet. I am extremely confident this will work on the ‘form close event’. I hope this is helpful to someone out there. This is the best solution for me because it is the only solution I am aware of. There may be a better method employed by adept Access users. Perhaps DLookup was not appropriate on this occasion.