using memory variables in a query criteria

[Guest]

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

How do I do that? BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

Thanks

 

[Guest]

Hi, Paul.

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

You can do this by creating an actual query or by writing it entirely in VBA
code. I'll assume you want the easiest and quickest, but please advise if
you want to do this entirely in VBA code.

When you build the query, type the following in the "Criteria:" cell below
the "Control_Number" field in the QBE grid:

[Forms]![MyForm]![txtControl_Number]

Replace "MyForm" with the name of your open form and txtControl_Number with
the name of the text box displaying the value of the "Control_Number" field.
Save the query and then open it in Datasheet View (your form must be open and
have a value for this to work). Every time you run this query, it will use
the criteria supplied on this open form, no matter which record is displayed.

BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

You can start by converting your macros to VBA and examining the code
produced by the Wizard and comparing that with the actions in the macro to
see what the equivalents are. To convert a macro, select the name of the
macro in the Database Window then select the Tools -> Macro -> Convert Macros
to Visual Basic to open the Convert Macros dialog window. Select the
"Convert" button and the macro will be converted for you and saved in a new
module named "Converted Macro- " followed by the name of your macro. Compile
the code.

Now you can use this public function in code or in property settings
whenever needed and just delete the original macro. Do this for all of your
macros when you feel you are ready to take the next step in the learning
process, and the next step in building more reliable, more flexible, and more
complex database applications.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi Gunny, and thanks.

OOPs, I left out one important thing. This query will be used with a number
of forms, so it would cool if I could just take that value, store it as a
variable and point the query criteria to the variable. The alternative is to
build a different query for each form. There's got to be a way of doing it.
It's times like these that I long for dBase.

Thanks again

Hi, Paul.

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

You can do this by creating an actual query or by writing it entirely in VBA
code. I'll assume you want the easiest and quickest, but please advise if
you want to do this entirely in VBA code.

When you build the query, type the following in the "Criteria:" cell below
the "Control_Number" field in the QBE grid:

[Forms]![MyForm]![txtControl_Number]

Replace "MyForm" with the name of your open form and txtControl_Number with
the name of the text box displaying the value of the "Control_Number" field.
Save the query and then open it in Datasheet View (your form must be open and
have a value for this to work). Every time you run this query, it will use
the criteria supplied on this open form, no matter which record is displayed.

BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

You can start by converting your macros to VBA and examining the code
produced by the Wizard and comparing that with the actions in the macro to
see what the equivalents are. To convert a macro, select the name of the
macro in the Database Window then select the Tools -> Macro -> Convert Macros
to Visual Basic to open the Convert Macros dialog window. Select the
"Convert" button and the macro will be converted for you and saved in a new
module named "Converted Macro- " followed by the name of your macro. Compile
the code.

Now you can use this public function in code or in property settings
whenever needed and just delete the original macro. Do this for all of your
macros when you feel you are ready to take the next step in the learning
process, and the next step in building more reliable, more flexible, and more
complex database applications.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

How do I do that? BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

Thanks

 

[Guest]

Hi, Paul.

OOPs, I left out one important thing. This query will be used with a number
of forms, so it would cool if I could just take that value, store it as a
variable and point the query criteria to the variable.

No problem. That's the better method anyway for maintainability. You'll
need to do some legwork first. In a standard module, paste the following
(I'm assuming you've got a string value, but if not, just change the data
type):

Private m_sCtrlNum As String

Public Function setCtrlNum(sSomeVal As String)
m_sCtrlNum = sSomeVal
End Function

Public Function getCtrlNum() As String
getCtrlNum = m_sCtrlNum
End Function

Create your query that uses the "get" function in the WHERE clause. For
example:

SELECT *
FROM tblControlRecords
WHERE (Control_Number = getCtrlNum( ));

Now, whenever you want to run the query, just call the "set" function in VBA
code to set the value that the query needs first, then run the query. For
example:

Private Sub testCtrlNumSetting()
Call setCtrlNum("V1234N")
DoCmd.OpenQuery "qryCtrlNums"
End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi Gunny, and thanks.

OOPs, I left out one important thing. This query will be used with a number
of forms, so it would cool if I could just take that value, store it as a
variable and point the query criteria to the variable. The alternative is to
build a different query for each form. There's got to be a way of doing it.
It's times like these that I long for dBase.

Thanks again

Hi, Paul.

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

You can do this by creating an actual query or by writing it entirely in VBA
code. I'll assume you want the easiest and quickest, but please advise if
you want to do this entirely in VBA code.

When you build the query, type the following in the "Criteria:" cell below
the "Control_Number" field in the QBE grid:

[Forms]![MyForm]![txtControl_Number]

Replace "MyForm" with the name of your open form and txtControl_Number with
the name of the text box displaying the value of the "Control_Number" field.
Save the query and then open it in Datasheet View (your form must be open and
have a value for this to work). Every time you run this query, it will use
the criteria supplied on this open form, no matter which record is displayed.

BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

You can start by converting your macros to VBA and examining the code
produced by the Wizard and comparing that with the actions in the macro to
see what the equivalents are. To convert a macro, select the name of the
macro in the Database Window then select the Tools -> Macro -> Convert Macros
to Visual Basic to open the Convert Macros dialog window. Select the
"Convert" button and the macro will be converted for you and saved in a new
module named "Converted Macro- " followed by the name of your macro. Compile
the code.

Now you can use this public function in code or in property settings
whenever needed and just delete the original macro. Do this for all of your
macros when you feel you are ready to take the next step in the learning
process, and the next step in building more reliable, more flexible, and more
complex database applications.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have a form with the field "Control_Number". I want to store the value
contained in that field as a variable for use as a criteria in a select query.

How do I do that? BTW, I'm not a VB programmer yet, so all my actions are
contained in Macros.

Thanks