Assign Label Caption Based On Function Variable Run Through A SQL Statment

[Randomblink]

Ok...

This is hard to explain...

I have two tables and one form I am using here.

Table #1: Employee Table
Table #2: Department Table

The Employee table is linked to the Department table by a Dept_ID field that is in both tables.

I have a function: GetNTUser() that grabs the NT Logon ID.

What I want to do, is as the user opens the form.
The form runs the GetNTUser() function.
Then it checks that ID against the Employee table.
It will find the Employee who matches up to the NT Logon.
Then it grabs the Dept_ID that that Employee has.
It then checks the Department table.
It finds the Department with that Dept_ID.
It assigns the label caption of choice to be that Department Name.

Here is the SQL code that finds the record containing the right employee and therefore the correct department.

SELECT tbl_Employee.Empl_NTLogon, tbl_Department.Dept_Name FROM tbl_Department INNER JOIN tbl_Employee ON tbl_Department.Dept_ID = tbl_Employee.Dept_ID WHERE (((tbl_Employee.Empl_NTLogon)=GetNTUser()));"

But, I cannot seem to run this code and then assign the tbl_Department.Dept_Name to the label.caption...?

Can someone help?

I can create a listbox that has the above SQL as it's code.
Then set it up with two columns.
Put the Dept_Name as Column(2).
Bind Column(2) to the listbox.
Then set the lbl_DepartmentName.Cation = listbox

But that is a massive workaround...
I am sure there is code that will do the same thing...
I NEED that code...

 

[Travis]

Use "DLOOKUP"

Public Function GetUserDepartment(sUser As String)
Dim sDept As String
sDept = DLookup("[DEPT_ID]", "tbl_Employee", "[Empl_NTLogon]='" & sUser & "'")
GetUserDepartment = DLookup("[DEPT_NAME]", "tbl_Department", "[DEPT_ID]='" & sDept & "'")

End Function

 

[The_Doc_Man]

There are several ways to do this. The easiest way that comes to mind is to CHEAT LIKE HELL. Never mind captions, on a form they are just text anyway. ('tain't quite so simple in tables and queries, however.)

Find your label that you want to change. Delete it. Replace it with an unlabeled text box of the same size, unbound, and with the same attributes (forecolor, backcolor, border color, font, style issues, etc.) as any other label on that form. Name the text box DeptName or something equally prosaic. (You can do the rename by opening the Properties of the text box and find its Name property.)

Now, in the Form_Current routine, put some code to do a DLookup or two...

Private Sub Form_Current()

Dim stEmpl as String
Dim loDeptID as Long

stEmpl = GetNTUser()

loDeptID = DLookup( "[Dept_ID]", tbl_Employee, "[Employee_NTLogin] = """ & stEmpl & """ )

[DeptName] = DLookup( "[Dept_Name]", "tbl_Department", "[Dept_ID] = " & CStr( loDeptID) )

End Sub

The above presumes that Dept_ID is stored as a number. If not, then the loDeptID might need to be a string, not a long, and the syntax for the second lookup needs extra quotes.

The above ALSO presumes that you never get a failure in either lookup. You would put a test after the GetNTUser call and the DLookup calls to assure that you got something reasonable. And if you didn't, you would add logic to put in some default value or even force the closure of the form.

You use Form_Current event because that is reevaluated when you move to a new record. Form_Open would not update if you changed records.

 

[Randomblink]

THANK YOU SO MUCH!

Private Sub Form_Current()
GetUserDepartment GetNTUser
End Sub

Private Function GetUserDepartment(sUser As String)
Dim sDept As Integer
sDept = DLookup("[DEPT_ID]", "tbl_Employee", "[Empl_NTLogon]='" & sUser & "'")
GetUserDepartment = DLookup("[DEPT_NAME]", "tbl_Department", "[DEPT_ID]=" & sDept & "")
lbl_EmployeeDepartment.Caption = GetUserDepartment
End Function

I had to tweak it just a little...
But this is what I got...
And it freaking works...

I thought I was stuck with SQL to tweak this and I was going cross-eyed! Thanks so much! You are the greatest...

I completely appreciate this!
It might not look like alot to others, but this has to shine and this will make it so...!!! Thanks!

 

[David R]

Keep in mind that doing this once is efficient; doing it 100 times is not

DLookup is notoriously slow for repeated applications within a query or report. Because it has to look through EVERY record EVERY time it is invoked, it will slow things down significantly.

I couldn't tell from your question whether this was something you're doing once in the report header, or in every detail record. If it's in every record, consider linking within the underlying query to the table you're Dlooking up to currently, and pulling in the data that way.

My apologies if I misunderstood the problem.

 

[The_Doc_Man]

David R's suggestion is true - unless you put an index on the fields that will participate in the lookup.

Access will optimize the DLookup to use the index if there is one that matches the field you are looking up.

 

[Randomblink]

Index

Thanks David R,

I am using this merely for looks.
It is on a request form.
A user opens the form to request information.
They fill-in-the-blanks...
ANd the Department information is just a label at the top so they know that they are on the right computer.

Thanks alot!

It's too bad there isn't somewhere to showcase databases you are proud of...<chuckle>

This baby and the backend it is linked to will be freaking beautiful once I am done.

Later