Updating unbound field based on another unbound field

[irunergoiam]

Okay... So, I'm pretty comfortable with using the After Update and On Change events for a combo box to populate unbound fields on a form.

The problem I'm now having is that when one of the unbound fields (unbEmployeeNo) on the form (that gets updated via the After Update or On Change events) has a value, I'm not quite sure how to get another unbound field (unbPosition1) further down on the form (and not part of the combo box SQL statement record source) to populate. I have a query that I'd like to use to bring data in to this field (with criteria based on the form name and EmployeeNo) so that where the [Forms]![frmNewHireLegalNameVerification]![unbEmployeeNo] criteria for the (unbPosition1) query is satisfied the unbPosition1 field on the form populates.

I don't know if any of this makes any sense...and am more than willing to clarify for anyone out there with ideas as to how I might go about this. Thanks much...

 

[irunergoiam]

Never mind. I'm going to try a list box. Wish me luck...

 

[The_Doc_Man]

Both list boxes and combo boxes carry "baggage" when you use them.

I use this instead:

[mycontrol].Text = DLookup("[NeededField]", "sourcequery", "WHERE-clause without the WHERE")

or

[mycontrol] = [othercontrol] & " - " & DLookup("[specialfield]","specialquery","selection criteria")

You get the picture, I hope. Or was your question not about the formula but rather about the event?

When you have unbound text boxes, they are trickier because you cannot so easily tell if they are are clean or dirty. (No .OLDVALUE property, for one thing.) You can try this:

Build a variable that is private within the form's class-module, i.e. its declaration area.

In this scenario you have TWO fields - the one that gets changed (independent field) and the one you want to change depending on the first field (dependent field). If both are unbound, I believe the AfterUpdate event won't fire but perhaps the OnChange event still works. If not, you could put some code in a subroutine that checks the source field for changes that would affect the destination field. (Again, in the form's class module.) Just call that subroutine from the LostFocus event of any field that could trigger the update directly or indirectly. Use the global variable to hold the .TEXT associated with the independent field. Inside your new subroutine, see if the independent field changed. If it did not, then just exit. Otherwise, update your dependent field.