Preserving the ability to input data while using DLookup on a continuous form

[jumnhy]

Hello all,

I have a form used to select various employee and process training records. One half of the bottom of the form is a subform displaying the trainings that we "think" should be needed. It uses a complicated query to link employee names and job titles with what training we already have on record, compares what's on record with what trainings we have determined might be needed for a given job title, and checks the date of each employee's most recent training on each SOP against the most recent revision dates of the SOPs in our table of controlled documents. Trainings that are missing or out of date relative to that most recent revision are then displayed on the subform.

Double-click on the last name of the entry, and it gets inserted to a temporary holding table where the user can add details of a training record (trainer, date of training, notes) before approving the training as complete and inserting the records into our table of training records.

The temporary holding table is then displayed to the right in another subform. The parent form contains controls to capture the additional data for the permanent training record (trainer, date, notes) and if populated, those controls are inserted to populate the appropriate temporary holding table fields. The second subform displaying the holding table has similar behavior to the first--you can remove someone from the list of records to be inserted by double-clicking their entry on the form, and they are then "moved" back to the other subform.

I'm quite proud of how the whole thing works for the most part--there's some black magic that I don't totally understand being used in the insertions and deletions that some folks here have tried to help parse (though no one quite seems to think it should work... I don't either, but it functions flawlessly as far as I can tell).

That said, I would like the end user to be able to manually enter custom names on the right subform as well--if the logic that selects the suggested training requirements malfunctions somehow, or we want to document trainings given to someone that are outside the scope of their typical job duties, the user should be able to type in new entries directly on the second subform.

The controls used on the second subform use DLookups to display employee names and SOP numbers correctly. Of course, an unbound textbox control that pulls in the employee names from their IDs doesn't allow for custom entries. Is there a way to preserve the DLookup logic for the "existing" entries in the holding table (ie, those ones inserted by the doubleclick interaction) while leaving a blank row that can be populated manually?

Have contemplated some convoluted conditional formatting with two controls overlaid on each other, like, switching to displaying the control that has focus, waiting for user input, and then doing a post-update insertion of that data into the actual underlying recordsource and switching back to the DLookup control to display the data. Seems hokey.

Will I be better off just reconfiguring the DLookup fields into combo boxes instead?

 

[Ranman256]

I rarely use Dlookup. Esp in a combo box. The combo can have > 1 column , so instead of using dlookup , put the result wanted in the other column.
Set the combo.columns = 2 ( or more)
the user doesnt have to see them. columnwidths = 1;0

then when user picks the item, fill in another box with the other column. FASTER than dlookup.

NOTE: in vba, column #'s begin with zero

sub cboBox_afterupdate()
    txtBoxDescrip = cboBox.column(1)  'actually column 2
end sub

 

[Pat Hartman]

I've used the technique suggested by Ranman for things like comments. For example, there are a dozen or so standard comments but sometimes the user needs something custom so the combo is used to select a "standard" comment which is then placed in a bound textbox. Or, the user can just type something in the textbox. The record is not connected to the ID from the combo. Only the text is saved rather than more commonly the ID.

However, I don't like the technique for what you are doing. I don't have enough information to suggest an alternative. Perhaps you can post a picture of the form and a picture of the relevant tables that it uses.