How to "elegantly" convert values from a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Let's say I have one table with two fields. The first field is "user" and
the second field is "view" (the table's name is "tblFolks"). If I have one
value that is coming from a module, how do I convert that value from the one
field into another (my module is returning the value "view", but I want the
value "folks" from that record so I can later compare the "view" value to a
form view). Do I have to create a query to do this? If so, what would the
code look like? For that matter, if not, how would the code look? Thank you
in advance!
 
I'm not tracking...

You appear to want to make a value in one field dependent on the value in
another field in the same table. If so, this is not a well-normalized
design. You will not get the best use of Access' features and functions if
you don't provide it data structured as it is designed to receive.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Let me try explaining it this way:

I have a string value (a user name) that is returned from a module in my
database. This value needs to be compared against a table that holds unique
user names in one field and their permissions level in another in each record:

Name___|PermLev_
Bob_____|Edit_____
Mike____|Read____
Phil_____|Edit_____
Sue_____|Read____

I would like to use VBA (and I'm assuming a SQL statement) to convert the
name over to the permission level.

I'm thinking it should look along the lines of:

Dim strReturnedName as String
Dim blnAllow as Boolean

strReturnedName = Module.mdlMODNAME.Modvalue

<VBA / SQL Statement that converts the name in the table to the permission
level and returns value Permis>

blnAllow = Permis <> "Edit"
With Me
.AllowEdits = blnAllow
.AllowDeletions = blnAllow
.AllowAdditions = blnAllow
End With



Is this a little more clear as to my intent?
 
I'm probably hung up on the "convert the name over to the permission"
aspect.

If you already have a name associated with a permission, why do you feel you
need to "convert" the name? Would it be sufficient to "look up the
permission"?

I could imagine a query that returns "permission" based on a selection
criterion for the name field that is the name returned by the module.

By the way, using the term "Name" as the name of a field (or any other
object in Access) is risky. "Name" is a reserved word in Access, so what it
thinks you mean and what you think you mean may not be what "name" means!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Okay, to keep things simple, let's use your syntax of "look up" instead of
convert (which I'm using incorrectly as a novice in the world of coding). In
that case, how would I look up the value in the table based on the value of
my name field (which is actually named "NTUserID", not "name").
 
Please re-read my last response. I mentioned using a query to look up that
value.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
How about the DLookUp method:

perm = DLookUp("PermLev","tblFolks","NTUserID='" & strReturnedName &
"'")

Carl Rapson
 
Back
Top