Dynamically Updating (Display) Unbound Label based on Input from Another Textbox

[shelzmike]

I hope this will make some sense.



I have a bound textbox called techNum in which the user can enter any 4 digit number that corresponds to one of our technicians numbers. I also have an unbound label called FSM, which will hold the name of their manager, but this label will need to be read only yet change based upon what the user enters in the techNum field.

I have two lookup tables that I am referencing here.

techTbl
[techNum],[techName],[fsm_Id],[longTechNum]

-AND-
fsmTbl
[fsm_id],[fsmName],[longFSMID]

So, on the form, when a user enters say, 2900, for the tech number I want that number to be looked up on the techTbl, find the fsm_Id, then return the fsmName associated with that fsm_Id on the fsmTbl. I want the returned fsmName to be posted in the aforementioned unbound label.

How can I do this? For some reason when I list it out like that it sounds more complicated that I think that it should.

Mike

[June7]

Label cannot be bound to data. You can programmatically set their Caption property.

Instead, you might just modify the form's RecordSource to include joins with techTbl and fsmTbl. Be careful to get the JoinType correct. Then you can display the name info in locked textboxes.

[shelzmike]

Thanks for the reply. Actually, it was much easier than that. I changed the control to a textbox and then used a DLookup function in the AfterUpdate event of the techNum textbox on the form.

This worked beautifully. However, now I have to figure out how to be sure that the TechName textbox is cleared of any data on each new subsequent record navigation or creation.

What I mean is that this process works as it should on an individual record; however, it retains the changed data when I move about the record set, making the others incorrect.

Any tips on that?

Mike

[June7]

Use the join in RecordSource or repeat the VBA code in the form OnCurrent event.

So which is easier - the join or figuring out everywhere need code to trigger the refresh of the textbox?

[shelzmike]

This may show my novice status in all its glory, but I do not see the OnCurrent event in the properties for events. Is this something that needs to be added in VBA explicitly? Also, I am guessing by your wording in the last response that using the join is easier, though I am not fully sure how to do that either! Thanks for the push in the right direction though> Thanks!

[June7]

OnCurrent is an event of the form, and it is the first one in the list. You must have the form selected, not a control. Click the form in the upper left corner or from the Properties dialog dropdown list click the Form item.

RecordSource for a form can be a table or a query. Query can include joins to other tables/queries. You can build the query and save it as an object and reference the query in the form RecordSource or build the query right in the RecordSource property. In the latter there will not be an Access query object saved, just an SQL statement in the RecordSource. Click the ellipses (...) to open the designer and build.

Those two events might be adequate if you want to go the VBA route. However, the join does not require any VBA.