SQL SELECT in an Event Procedure

N

Navatin

I have a procedure I am trying to write withing a "On Click" from a form
button. In this, I would like to select a value from a record in a table,
compare that value to a value that a user entered, then have a IF/THEN based
on the comparison.

Any suggestions?
 
T

tina

Dim var As Variant

var = DLookUp("value", "table")

If var = Me!ControlName Then
' do something
Else
' do something else
End If

hth
 
N

Navatin

Thanks Tina for responding so quickly... I have tried this, and I keep
getting an error that says "You have canceled the previous operation".
Here is my code:
----------start-------------------------
Private Sub open_main_menu_form_Click()
On Error GoTo Err_open_main_menu_form_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stUserName As String
Dim inGroupId As Variant

stUserName = Me![user-name-box]
inGroupId = DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= " & stUserName)

If inGroupId = 1 Then
DoCmd.Close
stDocName = "main-menu-all"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf inGroupId = 2 Then
DoCmd.Close
stDocName = "main-menu-user"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.Close
stDocName = "password-fail-error"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_open_main_menu_form_Click:
Exit Sub

Err_open_main_menu_form_Click:
MsgBox Err.Description
Resume Exit_open_main_menu_form_Click

End Sub
--------------end------------------
 
T

tina

first, since you know what data type the [sysu-user-group] field is, suggest
you change the inGroupId variable's data type to match.

second, since stUserName is a String data type, then presumably the
[sysu-user-name] is a Text data type, so you'll need to correct the syntax
in the DLookup() function, as

DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= '" & stUserName & "'")

third, since you never set the value of stLinkCriteria in the procedure,
there's no point in including it in the OpenForm method, so suggest you
remove it from there and from the variable declarations.

hth


Thanks Tina for responding so quickly... I have tried this, and I keep
getting an error that says "You have canceled the previous operation".
Here is my code:
----------start-------------------------
Private Sub open_main_menu_form_Click()
On Error GoTo Err_open_main_menu_form_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stUserName As String
Dim inGroupId As Variant

stUserName = Me![user-name-box]
inGroupId = DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= " & stUserName)

If inGroupId = 1 Then
DoCmd.Close
stDocName = "main-menu-all"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf inGroupId = 2 Then
DoCmd.Close
stDocName = "main-menu-user"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.Close
stDocName = "password-fail-error"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_open_main_menu_form_Click:
Exit Sub

Err_open_main_menu_form_Click:
MsgBox Err.Description
Resume Exit_open_main_menu_form_Click

End Sub
--------------end------------------

tina said:
Dim var As Variant

var = DLookUp("value", "table")

If var = Me!ControlName Then
' do something
Else
' do something else
End If

hth
Click to expand...
Click to expand...
 
N

Navatin

Tina...

You are a blessing... Thanks for all your help with this. It worked
perfect.

Navatin



tina said:
first, since you know what data type the [sysu-user-group] field is, suggest
you change the inGroupId variable's data type to match.

second, since stUserName is a String data type, then presumably the
[sysu-user-name] is a Text data type, so you'll need to correct the syntax
in the DLookup() function, as

DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= '" & stUserName & "'")

third, since you never set the value of stLinkCriteria in the procedure,
there's no point in including it in the OpenForm method, so suggest you
remove it from there and from the variable declarations.

hth


Thanks Tina for responding so quickly... I have tried this, and I keep
getting an error that says "You have canceled the previous operation".
Here is my code:
----------start-------------------------
Private Sub open_main_menu_form_Click()
On Error GoTo Err_open_main_menu_form_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stUserName As String
Dim inGroupId As Variant

stUserName = Me![user-name-box]
inGroupId = DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= " & stUserName)

If inGroupId = 1 Then
DoCmd.Close
stDocName = "main-menu-all"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf inGroupId = 2 Then
DoCmd.Close
stDocName = "main-menu-user"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.Close
stDocName = "password-fail-error"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_open_main_menu_form_Click:
Exit Sub

Err_open_main_menu_form_Click:
MsgBox Err.Description
Resume Exit_open_main_menu_form_Click

End Sub
--------------end------------------

tina said:
Dim var As Variant

var = DLookUp("value", "table")

If var = Me!ControlName Then
' do something
Else
' do something else
End If

hth


<Navatin> wrote in message
I have a procedure I am trying to write withing a "On Click" from a form
button. In this, I would like to select a value from a record in a
table,
compare that value to a value that a user entered, then have a IF/THEN
based
on the comparison.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
T

tina

you're very welcome :)


Tina...

You are a blessing... Thanks for all your help with this. It worked
perfect.

Navatin



tina said:
first, since you know what data type the [sysu-user-group] field is, suggest
you change the inGroupId variable's data type to match.

second, since stUserName is a String data type, then presumably the
[sysu-user-name] is a Text data type, so you'll need to correct the syntax
in the DLookup() function, as

DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= '" & stUserName & "'")

third, since you never set the value of stLinkCriteria in the procedure,
there's no point in including it in the OpenForm method, so suggest you
remove it from there and from the variable declarations.

hth


Thanks Tina for responding so quickly... I have tried this, and I keep
getting an error that says "You have canceled the previous operation".
Here is my code:
----------start-------------------------
Private Sub open_main_menu_form_Click()
On Error GoTo Err_open_main_menu_form_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim stUserName As String
Dim inGroupId As Variant

stUserName = Me![user-name-box]
inGroupId = DLookup("[sysu-user-group]", "sys-users", "[sysu-user-name]
= " & stUserName)

If inGroupId = 1 Then
DoCmd.Close
stDocName = "main-menu-all"
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf inGroupId = 2 Then
DoCmd.Close
stDocName = "main-menu-user"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.Close
stDocName = "password-fail-error"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_open_main_menu_form_Click:
Exit Sub

Err_open_main_menu_form_Click:
MsgBox Err.Description
Resume Exit_open_main_menu_form_Click

End Sub
--------------end------------------

:

Dim var As Variant

var = DLookUp("value", "table")

If var = Me!ControlName Then
' do something
Else
' do something else
End If

hth


<Navatin> wrote in message
I have a procedure I am trying to write withing a "On Click" from
Click to expand...
a
form
button. In this, I would like to select a value from a record in a
table,
compare that value to a value that a user entered, then have a IF/THEN
based
on the comparison.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Top