Using VBA to get data from a table

[David Trickett]

I have been writing Excel vba code for ages, but have recently inherited an Access database and am trying to get it under control!

Simplifying the issue the database contains a table called "People". This has 2 fields, Name and Status. Status can be "Master" or "Slave".

My code has already identified the name - call it CurrentUser.

I need to restrict access to a form opened by a button on another form to users with the status "Master"

So the logic would be:

Find CurrentUser in table("People")
If currentrecord.status <> "Master" then exit sub

(Excel programmers will see from the second line that I use the "Open" statement a lot!).

Thanks

David Trickett

 

[WayneRyan]

David,

' *******************************
Dim dbs As Database
Dim rst As RecordSet
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from People Where UserName = '" & _
CurrentUser & "'"
Set rst = dbs.OpenRecordSet(sql)
If rst.EOF And rst.BOF Then
MsgBox("No such user.")
Exit Sub
End If

If rst!UserType <> "Master" Then
MsgBox("No privileges.")
Exit Sub
End If

Set rst = Nothing
Set dbs = Nothing

'
' Open the form
'

Exit Sub

' **********************************

Note: Check the Single-quote Double-quote syntax in sql.
This is because the string you're building looks like:

Select * from People Where UserName = 'JOE';

hth,
Wayne

 

[David Trickett]

Thanks

Wayne

Many thanks - works a treat but I forgot to mention that the username can appear in the table twice, as master and slave. So I need to search further down the table if "Slave" is found.

It seems I need to learn a bit about sql - do you know of any good resources?

David

 

[WayneRyan]

David,

sql = "Select * from People Where UserName = '" & _
CurrentUser & "' and UserType = 'Master';"

The above will work, but with the previous code it will report
a user with Slave status as unknown.

sql = "Select * from People Where UserName = '" & _
CurrentUser & "' order by UserType;"

This will work because it will list Master first.

Try looking at the sample databases, experimenting and
reading the forums.

hth,
Wayne