Using an Object from a Current form for Criteria in a Query

  • Thread starter Thread starter nytwodees
  • Start date Start date
N

nytwodees

I am using Access 2000.

I have a Command Button on a form that is designed to preview or print that
customer's invoice.

I use a Pop-Up Dialog Box. The end user will supply 2 pieces of data in this
Dialog Box. This data, together with the Customer ID from the current form.
will provide the 3 necessary pieces of info to pass on to the query, and
eventually the report, that creates the invoice.

I suspect that the "Me." or "Me!" "command" or "action" must be used, but am
not clear about how to use it.

Perhaps someone can give me some examples of how to use that "command" or
"action" with the proper syntax. I've read the Help with no success. It
seems to be a important tool!
 
I suspect that the "Me." or "Me!" "command" or "action" must be used, but am
not clear about how to use it.
Click to expand...

This Me keyword is probably one of the greatest tools available to
form/report programming, and you will be very happy to learn to use it.

I would suggest, where possible (which is pretty much everwhere) to use the
Me. rather than the Me! The bang operator (!) has a specific meaning that
is generally easily confusable. The dot operator will, unlike the bang
operator, provide you with a list of available commands/actions that pertain
to Me (this list is called intellisense, and is quite handy)

The keyword "Me" refers to the current form or report. So lets say you have
a simple form with a textbox (which is named txtThisBox) and a button (which
is named btnThisButton), and the form itself is named frmThisForm.

When you open frmThisForm, and go to the events and create an event
procedure, it brings you to the code module behind that form. Because
txtThisBox and btnThisButton are members of frmThisForm, you can reference
them in this module.

If you type "Me.", as soon as you hit the "." you will see a list pop up of
all the available methods, actions and controls pertaining to the form. So
lets pretend that you want to click on btnThisButton, and if there is an
entry in txtThisBox, use that entry in an sql statement, and change the
caption of the button to "SQL Ran" (I understand that this is not a likely
situation, but serves the purpose of example).

Your event procedure for btnThisButton_Click may resemble something like
so... notice how the Me keyword is used to reference the controls that are
on the form.




Private Sub btnThisButton_Click()

Dim strSQL As String 'to hold your sql until we run it

'if there's no value in txtThisBox exit the sub
If Nz(Me.txtThisBox, "") = "" Then
Exit Sub
End If

'if we made it this far there was a value, so we'll continue...
'lets enter the value into an sql string

strSQL = "DELETE * FROM tblThisTable WHERE " _
& "ThisField = '" & Me.txtThisBox & "'"

'now we have an sql string that has the value of the text
'box inserted as criteria

CurrentDb.Execute strSQL

'now set the caption of the button to "SQL Ran"

Me.btnThisButton.Caption = "SQL Ran"

End Sub





Hopefully this sheds a little bit of light on the usage of the Me keyword.
Basically, in a form or report, this allows you to quickly and easily
reference all of the form/reports properties, actions, controls, methods, etc
etc.

happy coding

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Thanks for your lengthy and informative response. Unfortunately it did not
get me any further.

Let me make myself clearer:

1. Suppose the field from my form that I want to capture and Pass on to my
query id CustomerID. What code should I use?

2. How do I then enter it in my query so that the criteria for the
CustomerID field is = to the Me. ,,, expression, or whatever the necessary
expression should be?
 
You can use this type of thing in the criteria row of a query.
Forms![NameOfForm]![NameOfControl]

Access can read the value of customer id from the open form and put in the
query.

You would want to do this if you are running the query while the form is
open,
and if the query is not the record source of the form.

We are still trying to completely understand your question, so hope we are
getting closer to your scenario and an answer that will work for you.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette:

Thanks again for your help!

If I understand you correctly all I need do is enter
Forms![NameOfForm]![NameOfControl] in the criteria of the query's grid!

Then this is one of the methods to pass on a value to the query. The other
method is to pass on values from a Form's Dialog Box to the query.

