Have not been able to figure this out and would greatly appreciate some assistance...
Here is my database (tables and forms design):
Database design (portion)
tblEmployee (main table)
EmployeeID (autonumber, PK)
LastName (text)
FirstName (text)
tblIALvl (data table)
IALvlID (autonumber, PK)
IALvl (text)
tblEmployeeIALvlJunction (M-M junction between tblEmployee and tblIALvl)
EmployeeIALvlID (autonumber, PK)
EmployeeID (long integer, FK from tblEmployee)
IALvlID (long integer, FK from tblIALvl)
frmEmployee (Main Form)
EmployeeID (bound to tblEmployee.EmployeeID) textbox (PK)
frmEmployeeIALvlJunction (subform of frmEmployee)
EmployeeID (bound to tblEmployeeIALvlJunction.EmployeeID) textbox (FK from tblEmployee)
EmployeeID Default Value =[Forms]![frmEmployeeNew]![EmployeeID]
Current IA Lvl (unbound) Listbox is populated using CurrentIALvlQuery
SELECT tblIALvl.IALvl, tblIALvl.IALvlID, tblEmployeeIALvlJunction.EmployeeID
FROM tblIALvl INNER JOIN (tblEmployee INNER JOIN tblEmployeeIALvlJunction ON tblEmployee.EmployeeID = tblEmployeeIALvlJunction.EmployeeID) ON
tblIALvl.IALvlID = tblEmployeeIALvlJunction.IALvlID
WHERE (((tblEmployeeIALvlJunction.EmployeeID) = [Forms]![frmEmployeeNew]![EmployeeID]));
Process flow:
The main form is used to input new employee data. When opened, it begins at a “New” record (EmployeeID is set to “New”). When beginning to enter the last name, EmployeeID is set by autonumber.
The frmEmployeeIALvlJunction sub-form uses frmEmployee.EmployeeID to set EmployeeID via Default Value control. I use a combobox (IALvl bound to Control source IALvlID, and data selected by a query: SELECT tblIALvl.IALvlID, tblIALvl.IALvl) to select the IA Lvl. Then use a Save Record button to save the data. When this happens, the listbox CurrentIALvl returns the information derived from the query CurrentIALvlQuery (above). When I click the “Add new record” button on the main form, EmployeeID is then set to “New”, and EmployeeID on the subform is not set until new data is input into the main form (then set to the value in EmployeeID from the main form).
Problem:
The list box (in the sub form), however, still shows the data from the previous record. What is causing this, and how can I make it so that the listbox also clears the data?
Thanks very much in advance!