Update Label Caption Based On Query Result

[David Layzell]

Good Day,

I have an access database with an employees table. This table has
employee ids, first names and last names. I also have a form which includes
an employee ID field and a label. What I would like to do is use the
AfterUpdate event of the field to query the employees table for the first
and last names associated with the employee ID that has just been entered in
the field and use the results to update the label's caption. Can anyone
advise on how to go about this?

Thanks,

David Layzell

 

[Guest]

Why would you use labels which would require coding when just defining a text
box to display them takes no code?

 

[Allen Browne]

And why would anyone post the same question to 4 different newsgroups?

 

[Guest]

Because they have not read item 3 under "Asking questions the right way" at
this site:
http://www.mvps.org/access/netiquette.htm#msnews

 

[Guest]

Because they have not bothered to read item 3 under Asking questions the
right way" at this site:

http://www.mvps.org/access/netiquette.htm#msnews

 

[David Layzell]

It was 5 different newsgroups, actually, and I only got the answer I asked
for in one. If I had asked "the right way" I'd still be scratching my head.
Why am I doing it this way? Becuase it is essential for my project. (ie this
is not the ONLY place I will be using the same technique) That's why. Thanks
for asking. Wish I could thank you for the advice you offered...

 

[Guest]

You haven't received any advice, yet. My original response was to find out
why you want to do that. If it is a requirement, fine.

How you do it will depend on a couple of things. You say you have a form
with an employee id field. (It really isn't a field, it is a control. Fields
are in tables, forms have controls). What kind of control is it? A text
box, combo box, etc?
What is the record source for the form? Is it a bound or unbound form?

If you can provide this info, we can guide you in the right direction.

Perhaps the reason you got a response in only one group is because most of
us ignore multiple posters. It just happened I saw this one before I changed
rooms. Please understand this comment is only to help you. These newsgroups
are a great source of information, but you don't want to end up being ignored.

 

[David Layzell]

Hi,

Yes, the control is a text box, on an unbound form. I've managed to get
this working by using the 'AfterUpdate' event of this control. I wrote some
VB code using the DLookup function (of which I was previously unaware), to
retrieve the correct data. Here's the code:

Private Sub REP_FIELD_AfterUpdate()
Dim v_fname, v_lname
v_fname = Nz(DLookup("[FIRST_NAME]", "IQS_EMPLOYEE", "EMPLOYEE_ID ='" &
REP_FIELD.Value & "'"), "")
v_lname = Nz(DLookup("[LAST_NAME]", "IQS_EMPLOYEE", "EMPLOYEE_ID ='" &
REP_FIELD.Value & "'"), "")
REP_NAME_LAB.Caption = v_fname & " " & v_lname
End Sub

Which is working perfectly to requirements. Sorry for getting snippy. In all
the other newsgroups I posted to, I got "don't do it that way, do it this
way" type of answers, and I had mistaken your reply for one of those. Hence
how I only got one answer directed at my question. (ie, all other posts were
suggestions of alternate means).Thanks for your time, I'll be sure to keep
my posts in one group from now on.

Regards,

Dave