Help with process

[Indigo]

I am running Access 2003 and I am trying to figure out how to get this to work and would appreciate another set of eyes. I have a Holding form that runs (not visible) in the background of my database. When the form opens I want a Shift Textbox to be populated with the shift for the user logging in based on that users details in a User table. The user table has the login name and shift. I am using the following:

Sub GetAMShift()
    Dim dbObject As DAO.Database
    Dim GAMRS As DAO.Recordset
    Dim HoldAMShift As String
    Dim strQuery As String
        Set dbObject = CurrentDb
        strQuery = "SELECT * FROM Users;"
        Set GAMRS = dbObject.OpenRecordset(strQuery)
        
            With GAMRS
                .MoveFirst
                    Do While Not .EOF
                    HoldAMShift = .Fields("Shift").Value
                .MoveNext
                Loop
            End With
            
        Forms![HoldingInfo].AMShift.Value = HoldAMShift
End Sub

There are about 60 user records in the database and I need to loop through them to get the user ID and match the shift information. Can someone help me out?

 

[vbaInet]

The DLookup() function might be what you need? Is shift a single field or a set of records? Can a user have more than one shift?

 

[Indigo]

That works.... thanks!!

 

[vbaInet]

You're welcome.