Getting User Name from a User Table

[Christopher Hickman via AccessMonster.com]

I have a database with three tables. One table has my user information
(User) with a primary key called UserID. Another table has inventory
information (Inventory) with a primary key called EquipID. Another table
has information on which users are assigned which items of inventory
(Assignments) with no primary key.

The Assignment table has four fields: EquipID, UserID, IssueDate, and
ReturnDate.

In a query I need to get the field [Display Name] from the User table for
the user that is assigned a given EquipID. The only way I figured out how
to do it was with code. It works, but it is slow. Is there a simple way
to just query for it?

Here is the code I use now:

Function UserAssignedToEquipment(theEquipID) As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim strOut As String

strOut = ""
Set rst = CurrentDb.OpenRecordset("SELECT [Display Name] FROM Users WHERE
UserID = (SELECT UserID FROM Assignments WHERE IssueDate IS NOT Null AND
ReturnDate IS NULL AND [EquipID]='" & theEquipID & "')")
Set fld = rst("[Display Name]")
If Not rst.RecordCount = 0 Then
If Not IsNull(fld) Then
strOut = fld
End If
End If
rst.Close
Set rst = Nothing
UserAssignedToEquipment = strOut
End Function

 

[[MVP] S.Clark]

It's slow because you have a string based Query with Sub Query. Access has
no idea how to deal with it, so it has to make a plan prior to running...
EVERY time.

Since I don't know the bigger picture of why you are using code in this
fashion, I can't really comment on much more.

 

[Christopher Hickman via AccessMonster.com]

I figured it out. I had to just make a subquery that linked the assigned
equipment to users' names, and then reference it based on the EquipID.

Here is the SQL I used, for reference for anybody else trying to accomplish
this:

UsersWithAssignedEquipment:
SELECT Assignments.EquipID, Assignments.UserID, Users.[Display name]
FROM Assignments INNER JOIN Users ON Assignments.UserID = Users.UserID
WHERE (((Assignments.IssueDate) Is Not Null) AND ((Assignments.ReturnDate)
Is Null));

Control Source of My Form:
SELECT Inventory.EquipID, Inventory.[BB-Phone#],
UsersWithAssignedEquipment.[Display name] AS UserName FROM
UsersWithAssignedEquipment INNER JOIN Inventory ON
UsersWithAssignedEquipment.EquipID=Inventory.EquipID WHERE ((
(Inventory.EquipID) Like "B-*") AND ((Inventory.[BB-Phone#])<>"")) ORDER BY
UsersWithAssignedEquipment.[Display name];