OnClick Event in Form STOPS WORKING when Form is Made a Sub-Form

[kev100 via AccessMonster.com]

I've got a form which displays data in datasheet format.

One of the fields (cells) has an On-click event set.

It opens a query. That query uses data from that clicked cell in that data
sheet as a parameter query input.

It works GREAT when that Form is run by itself.

However...when I imbedded that query into another form. It displays its data
fine....however...when I click on the cell...the query will run...but it is
prompting me for that cell's value. That value is, for some reason, not
being passed through to the query.

Here's how it's set up:

A form called "NameForm" displays 2 fields (cells) First Name , Last Name

When I click on Last Name, a query will run (via an onclick event) which
displays the Phone number and Address of that person.

The criteria in the query is: [Forms]![NameForm]![LastName]

Running NameForm and clicking works GREAT.

However......

When I embed NameForm into another form called "MasterForm"...........it
displays the First / Last Name lists fine....but when the user clicks on the
Last Name cell they are Prompted to enter:

[Forms]![NameForm]![LastName]

Do I need to add some sort of specifier to the above since the form in now
embedded in another?

Thanks very much.

 

[Klatuu]

Since the critera, as you have it, is object specific, it will only be able
to find the value when that form is open and is not a sub form to another
form. A way around that is to use a Static function in a standard module.
In the click event you describe, load a value into the function, then use the
function as the criteria.

A static function looks like this:

Static Function GetLastName(Optional ByVal varNewName As Variant) As String
Dim varOldName As Variant

If Not IsMissing(varNewName) Then
varOldName = varNewName
End If
GetLastName = varOldName

End Function

In case you are not familiar with how a static function works. The static
function will retain the value in all it's variables until you change them or
the application closes. That assumes the static function is in a standard
module. If you include it in a form module, the values are only retained as
long as the form is open. In the example above. You would assign the value
in the click event:

GetLastName(Me.LastName)

That will give the function the value in the control.


Then run the query. The query will use the function without an argument as
the criteria in the query.

 

[kev100 via AccessMonster.com]

I kind of follow....but...I think I'm missing something.


In the OnClick event......don't I have to call the query....since that's what
I want to run when I click a particular cell?


If I put GetLastName(Me.LastName) as the On Click event....how do I activate
that query?

Thanks

 

[kev100 via AccessMonster.com]

I've tried a few more things...


Currently....I have a Module Named:

GetLastNameFunction

.....it contains:

Static Function GetLastName(Optional ByVal varNewName As Variant) As String
Dim varOldName As Variant

If Not IsMissing(varNewName) Then
varOldName = varNewName
End If
GetLastName = varOldName

End Function



The Criteria in the Query that is run by clicking on the cell in the form is..
..

[modules!][GetLastNameFunction]

.....I've also tried simply: [GetLastName]


The On Click action in the event tap of the cell on the form that I need to
run the query (when clicked) is simply the name of that query (doesn't it
have to be?).

So....I'm not sure where...

GetLastName(Me.LastName)

....is supposed to go. When/where does the value in the cell that is clicked
get assigned to the GetLastName variable?

and......am I using the right thing in the criteria of my query to refer to
the value stored in GetLastName?

Thanks very much.

Thanks

 

[Klatuu]

You have to call the function once to put the value into it. Then you put
the function where your parameter is currently in the query. Now the query
will call the function to get the value.

 

[kev100 via AccessMonster.com]

If I unstand this correctly....I would need the OnClick Event to do 2 things:

Then you put the function where your parameter is currently in the query.

Can be put in the criteria of the query that is opened by the OnClick event.

But....

You have to call the function once to put the value into it.
and

Now the query will call the function to get the value.

....would have to be BOTH be activated by the OnClick Event...

Do I understand this correctly? If so....how could 1 On Click event activate
2 actions (Calling the Function to load the value AND open the Query) ?

Thanks

 

[Klatuu]

That is not a problem. If you are using VBA for your Click event, it is just
another sub, so you can do as much as you need in that code.

 

[kev100 via AccessMonster.com]

That is not a problem. If you are using VBA for your Click event, it is just
another sub, so you can do as much as you need in that code.

I think I see....

The function does both the loading of the value and the opening of the query.
I just put the function name (module) in the On Click Event....(?)

 

[Klatuu]

I don't think we are on the same page. You need to be using the VBA code
event. Then in that event, you call the function and assign it a value. The
the same function can be used in the criteria of the query.

 

[kev100 via AccessMonster.com]

I don't think we are on the same page. You need to be using the VBA code
event. Then in that event, you call the function and assign it a value. The
the same function can be used in the criteria of the query.

Okay...I think I have it.....

....When I create the function (the actually programming).....I actually do so
right there at the On Click event line.....where I specifiy the OnClick event.
.....Then.....I simply refer to that created function in the query.....(?)

There are really just 2 "places" needed...the On Click event and the Query....
(?)

 

[Klatuu]

That is correct.

Okay...I think I have it.....

....When I create the function (the actually programming).....I actually do so
right there at the On Click event line.....where I specifiy the OnClick event.
.....Then.....I simply refer to that created function in the query.....(?)

There are really just 2 "places" needed...the On Click event and the Query....
(?)