I will try to be as descriptive as possible. I have two tables right now "Casework" and "Employee Information". I have two forms "Casework" and "Employee Information Search Form". The main form which will be used is the "Casework" form. This form contains a section labeled employee information and contains the fields Employee ID (EID), Last & First Name, Email, Business Group, Company, Region, and Country. When a user is on the form and enters an EID that matches a record in the Employee Information table, the other fields will DLookUP the corresponding data for that Employee ID (EID). All is great so far.
If the user inputs an EID that has not been entered before, a dialog pops up and says "employee not found". Once you click "ok" the "Employee Information Search Form" opens in a dialog box for the user to input a new Employee record for that EID. Once all the information is entered into that dialog box (employee information search form), you hit a button on the top that saves the record and closes the employee form, returning you back to the new entry in the "casework" form you were originally at. All works perfect, until this next part.
Now that there IS a record in the "Employee Information" table that matches that new EID, I need the DLookUp fields to populate. I have tried putting a "refresh" command button that essentially just requeries the EID field and that works. It will populate the other employee fields with the new information. The issue is when you go to save the record, Access still thinks there is not a record matching the EID you just created and throws an error for the DLookUP fields. The only way I can seem to get around this error is this: After the new employee information is entered and the dialog box is closed, you have to delete the EID field and re-enter the EID to populate the DLookUp fields.
Is there a way to do this without having the user delete the characters and re-type them in? Why wont the "refresh" button work?
Please bear with me as I am fairly new to Access and my abilities are really limited. I appreciate all the patience and help!