Parameter passing from module to query

[Edward_Whitmore]

Dear Access Community:
I am new to Access - especially coding. I looked for the answer to my
question, but wasn't able to find an answer to this exact question. Your
help is much appreciated.

I use the code below to ask the user to enter a manager ID (either 1190 or
20368). The value is stored in the variable InputMgrID. Based on the
response, the program opens one of two forms (frmMgrLvl1 or frmMgrLvl2) based
on two different queries (qryMgrLvl1 and qryMgrLvl2). The form frmMgrLvl1 is
based on query qryMgrLvl1 - and within that query, there is a criterion for
the MgrID field: [Enter manager ID]. So when I run the module, the user is
asked for the manager ID twice - once for the InputBox and once within the
form.

Is there a way to have the query use the value from the InputBox as the
criteria so the user doesn't have to enter the ID twice?

Thank you!

Sub OpenDatabase()
'Ask for manager password, store in variable
Dim InputMgrID As String
InputMgrID = InputBox("Enter Manager ID")
'Choose the form based on the manager ID
Select Case InputMgrID
Case 1190
DoCmd.OpenForm "_frmMgrLvl1", acNormal, "", "", , acNormal
Case 20638
DoCmd.OpenForm "_frmMgrLvl2", acNormal, "", "", , acNormal
Case Else
MsgBox ("Manager not recognized")
End Select
End Sub

 

[Jim Burke in Novi]

I think he assumes, as I would, that you must be running the OpenDatabase sub
somewhere from within another form's code. So you would want to create a
hidden text box on that form, e.g. call it txtManagerID. In your OpenDatabase
sub (I'm assuming this sub is in a form's module), rather than have a
variable in that sub called InputMgrID, you would use the statement

txtManagerID = InputBox("Enter Manager ID")

Then use SELECT CASE txtManagerID instead of SELECT CASE InputMgrID. Then,
in the queries you would put

Forms![YourForm]![txtManagerID].Value

in the Criteria for the manager ID. Really, you wouldn't need to use
InputBox. You could just have txtManagerID labelled "Manager ID:", and the
user could just type the ID in that field. Then have a button they click once
they've entered the ID whose Open event runs the OpenDatabase sub.

If this code isn't being run from another form, but is run, for example, by
an AutoExec macro, then you should create a form that is strictly used to
enter the ID and open the appropriate form based on that.

Yes. In the criteria for the ManagerID field, put:

Forms![YourForm]![YourFieldName].Value

Dear Access Community:
I am new to Access - especially coding. I looked for the answer to my
question, but wasn't able to find an answer to this exact question. Your
help is much appreciated.

I use the code below to ask the user to enter a manager ID (either 1190 or
20368). The value is stored in the variable InputMgrID. Based on the
response, the program opens one of two forms (frmMgrLvl1 or frmMgrLvl2) based
on two different queries (qryMgrLvl1 and qryMgrLvl2). The form frmMgrLvl1 is
based on query qryMgrLvl1 - and within that query, there is a criterion for
the MgrID field: [Enter manager ID]. So when I run the module, the user is
asked for the manager ID twice - once for the InputBox and once within the
form.

Is there a way to have the query use the value from the InputBox as the
criteria so the user doesn't have to enter the ID twice?

Thank you!

Sub OpenDatabase()
'Ask for manager password, store in variable
Dim InputMgrID As String
InputMgrID = InputBox("Enter Manager ID")
'Choose the form based on the manager ID
Select Case InputMgrID
Case 1190
DoCmd.OpenForm "_frmMgrLvl1", acNormal, "", "", , acNormal
Case 20638
DoCmd.OpenForm "_frmMgrLvl2", acNormal, "", "", , acNormal
Case Else
MsgBox ("Manager not recognized")
End Select
End Sub

 

[Edward_Whitmore]

Dear tkelley:
Thank you for writing - and so quickly. I'm sorry, but I don't understand
your recommendation. The manager ID is coming from an InputBox rather than a
form. I guess I don't understand what form and field you are referring to in
your recommendation.

