Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Oncurrent not fired from clicking the "+" sign of subdatasheet

Posted on 2006-04-11
Medium Priority
Last Modified: 2012-06-21
I have a subdatasheet that changes depending on the current record. This works fine as long as the user clicks the record selector first, then the "+" sign. If the user just clicks the "+" sign, then it doesn't fire oncurrent... therefor, not changing my subdatasheet properly.

I have read that selecting the "+" sign doesn't change the current record selected... i think this is my main challenge.

Any ideas?
Thank in advance,
Question by:a260148
  • 3
  • 3
  • 2
  • +1
LVL 58

Expert Comment

ID: 16427250

This is right, as far as I know, there are no "diplay" events, such as Form_Scroll, Form_SubdatasheetXX, etc. You will have to devise a single multii-purpose subdatasheet.

If you explain why and how you change it, we might find a single query that does it all...


Author Comment

ID: 16427294
i am displaying a list of items and depending on the item type...

"Kit Type" - display a Bill Of Materials subdatasheet
"All Else" - display a "Where Used" subdatasheet... which then has another subdatasheet wich shows the Bill Of Materials for each of those

LVL 58

Expert Comment

ID: 16428479
Ah, I see.

In that case, there is no way that I can imagine. Or perhaps...

"Kit Type" - display a one-record dummy "where used", with Bill of Material as sub-sub-datasheet
"All Else" - display a real "where used" sub-datasheet, with identical sub-sub-datasheet...

Not what you wanted, though.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 16484104

Why not use a Main form and two sub-forms?
(Instead of a Main table and two possible Subdatasheets)

One subform for "Kit Type" (Bill Of Materials) and one for "All else" (where used)

Remember also, that if you use the wizard to create a Main/Subform 3 levels deep, it will code it so that clicking on a record in the subform will refresh the records on the sub-"sub" form.

Might this be another option?

Author Comment

ID: 16486741
I like using the subdatasheets because the users are very particular and are used to using Excel. I want to keep it as much like Excel as possible. (i know... silly). What would using the wizard do that I didn't do? I use master and child links?
LVL 58

Expert Comment

ID: 16487081
> What would using the wizard do that I didn't do?

Nothing. boag2000 was referring exactly to those: the link master & child fields. If you create them "manually", so much the better. But a form with subform will not have the "Excel look" you seek.


LVL 74

Accepted Solution

Jeffrey Coachman earned 750 total points
ID: 16489679
harfang, a260148,

Yes, the wizard does make link.

But, I was refering to the Sub-Sub Form. (Or in your case, the sub-sub datasheet)

For example, If you have the standard Northwind setup (Customers-->Orders-->OrderDetails)
The Man form will have two subforms.

On the Current Event of the First subform (Orders) the wizard places code to "Requery" the records in the Sub-SubForm (OrderDetails)

This way selecting an Order on the subform (Orders), will display the corresponding records in the sub-sub form (OrderDetails).

Like so:

Sub Form_Current()

    Dim ParentDocName As String

    On Error Resume Next
    ParentDocName = Me.Parent.Name

    If Err <> 0 Then
        GoTo Form_Current_Exit
        On Error GoTo Form_Current_Err
        Me.Parent![frmOrderDetails Subform1].Requery
    End If

    Exit Sub

    MsgBox Error$
    Resume Form_Current_Exit

End Sub

But like harfang states, it works but, this whole setup will not have the "Excel Look" that you are after.

I was just offering it as another alternative, in case you could not find another solution.
LVL 38

Expert Comment

ID: 16736732
In fact, Allen Browne recommends that subdatasheetname property be turned off due to a flaw, which may impact performance or cause other unintended consequences.  
See  this link (third topic from the bottom of the web page):


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17063637
Glad I could help

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question