By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,500 Members | 2,809 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,500 IT Pros & Developers. It's quick & easy.

find records in a sub sub form

P: 9
i have a mainform (MainInvoice) and it has a sub form (InvoiceDetailForm). Subform (Invoice Detail has a subform also(tblInvoiceDetail subform)

tblInvoiceDetail subform has the summary detail of InvoiceDetailForm.

I want to view the records in tblInvoiceDetail and show it in InvoiceDetailform.

Please help
Jan 27 '14 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 5K+
P: 5,397

So, is the issue the fact that you do not know how to accomplish this task, or is it that you've attempted this task with a parent/child form setup and that the forms are not working as expected?

The basic methodology is given here:
Create a form that contains a subform (a one-to-many form) ACC2007 You've not indicated which version of Access you are using; however, this basic method hasn't changed since ACC1997.
Jan 27 '14 #2

P: 9
i used 2003 access.

Mainform is the header of invoice, inside the mainform there is a subform InvoiceDetailForm.
InvoiceDetailform is the Detail of Invoice and it has also a subform tblInvoiceDetail subform. tblInvoicedetail is the summary of InvoiceDetailForm
each data in tblInvoiceDetail subform has a ID(unique) and if i click that ID i want the data display in InvoiceDetailForm

Mainform link by InvoiceNumber to subform InvoiceDetailForm
InvoiceDetailForm link also by InvoiceNumber to subform tblInvoiceDetail subform.

please tell me how to show the data in InvoiceDetailForm once i click the Id in tblinvoiceDetail subform.

i used this code but it doesn't work out

Expand|Select|Wrap|Line Numbers
  1. Private Sub No_Click()
  2.    DoCmd.SearchForRecord acDataForm, _
  3.       "InvoiceDetailForm", _
  4.       acFirst, _
  5.       "[No] = " & Me!No
  6. End Sub
Jan 28 '14 #3

P: 9
i also try this one
but nothings found.

Expand|Select|Wrap|Line Numbers
  1. Private Sub No_Click()
  2.    Dim rst As Recordset
  3.    If Not IsNull(Me.No) Then
  4.       Set rst = _
  5.       Forms!MainInvoice!InvoiceDetailFom.Form.RecordsetClone
  6.       rst.FindFirst "Id = " & Me.Id
  7.       If Not rst.NoMatch Then
  8.          Forms!MainInvoice!InvoiceDetailFom.Form.Bookmark _
  9.             = rst.Bookmark
  10.         Else
  11.             MsgBox "Not Found!"
  12.       End If
  13.       If Me.No = Null Then
  14.          DoCmd.CancelEvent
  15.       End If
  16.    End If
  17.    rst.Close
  18.    Set rst = Nothing
  19. End Sub
Jan 28 '14 #4

Expert Mod 5K+
P: 5,397
1) You are making this WAY TOO difficult

2) Follow the link I gave you in my first reply it will show you step by step just how to accomplish what you want.
2a) You shouldn't need any VBA at all for a subform/form relationship unless doing late binding or something to the filter in the parent form
3) Please use [code] [/code] to enclose your script, these tags come from the [CODE/] button on the formatting tool bar.

4) I'm going to attempt to make sense of what you posted in Post#3
  • Form 1: named "Invoice"
  • Form 2: Subform1: named "InvoiceDetailForm"
  • Form 3: Subform2: named "tblInvoiceDetail Subform"
all forms are bound to a recordset in some way:

Form 2 is child to Form 1
Form 2 is in the details section of Form 1
Form 2 and form Form 1 are linked by field [InvoiceNumber]
Form 3 is child to Form 2 (thus grandchild to Form 1)
Form 3 is in the details section of Form 2
Form 3 and form Form 2 are linked by field [InvoiceNumber]
Please, making some logical groupings instead of running everything togeither in on massive text post will help with understanding what you attempting.

each data in tblInvoiceDetail subform has a ID(unique) and if i click that ID i want the data display in InvoiceDetailForm
You do not normally select a record in the child form and force the parent. Instead, the parent filters out the related records in the child. Thus, what you are attempting here is illogical. In fact, if properly done: Form 1, Form 2, and Form 3 if showing the [InvoiceNumber] field in each, would have the same value shown for the [InvoiceNumber] field.

Furthermore, as you move thru the records at the topmost level of the form structure, the subforms should be updating to reflect the related records in their recordsets (and the same should be true for the grandchild to to child; however, because the grandchild and child share a field with the parent for linking, there is something amiss with what you are attempting).

If your Database Normalization and Table Structures (click here) are properly setup then you shouldn't need to have such a complex form. Indeed the second subform shouldn't be required at all - you need to go back an examine the database structure.

Think about this for a simplification: Because both Form 2 and Form 3 appear to use the same [InvoiceNumber] field as the parent Form 1 to link on, then you should be able to pull form 3 out of form 2 directly to Form 1 to simplfy the design.

This fact should have been a red-flag alerting you to the fact that something isn't optimal and possibly amiss in the overall database design.

5) I'm going to ignore post #4 for the time being as there are other more serious issues with your database and form design that make the posted code there a loss-lead.
Jan 28 '14 #5

P: 9
Hi zmdb

Thank you for the reply

-form 2 and form 3 is the same
-form 3 is the datasheet of form 2
Jan 28 '14 #6

Expert Mod 5K+
P: 5,397
-form 2 and form 3 is the same
-form 3 is the datasheet of form 2
So are you using the "Split Form" (click Here) for "Form2/3"
This will not work as a split form will not display correctly as a subform.


Do you mean to say...

Form 2: In form view
Form 3: is a copy of Form 2 in datatable view

You have imbeded Form 3 into Form 2 in an attempt to emulate the split form functionality?
Jan 28 '14 #7

P: 9

Form 2 in form view
Form 3 in datatable view

i imbedded form 3 into form 2
Jan 29 '14 #8

Expert Mod 5K+
P: 5,397
This will take quite the high level of VBA programing to accomplish.

In ACC2003, the only way I found to do something like this was with CustomEvents and that appears to really not have changed with the advent of the ACC2007/2010 split form.

If you have a VERY high level of understanding of VBA then this can be accomplished; however, I would otherwise reconsider the interface.
Jan 29 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.