Setting Variable via Query

[BSSME]

Hi All,

I want to set a public (global) variable via a query in VBA for Access. I have an idea like this:

 Dim SomeNum as Integer

SomeNum = "Select sometable.field from sometable where sometable.field=Form!frmform!field

I think I'm close, but something is missing or I may be way off. Any help would be greatly appreciated.

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BSSME]

I checked this out but now I get a "previous action cancelled" error.

Private Sub Form_Open(Cancel As Integer)

Dim SecLev As Integer
cboSombox.Requery

SecLev = DLookup("[SecurityLevel]", "tblUsers", "[UserN]=" _
& Forms!frmLogin!txtUser)


End Sub

Any idea what I'm doing wrong here?

 

[PHV]

If UserN is not defined as numeric in tblUsers:
SecLev = DLookup("[SecurityLevel]", "tblUsers", "[UserN][tt]=[!]'[/!]"[/tt] _
& Forms!frmLogin!txtUser [tt][!]& "'"[/!])[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BSSME]

I tried the edits as suggested however, I'm still getting an Out of Context value on the variable.

What I need is to reference a table using the user name txt box to select the appropriate record from the table. Then I want the variable to be set to the SecurityLevel which is any number from 1 - 10 for that user.

Thanks for your help thus far!

 

[PHV]

Is Forms!frmLogin!txtUser already populated when you call the DLookUp function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BSSME]

Yes, it is already populated.