I have a query made that has all open calls by UserTID. If this were in a form I would open the Query and in the QBE grid I would right something like this to filter the records. Forms.FrmUsers.UserTID. This would allow me to filter the records in my query by the UserTID. However, in my case I dont have a loaded form so I cant use that filter. What I tried to do is UserTID = [TblDealerRewardsConnectData].[UserTID] = " & Me.UserTID"
It doesnt work.
Any suggestions would be appreciated.

Thanks,

Chris

You have to get the UserID from somewhere. If its not from an open form, then it needs to be either a public variable or a prompted value.

Chris,

I've got to do a little guessing as to what you really want, but see if this makes sense:

In your QBE grid, there's a row called criteria. In it, type something like [Enter the User TID]. I say "something like" because the brackets are required, but the prompt itself can be anything that makes sense to the user.

When the query is run, it will prompt the user to enter the user TID and it will "filter" in that way.

Does this make sense? Does it sound like what you (more or less) want?

Kerry

Ok my query has a list of records and one of the fields is UserID.
I want to filter those records somehow based on what User is currently logged in.
The user would log in and see only those calls associated with his UserID.

Because the security form closes after the User logs in I dont have a related form open to filter by my normal method.
So, how do I link or filter related records?
You say I need to make a public variable or a prompted value?
Would I achieve it using the below method? I just need to match the UserID in my query with the UserID from the User Login/logout table.

Any links to other posts explaining this would be appreciated.

That is close but I want in auotmated in that when the User logs in and accesses the form, the form filters only records associated with users UserID. The problem is that my login form closes after the user logs in. so how do I get filtered records for the user? There must be other means to do what Im attempting.

Thanks,

What type of security are you using? If its Access's built in security, you can probably get the current user ID using the CurrentUser() function. If its something different, then you need to capture it some way.

I am not using Access built in security. I have a login and password form that when the user logs in the data is captured and input into a table that logs the entry and exit time of the user. I was thinking of somehow grabbing the UserID from here. However the pk is SecurityID and UserID are unique and in the same table.

Ooops, I posted a reply but I used quick send.

Don't close the form. Hide it instead. That way it remains open while the app is open and you can reference the UserID when needed.

The problem with that is that UserID is equal to the control name txtUser and is tied to the code below.

Im thinking that with some modification I can cut out some of what I dont need here and put this in the click event that brings up the "Call In" form that each CSR looks at. As you can see there is some code where it links the data that I need. however the switchboard form doenst have any related data on it.

At this point im guessing and not sure where to go.

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstV As Recordset
Dim stDocName As String
Dim stLinkCriteria As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)

If Not IsNull(Me.txtUser) And Not IsNull(Me.txtPassword) Then
rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserTID = '" & Me.txtUser & "'"

If rst.NoMatch Then
MsgBox "You entered the wrong User TID or Password." & Chr(13) & _
"Please enter the correct User TID and Password or " & Chr(13) & _
"contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
ElseIf Me.txtPassword = "password" Then
MsgBox "This is the first time using the database or your passowrd has been reset." & Chr(13) & _
"You must change your password before you can enter the database.", _
vbOKOnly + vbExclamation, "Change Password"
stDocName = "frmUserLogonNew"
stLinkCriteria = "[UserTID]=" & "'" & Me![txtUser] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
stDocName = "frmStartUp"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
MsgBox "You left the User TID and/or Password blank." & Chr(13) & _
"Please enter the correct User TID and Password or " & Chr(13) & _
"contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
End If

With User
.AccessID = rst.Fields("AccessID")
.ViewID = rst.Fields("ViewID")
.Active = rst.Fields("Active")
.Password = rst.Fields("Password")
.SecurityID = rst.Fields("SecurityID")
.UserTID = rst.Fields("UserTID")
End With

rst.Close

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub

This doesn't matter. You need to store the userID somehow. The easiest thing to do is NOT close the form (and you aren't doing it in that code), but hide it. Then you can ise

=Forms!formname!txtUser

to reference the user id

The problem with your proposal is that txtUser is unbound and the logon form is not tied to a table, which is why the filter you mention wont work. This is why I was attempting to look in the Security Log table and use the value from there.
I think Im just going to make a small form with on field for the UserID and then minimize this and use this small form as the filter or criteria if you will.

Thanks for your help,
I live and learn

That doesn't matter. As long as the data is entered into the control and the form is left open it will retain the value. I use unbound forms to filter queries all the time.

Ok I stand corrected. Earlier before I started this conversation I had attempted what you had mentioned but without sucess. I thought for sure it would work even if the control was unbound. However, it did not work and I was trying to think of another solution so I wouldnt have to have my logon form visible. I was trying to think of some cute way to extract the user logon name from the logon table and insert this as the query parameter or filter in the query.
Anyway I abandoned all my attempts and started from the beginning with my first assumption and your suggestion to just leave the logon form open and reference the unbound control. I went into the query QBE and inserted [Forms].[FrmUserLogon].[txUser] and again it didnt work. So, Im thinking HA it wont work! But after further review I noticed that I mispelled the control >.< it is TXTUSER!!! WHAT A DUMMY!!!

Thanks Scott for pushing me, what a dumb mistake!

Sometimes it just requires a new pair of eyes.