Passing Data from an Access table to a Variable in VB code

[rc51wv]

I need to pass the Sec_Level_ID Feild from an Access table to the
Sec_level_ID int variable in the code and I need it to work like this.

The User enters their name and password on the login screen. If it's
correct, the code needs to search table AppUsers for the User's name and pass
the Sec_Level_ID from the table to the global variable in the code so It can
be used to tell what tables the user can view.

What's the easiest way to go about passing the variable from the table to
the code?

 

[Ofer]

You can use DLookUp

VariableName = DLookUp("Sec_Level_ID","AppUsers","[UserNameFieldName]='" &
Me.[UserNameFieldNameInTheForm] & "'")

 

[George Nicholson]

Assuming Sec_Level_ID and UserName are fields in table "User":

glngSecID = nz(Dlookup("[Sec_Level_ID]","Users","[UserName] = '" &
strLoginName & '"),0)

This will set glngSecID to 0 (rather than "Invalid use of Null") if Users
does not contain the specified name.

HTH,

 

[rc51wv]

I tried both of the ways described and it keeps coming up with Run-time error
2467, The expression you entered refers to an object that is closed or
doesn't exist.

What part of the statement is it talking about. This is what the code looks
like.

--->not code
glngSecID = variable to be passed to
Sec_Level_ID = Field in table that is passed to glngSecID
AppUsers = table name
User_Name = field in table that is compared to login name
TxtUsername = variable for login name in the code to match to the field
User_Name
--->not code

glngSecID = Nz(DLookup("[Sec_Level_ID]", "AppUsers", "[User_Name] = '" &
TxtUsername & "'"), 0)

Assuming Sec_Level_ID and UserName are fields in table "User":

glngSecID = nz(Dlookup("[Sec_Level_ID]","Users","[UserName] = '" &
strLoginName & '"),0)

This will set glngSecID to 0 (rather than "Invalid use of Null") if Users
does not contain the specified name.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I need to pass the Sec_Level_ID Feild from an Access table to the
Sec_level_ID int variable in the code and I need it to work like this.

The User enters their name and password on the login screen. If it's
correct, the code needs to search table AppUsers for the User's name and
pass
the Sec_Level_ID from the table to the global variable in the code so It
can
be used to tell what tables the user can view.

What's the easiest way to go about passing the variable from the table to
the code?

 

[John Welch]

Are you trying to reproduce the User Level Security feature of Access? It is
a bit tricky to set up User Level Security just right, but it might be less
work and will definitely be more secure than what you are doing, and it
might be a good learning experience. You might consider it. Look in google
for "securing a database."
-John

 

[rc51wv]

I was told to set it up this way so I'm following orders.

 

[George Nicholson]

I don't see why *that* line of code would cause that error unless you don't
have a table called AppUsers. (If the issue was a field, the message would
be "can't find field..." not "object doesn't exist").

Are you absolutely sure the error is being triggered by that particular
line???

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I tried both of the ways described and it keeps coming up with Run-time
error
2467, The expression you entered refers to an object that is closed or
doesn't exist.

What part of the statement is it talking about. This is what the code
looks
like.

--->not code
glngSecID = variable to be passed to
Sec_Level_ID = Field in table that is passed to glngSecID
AppUsers = table name
User_Name = field in table that is compared to login name
TxtUsername = variable for login name in the code to match to the field
User_Name
--->not code

glngSecID = Nz(DLookup("[Sec_Level_ID]", "AppUsers", "[User_Name] = '" &
TxtUsername & "'"), 0)

Assuming Sec_Level_ID and UserName are fields in table "User":

glngSecID = nz(Dlookup("[Sec_Level_ID]","Users","[UserName] = '" &
strLoginName & '"),0)

This will set glngSecID to 0 (rather than "Invalid use of Null") if Users
does not contain the specified name.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I need to pass the Sec_Level_ID Feild from an Access table to the
Sec_level_ID int variable in the code and I need it to work like this.

The User enters their name and password on the login screen. If it's
correct, the code needs to search table AppUsers for the User's name
and
pass
the Sec_Level_ID from the table to the global variable in the code so
It
can
be used to tell what tables the user can view.

What's the easiest way to go about passing the variable from the table
to
the code?