Access 97 SQL recordset 1

[Dimmer]

This function is supposed to look through all the records in a table to see if the user's username is in the table, but is only looking at the first record.

Public Function CheckUser() as Integer
Dim dbs As Databse
Dim rdsLocker As recordset
Dim rdsDistMaster As recordset

Set dbs = CurrentDb
Set rdsLocker = dbs.OpenRecordset("Select Locker From tblLocker)
Set rdsDistMaster = dbs.OpenRecordset("Select DistMaster from tblDistMaster)

If fOSUserName = rdsLocker("Locker&quot THen CheckUSer = 2...

Only the first name in the table is recognized, and I can\t figure out why. Any help would be greatly appreciated.
Thanks

 

[jebry]

Hi!

You need to put the code in a loop. Try this:

Public Function CheckUser() as Integer

Dim dbs As Databse
Dim rdsLocker As recordset
Dim rdsDistMaster As recordset

Set dbs = CurrentDb
Set rdsLocker = dbs.OpenRecordset("Select Locker From tblLocker)
Set rdsDistMaster = dbs.OpenRecordset("Select DistMaster from tblDistMaster)

If rdsLocker.EOF And rdsLocker.BOF Then
Else
rdsLocker.MoveFirst
Do While Not rdsLocker.EOF
If fOSUserName = rdsLocker("Locker&quot THen CheckUSer = 2...
The rest of your code then Exit Do
Loop
End If
More code if you have any

This will go through all of the records and exit the loop when the name is found.

hth
Jeff Bridgham

 

[SmokingCrater]

Dimmer,

Try this instead:

public function checkUser() as integer
dim dbs as Database
dim rdsLocker as recordset
dim rdsdistmaster as recordset

set dbs = currentdb
set rdslocker = dbs.openrecordset("Select * from tblLocker where Locker = '" & fOSUserName & "'&quot
set rdsDistMaster = dbs.openRecordset("Select * from tblDistMaster where ....(rest of query)&quot

if rdsLocker.recordcount > 0 then checkUser = 2 ...

****************************

When you use rdsLocker("Locker&quot, it will return the value of the record found in the "Locker" column. (i'm guessing a bit here but based on what I have done and what you are saying I believe this is true). Since you never move the pointer in the rdsLocker recordset, it will only look at the first record.

Solution: When you create the rdsLocker recordset, make the query have a WHERE clause to return only records that pertain to the certain user. That way, if the resulting recordset has more than zero records returned, that means there is something valid in the recordset meaning something satisfied the WHERE clause. (i'm assuming here that the username is unique)

Hope that helps.