VB code for an if statement and action

[Guest]

Does anyone know how to write a VB script to run different queries based upon the value that the user enters into a form?

Ex: If the user enters the number 1 in the form field, I want to run query#1
and if the user enters the number 2 in the form field, I want to run query#2.

I want to insert this VB code in the "ON ENTER" event of the field in the form.
I already tried to do this with a macro and logged a question under macros section but the nice person
that responded to my question indicated that I would not be able to handle this type of thing with a macro.
Unfortunately although I am very experienced with creating forms and programs in MS ACCESS, I am not
familiar with VB or SQL (never had to use it till now).

I am pulling my hair out trying to figure out how to just do this 1 thing. The rest of my program runs is done otherwise!
Thanks in advance for anyone's help on this.

 

[Guest]

You should use the AfterUpdate Event not the OnEnte

The OnEnter Event occurs before the GotFocus Event and therefore wouldn't be the correct one to use in your scenario


If Me!txtChoice = "1" the
DoCmd.OpenQuery "qry1
Elseif Me!txtChoice = "2" the
DoCmd.OpenQuery "qry2
Els
MsgBox "You have made an invalid choice, please re-enter",vbInformatio
End I

Nancy Welc
Programmer/Analys
King County IT

----- mfg2529 wrote: ----

Does anyone know how to write a VB script to run different queries based upon the value that the user enters into a form

Ex: If the user enters the number 1 in the form field, I want to run query#
and if the user enters the number 2 in the form field, I want to run query#2

I want to insert this VB code in the "ON ENTER" event of the field in the form
I already tried to do this with a macro and logged a question under macros section but the nice perso
that responded to my question indicated that I would not be able to handle this type of thing with a macro
Unfortunately although I am very experienced with creating forms and programs in MS ACCESS, I am no
familiar with VB or SQL (never had to use it till now)

I am pulling my hair out trying to figure out how to just do this 1 thing. The rest of my program runs is done otherwise
Thanks in advance for anyone's help on this.

 

[Guest]

Nancy

I just finally found the question I posted! LOL
Thanks so much for your help, this one has been a major hair puller! I've looked up everything from Dlookup to Dcount etc.
I will try your suggestion. I hope this will finally solve my delimna. I have 99% of my program done and couldn't figure this last part out for the life of me

Thanks agains SO MUCH for your help!

 

[Guest]

Hi Nancy

I tried the code you suggested, and substituted my form info (shown below) but nothing happened (?
SN is the name of the field in the form that the user enters a numerical value of 1 or 2 (not text)
I typed this code in the AFTERUPDATE event procedure but neither query seems to be evoked when I typ
a 1 or 2 in the form field. Am I doing something wrong? Shouldn't one of the queries run? I'm stummped again (
Thanks so much in advance for your help again

If Form1!SN =1 the
DoCmd.OpenQuery "qry1
Elseif Form!SN =2 the
DoCmd.OpenQuery "qry2
Els
MsgBox "You have made an invalid choice, please re-enter"
End I

 

[Guest]

How is SN defined? - Text Box? and is it Unbound [nothing in Control Source]

You were close ..

If [Forms]![Form1].[SN] = "1" the
DoCmd.OpenQuery "Query1", acViewNorma
ElseIf [Forms]![Form1].[SN]= "2" the
DoCmd.OpenQuery "Query2", acViewNorma
Els
MsgBox "You have made an invalid selection, please re-enter", vbOkOnl
End i

You can also use

If Me!SN = 1 the
DoCmd.OpenQuery "Query1", acViewNorma
ElseIf Me!SN = 2 the
DoCmd.OpenQuery "Query2", acViewNorma
Els
MsgBox "You have made an invalid selection, please re-enter", vbOkOnl
End I

This should be on the After Update Event on the Form you are interrogating. You can either put a Button on the form in which case, the code would be on the button - Click or DoubleClick Event. If not using a button, then the user would have to hit enter to get the code to work

Nancy Welc
Programmer/Analys
King County IT

 

[Guest]

Hi Nancy

Thanks again for your help on this

I set up SN to be a number field and am not sure what you mean by Unbound or control source
The user just enters a random number (i.e. does not select from a list/combo box)

I will the code you listed. I hope this works! Is acViewNormal just part of the script that I should just type in
I did enter this script in the Afterupdate event as you instructed. I tested to see if the code would work by hittin
enter after I keyed in a SN number

I will let you know if this works
Thanks a million again!

 

[Kelvin]

See below.

Nancy,
Thanks for your help! I was able to get the suggestion you gave me to

work but needed to change the action last minute to

compare the SN entered by the operator to another table. If the SN enter

already existed I want Macro1 to run

else if the SN did not exist in the other table, I wanted Macro2 to run.

SO I thought I could use the same logic

set up by the VB lines you gave me that worked by just changing my

condition and the Docm...BUT this did not work.

1) I did this 1st and IT WORKED ok
If [Forms]![Form1].[SN] = "1" then
DoCmd.OpenQuery "Query1", acViewNormal
ElseIf [Forms]![Form1].[SN]= "2" then
DoCmd.OpenQuery "Query2", acViewNormal
Else
MsgBox "You have made an invalid selection, please re-enter", vbOkOnly
End if

2) BUT WHEN I MODIFIED IT TO THIS, IT DIDN'T WORK:
If [Forms]![Form1].[SN] = [Tables]![Table1].[SN]
DoCmd.RunMacro "Macro1"
ElseIf [Forms]![Form1].[SN]<>[Tables]![Table1[.SN] then
DoCmd.RunMacro "Macro2"
End if

I thought this would work but I got either an error message "|" not found

or no error message.

This won't work becasue you cannot refer to a table like this.

3) I ALSO tried using a DLOOKUP function (which didn't work as well)

If (Not Isnull(DLookup("SN","Table1","SN=" & Forms![Form1]!txtSN" Then
DoCmd.RunMacro "Macro1"
Else
If (Isnull(DLookup("SN","Table1","SN=" & Forms![Form1]!txtSN" Then
DoCmd.RunMacro "Macro2"

End if

This part is very confusing. Try the following instead:

If (IsNull(Dlookup("SN","Table1","SN='" & Forms![Form1]!txtSN & "'") Then
DoCmd.Run Macro2
Else
DoCmd.RunMacro1
End If

Notice the single qutoes that have to go into the criteria.

Kelvin

I am 99% done with the urgent program I have to get done for work but am

totally stumped on how to get this

part to work. It's stressing me! LOL. I've been able to create numerous

advanced MS Access programs thus far without