Firing On Current Event For Subdatasheet Form

[SkyCraw]

Hello,

Is it possible to fire the On Current event for the subform of a datasheet main form when you expand a record's subdatasheet view (clicking the + sign) regardless of what record your cursor is in?

Right now, you have to select the desired record fire in order to fire the subdatasheet form's On Current event when you expand the subdatasheet view (+ sign).

Thanks for any help in advance

 

[MarkK]

A Current event is raised by an Access.Form object. Is there a form object involved in a subdatasheet? Not sure about that, but I don't think so.

 

[SkyCraw]

Yes, there's a form object involved with the subdatasheet. The form hierarchy is currently structured as follow:

Main Form (single)
Sub Form (datasheet)
Sub-Sub Form (continuous)

So, I would like to fire the Sub-Sub Form's On Current event whenever I click the expansion (+ sign) button in the Sub Form regardless of where the cursor is on the Sub Form. However, if this is not possible, then what I have right now is workable.

 

[spikepl]

Show some screenshots and indicate what is what. I'd love to see a sub sub form hooked on subdatasheets.

 

[SkyCraw]

Sure! It took a bit of searching, but I was grateful I could get away with something like this for what I was trying to accomplish



Right now, the On Current switches the sub-sub form's source object between three forms (showing 5, 10 or 15 lines) based on if a field in the 5th or 10th "line" (row of controls in one record) is empty or not. Right now, for it to fire, I have to have the cursor in the same record I wish to expand as show here:

 

[spikepl]

I think we are deep into some terminology confusion her. I do not see any subsub form. I see a subtable displayed in a subform (in the standard datasheet format, not in continuous format as per your claim).

Show your claimed subsubform object in the design view. You must be able to do that so as to set event handlers.

Otherwise, I do not think any accessible object exists there, and you cannot access any events stemming from that subtable.

In any case, what are you trying to accomplish?

 

[SkyCraw]

My apologies, I did misinterpret; the On Current event I'm trying to fire is the Sub Form's not the Subsubform's.

Here's the design view of the subsubform (initially, before I split into 3 different forms for the On Current Event):



Design view of the subform w/ On Current event:



Design view of the main form:



What I'm trying to accomplish is to fire the subform's On Current event, based on which record they expand, that changes the subsubform's source object to one of the three forms (5, 10 or 15 rows) and displays the cooresponding form.

So, for visual purposes, I would want the following expanded record to show 10 rows (instead of 5):



However, because my cursor is not in that same record, it's not firing the On Current event that updates the subsubform's source object to the correct form.

 

[spikepl]

Hrmpf - do I really really need to eat my hat? Could you please attach a stripped down example? I'd like to play with this (but can first do so much later).

 

[SkyCraw]

That was my exact mental reaction when this request was given to me

I've attached a stripped-down version of these forms. You will notice on the far right of the datasheet subform is a "text33" control; this is the field I'm basing my source object selection off of. The logic can be found in the OnCurrent event of the subform.

 

[SkyCraw]

Anyone have much luck with this yet?

 

[MarkK]

This crashes my Access (2007) when I try to interact with the sub-datasheet. I am not able to test it. I've decompiled and compact/repaired. Same outcome.

 

[SkyCraw]

Ah, my bad... It might be because I'm using Office 2010, so it's looking for 2010 VBA References.

I've attached the olb files here:

View attachment 2010 Refs.zip

Just put them in the following directory:

C:\Program Files (x86)\Microsoft Office\Office14

Not saying that will fix it, but since you're running 2007 you won't have these references.

 

[MarkK]

If I open a code window there are no broken references. It references Access 12.0 and Office 12.0, (and VBA and OLE)

 

[SkyCraw]

Weird... I can open it with no issues.

So, it just crashes? No error or anything?

 

[Cronk]

Mark,
I have no problem opening with A2007 and expanding subform.

 

[MarkK]

I can expand the sub-datasheet, but it opens scrolled all the way right. When I click the bottom scroll bar, Access crashes without an error being raised.

 

[Cronk]

Funny. I can expand the sub datasheet (to show 1st to 5th lines). The scroll bar is to the left and as almost 100% is displayed on my screen, the scroll bar is only good for a few millimetres but causes no crash for me. The parent sub form scroll bar is to the left and I can move it to show off screem fields to the right. (This is on a Windows 7 PC running Access 2007).


Back to the original OP's question, if I put a stop line on the OnCurrent event of
Test Quote Options Sub2, it triggers when the + is clicked to expand the record, and also when any of the lines is selected. I don't know if this answers that question.

 

[SkyCraw]

That might just do it, Cronk!

Does it show the corresponding subform? Meaning, when you put the stop line in the OnCurrent event, does it show 5, 10 or 15 lines based on the expression I have when you click the "+" regardless of what record your cursor is in?

If the answer's yes, then you have!

 

[The_Doc_Man]

I am at a site that blocks lots of downloads. My question is this (and is based on what I read in the thread): Are you trying to catch that event from a sub-form that is in datasheet view? If so, the class module for that sub -form isn't so easily acceptable because a datasheet view is TECHNICALLY a default form kept in a separate folder under MS Office's folder-tree (but don't ask me where.)

There are no controls on that default form. Fields, yes. Controls, no. I don't even begin to know how you would get to its class module, which is the place where you would have to be in order to accept an event from the sub-form.

Now, as to the OTHER part of this question... to expand to the right number of rows, you might need to experiment with the "CanGrow" property OR in the OnLoad event, have something count the number of rows, compute the needed size of that pop-up, and resize the form's Height. You'll have to play with that a bit because there is no display function that I have found that would tell you the required height (unlike reports in Print Preview mode or in Excel spreadsheets.)

 

[SkyCraw]

Funny. I can expand the sub datasheet (to show 1st to 5th lines). The scroll bar is to the left and as almost 100% is displayed on my screen, the scroll bar is only good for a few millimetres but causes no crash for me. The parent sub form scroll bar is to the left and I can move it to show off screem fields to the right. (This is on a Windows 7 PC running Access 2007).


Back to the original OP's question, if I put a stop line on the OnCurrent event of
Test Quote Options Sub2, it triggers when the + is clicked to expand the record, and also when any of the lines is selected. I don't know if this answers that question.

Cronk, I just had the chance to try your suggestion and I still have to click in the same record I want to expand (click the "+" sign) in order for the OnCurrent even to fire properly.

However... I might be putting the Stop line in the wrong place too...

Here's what I'm doing:

    If Me.Text33 = 1 Then
        Me.Test_Quote_Options_Sub2.SourceObject = "Test Quote Options Sub2 - 5 Rows"
    ElseIf Me.Text33 = 2 Then
        Me.Test_Quote_Options_Sub2.SourceObject = "Test Quote Options Sub2 - 10 Rows"
    ElseIf Me.Text33 = 3 Then
        Me.Test_Quote_Options_Sub2.SourceObject = "Test Quote Options Sub2 - 15 Rows"
    Else
        Stop
    End If