group account used as query criteria

[Gwen]

Please help.

The query result depends on the group account(s) that the
user is member of.
Some users belong to more than one group
I think I am close but not quite there.
The record count is 1 when it should be 559.
What am I doing wrong?

Sub getuser()

Dim usr As User
Dim strusername As String
Dim varname As String
Dim strcriteria As String
Dim grpname As String
Dim strsql As String

Dim i As Integer
Dim somenum as Integer
Dim ws As Workspace
Dim db As Database
Dim rst As Recordset

Set ws = DBEngine.Workspaces(0)
strusername = CurrentUser

Set db = CurrentDb

Set usr = ws.Users(strusername)


varname = "(table.[somefield])"
strcriteria = "(table.[somefield])"
For i = 0 To usr.Groups.Count - 1
grpname = usr.Groups(i).Name

strcriteria = strcriteria & "= """ & grpname & """ or "
strcriteria = strcriteria & varname

Next i

strcriteria = Left$(strcriteria, Len(strcriteria) -
somenum)

strsql = "SELECT * from table"
strsql = strsql & " WHERE(" & strcriteria & ");"
Debug.Print strsql
Set rst = db.OpenRecordset(strsql)
Debug.Print rst.RecordCount

db.Close
Set db = Nothing

End Sub

 

[Kevin K. Sullivan]

I haven't been through all of your code, but you can't rely on .RecordCount
to be accurate until you've navigated the entire recordset.

I would suggest:
''''''''''''''
Set rst = db.OpenRecordset(strsql)
If rst.EOF and rst.BOF Then
'there are no records
Else
rst.MoveLast
Debug.Print rst.RecordCount
'more rst manipulation
End if
rst.Close
Set rst = Nothing
db.Close 'Don't do this -- you didn't open CurrentDb, it will be ignored
Set db = Nothing ' this is good

''''''''
If this change doesn't work, please post the SQL that is generated by your
routine.

HTH,

Kevin

 

[Alex Dybenko]

when it prints strsql - copy it into new query SQL window and check how many
rows you get there

also you can add:
Set rst = db.OpenRecordset(strsql)
if not rst.eof then
rst.movelast
rst.move first
Debug.Print rst.RecordCount
end if

 

[Gwen]

Thanks It worked
You guys are sweethearts