Form_Click() on a Datasheet view

[Roland Alden]

In the Form_Click() handler of a Datasheet view I am trying to get a column
value from the row that was clicked on.

me.uid (uid is the name of the field in question) seems to return the
"current" row, not the row that might/will become current once the click is
processed.

I can't use Form_Current for this because it synchronizes the Datasheet
based on other events (such as the linked parent form moving). In this case
I specifically want to take some action when there is a click on a row
selector in the Datasheet view but I need the underlying column values to do
it.

 

[Wayne Morgan]

You could set a "flag" variable in the Click event. In the current event,
check the value of the flag and react accordingly. The flag variable would
need form level scope or higher. To do this, DIM it in the Declarations
section of the form's code (where it say Option Compare Database and,
hopefully, Option Explicit). In the handling in the Current event, remember
to set the flag back to its original value.

 

[Roland Alden]

Thanks. I take it that Click gets called first and then Current?

 

[Wayne Morgan]

No, I just tried it. I placed a message box in each event and the Current
event box came up first, but from you're description in the original message
it sounded as if you were experiencing it the other way.

What are you doing in the Current event now? You mentioned synchronizing the
subform with the main form if the main form changes records. If you simply
go from one record to another in the subform, then the main form shouldn't
have changed records. Also, are you using the Master and Child link fields
between the main form and the subform? You could have a Static variable in
the Current event to check for a value from a unique field on the main form
and if it hasn't changed, ignore the rest of the Current event. Next, the
Click event will process with your code.

Can you paste in the code you have in the Current event?

 

[Roland Alden]

You are close to guessing the deepness of the hole I have dug

Here is what I'm doing (sort of) and what I think I want:

I have a main form (called "frame") and two child forms (called "form view"
and "sheet view"). All are basically nailed down to the same query/table and
are synched with parent child fields. There is navigation on the frame, and
when you move it, the "form view" walks through each record, one at a time.

The records themselves are transactions, and one of the fields is "dog".

In the "sheet view" the parent/child link is on "dog" so that what happens
is that sheet view displays all the rows for a particular dog. In the form
you are looking at one record for the dog, and in the sheet you see all of
them. It is sometimes easier to scan/edit a set of related records in sheet
view, on the other hand, it is nicer to enter/edit/ponder a complex record
in form view so I'm trying to get the best of both here.

Master/Child does most of the work automatically. As a new dog is brought
into view using the navigators, or a search box I have on the "frame", and
presto, the sheet view is synched. However, when I click on a row in the
sheet view that is different than the record in the form I would like the
form to switch to that record. Put another way: if the form is viewing a
dog, the sheet shows data from that dog. What I want is as follows: if the
focus is on a certain dog record in the sheet, I want the form to display
that dog.

Trying to code this in the Current for each of the three forms I get an
infinite loop because moving the record fires another Current. I have got it
working now, as you suggest, by having a Guid on the frame and a Current
that checks to see if we are already there and it doesn't do a move record
in that case. I was using that technique to move the focus on the sheet to
agree with what record the form was on but what I really want is the other
way around; a hit on the sheet to move the record in the form.

To make matters worse I don't really understand the name/scope system in
Access. In having the two child forms talk to each other it seems as if I
have to route everything through the parent. I seemed to be able to figure
out identifiers that would allow the parent to refer to child (sub) forms
OK, and for child forms to refer to their parent. Actually, it seems as if
there are about a dozen different ways to do this and I'm sure some of them
are Not Best Practices but I don't know which. However, I've not been
successful in having a child talk to a "peer" directly. Also, there are
really two names for a subform near as I can tell. The parent has a kind of
container name (where the master/child fields live) that it can refer to,
and then there is the subform inside that. It's not clear to me if a parent
can/should refer to the container name or the form itself. In any case, you
can see I'm confused and if I need to involve the Current of the parent
as well as each child to pull this off I will almost certainly be debugging
some more infinite loop situations without expert guidance.

TIA

 

[rkc]

You are close to guessing the deepness of the hole I have dug

Here is what I'm doing (sort of) and what I think I want:

<snip a lot-O-stuff>

Take a look at the Customer Orders form in the Northwind sample
..mdb. As far as I can tell it does something real close to what
you are trying to do.

 

[Roland Alden]

Take a look at the Customer Orders form in the Northwind sample

.mdb. As far as I can tell it does something real close to what
you are trying to do.

Not quite. First off, it only has a one-way relationship between the two
forms. You can click on a row in the top form and it will change the bottom
(sub)f orm. However, if you click on a row in the bottom it does not change
the top. Thus this does not show me how to get around the "infinite loop"
problem.

It did show me the little bit of syntax I was wondering about
(Me.Parent![<formname>].Requery), so I thank-you for the pointer anyway.

 

[Wayne Morgan]

I'm not fully following, I'll have to think about it for awhile, but a
couple of things came to mind right off-hand so I thought I'd bounce them
off of you and see what you think. 1) Rather than two subforms of the main
form, should one subform be a subform of the other subform? 2) Could you get
by with one subform and switch between Form view and Datasheet view using a
button on the main form?

Example of toggling view:
Const conFormView = 1
Const conDataSheetView = 2

Me.SubformControl.SetFocus
If Me.SubformControl.Form.CurrentView = conFormView Then
RunCommand acCmdSubformDatasheetView
Else
RunCommand acCmdSubformFormView
End If

 

[Roland Alden]

If the sheet were a subform of the formview form then master/child will keep
them in synch. However, I have tried that in the past and a click on a
random row in the sheet still won't move the "pointer" for the pair such
that the form will scroll that record into view automagically.

Certainly having two forms be subforms of a parent and them both
representing two different views over the same data is "reasonable" no?

I suspect if I understood recordset "cloning" better I'd understand how to
do this. What's not clear (to me) is whether or not a "tree" of forms all
viewing the same underlying data "share" a "recordset" (their pathway into
that data). Actually it's pretty clear to me that they each have their own
state. What I think I want to do here is actually "force" the two child
forms to *really*share the parent's recordset such that any modification of
the "current record" pointer on that will move both subforms in synch.

Another thing which is not clear (to me) is what the "current record" means
in the context of a sheet view (which can show many records) and a "Current"
event (which is (sometimes?) a click on One of those records). To make any
notion of this work there has to be a clear concept of a single record in
the sheet view. Perhaps the "focus" or "last focus" is relevant?

 

[Wayne Morgan]

Another thing which is not clear (to me) is what the "current record"

means
in the context of a sheet view (which can show many records) and a
"Current"
event (which is (sometimes?) a click on One of those records).

The current record for the subform or any form, whether in single form view,
continuous form view, or datasheet view is the record where the cursor is.
The Current event fires when you move from one record to another. Clicking
may or may not be the method used to cause that movement.

If both subforms are different views of the same data, then yes, linking
(master/child links) both of them to the main form should keep them in sync
as the main form moves.

If you move one of the subforms and want the other subform to move where the
first one was moved to (but the main form's record hasn't changed), it would
probably be best to put a button on the main form (use the main form for the
button since one of the subforms is in datasheet view) and click that button
to sync them when needed by using FindFirst and the unique field value of
the record to move the other subform.