So I am basically doing the same thing to pass on the 3 values, 1 from the
Original Form and 2 from the Dialog Box Form. Is that correct?

Tomorrow morning I will try your suggestion.

PS: There is no need for Me. or Me! ????



yJeanette Cunningham said:
You can use this type of thing in the criteria row of a query.
Forms![NameOfForm]![NameOfControl]

Access can read the value of customer id from the open form and put in the
query.

You would want to do this if you are running the query while the form is
open,
and if the query is not the record source of the form.

We are still trying to completely understand your question, so hope we are
getting closer to your scenario and an answer that will work for you.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

nytwodees said:
Thanks for your lengthy and informative response. Unfortunately it did
not
get me any further.

Let me make myself clearer:

1. Suppose the field from my form that I want to capture and Pass on to my
query id CustomerID. What code should I use?

2. How do I then enter it in my query so that the criteria for the
CustomerID field is = to the Me. ,,, expression, or whatever the necessary
expression should be?
Click to expand...
Click to expand...
 
That's the idea.
You can't use Me. or Me! because the query is not part of the form.
You can only use Me. or Me! when code is running in the form and Me. refers
to the form where the code is running.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

nytwodees said:
Hi Jeanette:

Thanks again for your help!

If I understand you correctly all I need do is enter
Forms![NameOfForm]![NameOfControl] in the criteria of the query's grid!

Then this is one of the methods to pass on a value to the query. The
other
method is to pass on values from a Form's Dialog Box to the query.

So I am basically doing the same thing to pass on the 3 values, 1 from the
Original Form and 2 from the Dialog Box Form. Is that correct?

Tomorrow morning I will try your suggestion.

PS: There is no need for Me. or Me! ????



yJeanette Cunningham said:
You can use this type of thing in the criteria row of a query.
Forms![NameOfForm]![NameOfControl]

Access can read the value of customer id from the open form and put in
the
query.

You would want to do this if you are running the query while the form is
open,
and if the query is not the record source of the form.

We are still trying to completely understand your question, so hope we
are
getting closer to your scenario and an answer that will work for you.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

nytwodees said:
Thanks for your lengthy and informative response. Unfortunately it did
not
get me any further.

Let me make myself clearer:

1. Suppose the field from my form that I want to capture and Pass on to
my
query id CustomerID. What code should I use?

2. How do I then enter it in my query so that the criteria for the
CustomerID field is = to the Me. ,,, expression, or whatever the
necessary
expression should be?
Click to expand...
Click to expand...
Click to expand...
 
Thanks Jeanette for all of your help. I now get what I am looking for.

Jeanette Cunningham said:
That's the idea.
You can't use Me. or Me! because the query is not part of the form.
You can only use Me. or Me! when code is running in the form and Me. refers
to the form where the code is running.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

nytwodees said:
Hi Jeanette:

Thanks again for your help!

If I understand you correctly all I need do is enter
Forms![NameOfForm]![NameOfControl] in the criteria of the query's grid!

Then this is one of the methods to pass on a value to the query. The
other
method is to pass on values from a Form's Dialog Box to the query.

So I am basically doing the same thing to pass on the 3 values, 1 from the
Original Form and 2 from the Dialog Box Form. Is that correct?

Tomorrow morning I will try your suggestion.

PS: There is no need for Me. or Me! ????



yJeanette Cunningham said:
You can use this type of thing in the criteria row of a query.
Forms![NameOfForm]![NameOfControl]

Access can read the value of customer id from the open form and put in
the
query.

You would want to do this if you are running the query while the form is
open,
and if the query is not the record source of the form.

We are still trying to completely understand your question, so hope we
are
getting closer to your scenario and an answer that will work for you.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks for your lengthy and informative response. Unfortunately it did
not
get me any further.

Let me make myself clearer:

1. Suppose the field from my form that I want to capture and Pass on to
my
query id CustomerID. What code should I use?

2. How do I then enter it in my query so that the criteria for the
CustomerID field is = to the Me. ,,, expression, or whatever the
necessary
expression should be?
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top