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

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,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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...

a260148Author Commented:
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

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:

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?
a260148Author Commented:
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?
> 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.


Jeffrey CoachmanMIS LiasonCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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):


Jeffrey CoachmanMIS LiasonCommented:
Glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.