Use Access Query To Populate Field Value

Good Day All,

I have an access database, with an "employees" table. This table contains employee IDs, first names and last names. I would like to use the form (screenshot attached below) to enter an employee id, and query the above table, using the result to populate a label (ie set the caption property). Thanks in advance to anyone who can offer any advice on how to do this.

Regards,

Milty

 

man, I haven't used access in years.. there should be an onTextChanged event for the textbox in red.. use this event to update the label in blue for each char entered in the text box.

 

Its just change

Whilst I'm not too bad with vba in excel, i've never used it in access so while I can do event code I have no idea how to query the database, i'd probably use ADO and do it as if the form wasn't actually part of the DB.

Personally when I have to do something like this I write an external application to do it, ADO.net makes this so easy now.

Sorry I can't help wit with the actual DB code but hopefully this helps a bit. The small lump of code below uses the change event of the textbox but I think you should actually use the exit event, I don't particularily like the idea of a database query each time the text changes. If you use the Exit event then the query will only happen once you leave the text box (by tabbing to the next control etc)

Code:

[COLOR=#0000FF]Private[/COLOR] [COLOR=#0000FF]Sub[/COLOR] txtEmployeeID_Change()
    [COLOR=#008800]' Check the format before looking up in DB[/COLOR]
    [COLOR=#008800]' Too Many Lookups on change could be horrible.[/COLOR]
    [COLOR=#008800]' you could use the exit event so that when the user[/COLOR]
    [COLOR=#008800]' tabs out of the box it then updates my employee id just happens to be numeric ;)[/COLOR]
    [COLOR=#0000FF]If[/COLOR] IsNumeric(txtEmployeeID.Text) [COLOR=#0000FF]Then[/COLOR]
        [COLOR=#008800]'do some database stuff.[/COLOR]
        lblEmployeeName.Caption = [COLOR=#FF0000]"blah blah blah"[/COLOR]
        
    [COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]If[/COLOR]
[COLOR=#0000FF]End[/COLOR] [COLOR=#0000FF]Sub[/COLOR]

 

Thanks for the tips guys, but I do know how to cause an event to be triggered. Its retrieving the data that's stumping me right now.

I am using the 'After Update' event of the text field to try to pass the results of a query to the label. I think that's probably the same amount of overhead as the exit event - maybe less; you can exit the control without making changes and NOT query the DB.

The thing is, I can't get the query to pass its results to a variable I can use to set the label's caption. Its somewhat frustrating, too, as I know it can be done, and am just waiting to stumble upon that magic combination of settings.

Let me know if you have any further suggestions! I'm all ears!

 

I got it! Below is the code that is doing the job for me:

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

The DLookup function allows me to lookup values of fields that aren't in the record source.

 

are the reported by and blue label the same values after they are populated?

 

No. The 'reported by' text field holds the employee id, and the label gets updated with the employee's name.

 

Just to be pedantic you should use & for string concat stuff not +

 

Not to be arguementative; I'm just curious - why? Code portability? The plus signs haven't caused any problems for me.......YET. What kind of problems would you anticipate with these?

 

Well I wish I hadn't said that now lol

+ is adition operator. & is concatation

What if your db lookup had returned an integer ?

1 & 1 <> 1 + 1

I guess its not vital lol, not sure why you should do it this way maybe its just a good habit

 

Ahh, that's a good point! Thanks for the bit of insight.
However, data type mismatches are easy to recover from, and I do usually use a cstr command just to be thorough....

Keep in mind you're not seeing the finished product yet!

 

wouldn't you rather not have to recover from a DTMM? and not have to hit the overhead of a cast? I'd suggest as well that you use & instead

 

Well yes, but that depends on the data returned by my query. I don't think I have to worry about it at all in this case; there's almost certainly datatype restrictions on this table in the first place. (It's an Oracle db) I'm going to check and confirm.

I don't know how I would effectively control this short of changing the table design (an idea which scares the bejeebers outta me with the tables I'm working with), and/or verifying all the data in that particular column. (Which would have to be done continuously if the table design wasn't changed - now THAT'S overhead!) I've resigned myself to the idea that DTMMs are a fact of life, and should be anticipated by a programmer. Hence my excessive casting! What else am I going to do?

I have since adopted the habit of using ampersands instead of plus signs when doing string manipulations in the event of working with a db that isn't so well organized.