Assign and call variable from table

[smikkelsen]

I am self taught and not very good with the correct terminology etc. so forgive me if I sound stupid with my question



I have a query with two columns (user, and username) This is only going to have 1 record in it. I am using it for credentials to prevent users from seeing eachothers records.

So, I have main table with all records in it. Each one assigned to a user. I have a form that shows all of those records, but I want it to be filtered based on the query that shows the current user in it. I did do this which worked wonderfully by just adding the user/username query to the form for filtering, but I found that the records are not editable in the form because of that.

So, what I would like to try and do is somehow save the contents of column 1 record 1 (in the query) as a variable so that In the form I can just put "var" in the criteria.

So, I am not sure if this is possible to do this, but I am thinking it shouldn't be too hard.

Can this be done in VB by doing an on open event in the form that will filter, then requery or something like that?

Thanks!

[pbaldy]

How does the query filter to the specific user? Presuming it gets a value from a login form or something, you could base your form on a query that got a criteria from the same place.

[smikkelsen]

It actually pulls the username from the network login with a function. So I have a table that has user and username.

I type user and username like (Joe, joeusername)
Then the query uses the function in the critera to filter only the record that matches the network login so you are left with the one record (Joe, joeusername) I would just use the same function to filter the form records, but those records are listed as "joe" not "joeusername"

I hope that makes sense.

Here is the username function i'm using. It may be of use for someone, who knows.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

[pbaldy]

That's a pretty commonly used function. Well, there are several ways to do this. I'd probably do a lookup and get the user associated with the username and store it on a form that will stay open (could be hidden). Then in queries and such you can simply use that form control in the criteria.

[smikkelsen]

As you can tell, I am still somewhat limited in my scripting to taking and customizing code that already exists. I wouldn't know where to begin with the lookup thing you suggested, although it sounds like it might be a great place for me to start. Do you know where I may read about it?

Also, just so I understand correctly, could I do something onload of the form that will do a lookup and filter the results? or are you suggesting to do a lookup and store as a variable for later use when the database is opened? The latter sounds "cleaner" to me, but again, unsure on how to do this.

Thanks again so much for all the help. I hope I am not a bother.

[smikkelsen]

I'm sorry, that was actually a bit easier than I imagined. I think you are talking about dlookup ?? If so, I was able to do this:

in criteria of the form query

DLookUp("[User]","Form user filter")

when I view the query after filtering this, it shows only the records that I want and would expect. however, when I save and close, then open the form, it shows zero records. Any ideas? or is something wrong all together with the way I have tried to filter?

Thanks again.

[smikkelsen]

Edit:
Found the problem (it was unrelated)
Sorry, Jumped the gun..
Thanks for helping me again, this is the second time today. I really appreciate your time!

[pbaldy]

All this while I slept!

Happy to help; glad you got it sorted out.