What I think I should be able to do is to put the variable name, InputMgrID
in the criteria for the query - but how do I get the query to understand that
the criteria refer to a variable whose value was defined by an InputBox?

Regards,
Edward Whitmore

Yes. In the criteria for the ManagerID field, put:

Forms![YourForm]![YourFieldName].Value

Dear Access Community:
I am new to Access - especially coding. I looked for the answer to my
question, but wasn't able to find an answer to this exact question. Your
help is much appreciated.

I use the code below to ask the user to enter a manager ID (either 1190 or
20368). The value is stored in the variable InputMgrID. Based on the
response, the program opens one of two forms (frmMgrLvl1 or frmMgrLvl2) based
on two different queries (qryMgrLvl1 and qryMgrLvl2). The form frmMgrLvl1 is
based on query qryMgrLvl1 - and within that query, there is a criterion for
the MgrID field: [Enter manager ID]. So when I run the module, the user is
asked for the manager ID twice - once for the InputBox and once within the
form.

Is there a way to have the query use the value from the InputBox as the
criteria so the user doesn't have to enter the ID twice?

Thank you!

Sub OpenDatabase()
'Ask for manager password, store in variable
Dim InputMgrID As String
InputMgrID = InputBox("Enter Manager ID")
'Choose the form based on the manager ID
Select Case InputMgrID
Case 1190
DoCmd.OpenForm "_frmMgrLvl1", acNormal, "", "", , acNormal
Case 20638
DoCmd.OpenForm "_frmMgrLvl2", acNormal, "", "", , acNormal
Case Else
MsgBox ("Manager not recognized")
End Select
End Sub

 

[Edward_Whitmore]

Dear Jim:
Thank you. I'll try that.

Regards,

Edward

I think he assumes, as I would, that you must be running the OpenDatabase sub
somewhere from within another form's code. So you would want to create a
hidden text box on that form, e.g. call it txtManagerID. In your OpenDatabase
sub (I'm assuming this sub is in a form's module), rather than have a
variable in that sub called InputMgrID, you would use the statement

txtManagerID = InputBox("Enter Manager ID")

Then use SELECT CASE txtManagerID instead of SELECT CASE InputMgrID. Then,
in the queries you would put

Forms![YourForm]![txtManagerID].Value

in the Criteria for the manager ID. Really, you wouldn't need to use
InputBox. You could just have txtManagerID labelled "Manager ID:", and the
user could just type the ID in that field. Then have a button they click once
they've entered the ID whose Open event runs the OpenDatabase sub.

If this code isn't being run from another form, but is run, for example, by
an AutoExec macro, then you should create a form that is strictly used to
enter the ID and open the appropriate form based on that.

Yes. In the criteria for the ManagerID field, put:

Forms![YourForm]![YourFieldName].Value

Dear Access Community:
I am new to Access - especially coding. I looked for the answer to my
question, but wasn't able to find an answer to this exact question. Your
help is much appreciated.

I use the code below to ask the user to enter a manager ID (either 1190 or
20368). The value is stored in the variable InputMgrID. Based on the
response, the program opens one of two forms (frmMgrLvl1 or frmMgrLvl2) based
on two different queries (qryMgrLvl1 and qryMgrLvl2). The form frmMgrLvl1 is
based on query qryMgrLvl1 - and within that query, there is a criterion for
the MgrID field: [Enter manager ID]. So when I run the module, the user is
asked for the manager ID twice - once for the InputBox and once within the
form.

Is there a way to have the query use the value from the InputBox as the
criteria so the user doesn't have to enter the ID twice?

Thank you!

Sub OpenDatabase()
'Ask for manager password, store in variable
Dim InputMgrID As String
InputMgrID = InputBox("Enter Manager ID")
'Choose the form based on the manager ID
Select Case InputMgrID
Case 1190
DoCmd.OpenForm "_frmMgrLvl1", acNormal, "", "", , acNormal
Case 20638
DoCmd.OpenForm "_frmMgrLvl2", acNormal, "", "", , acNormal
Case Else
MsgBox ("Manager not recognized")
End Select
End Sub