select record based on record selected in the child form

S

SuzyQ

I have an invoice form which has a line item subform. The line item subform
has a subform which is a list of all of the line items of the invoice form.
So...
frmInvoice has subform frmInvoiceLineAddSubform (in form view) based on
invoiceID
frmInvoiceLineAddSubform has a subform frmInvoiceLineItemListSubSub (in
datasheet view) based on InvoiceID

If the user selects an item in the list from frmInvoiceLineItemListSubSub, I
want to change the record on frmInvoiceLineAddSubform base on LineItemID

I tried this which gives an error "You canceled the previous operation"
#2001 so I commented it out. How can I select a record in a parent form
based on the record selected in the child form?

Private Sub Form_Current()
' Dim rs As Object

' Set rs = Me.Parent.Recordset.Clone
' rs.FindFirst "[LineItemID] = " & Str(Me![LineItemID])
' Me.Parent.Bookmark = rs.Bookmark
End Sub
 
S

SuzyQ

thanks:) I should be able to use the simple filter version. I'll try on
Monday and let you know.

Jeanette Cunningham said:
Hi SuzyQ,
try this link for filtering a parent form based on a selection on a child
form
http://allenbrowne.com/ser-28.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

SuzyQ said:
I have an invoice form which has a line item subform. The line item
subform
has a subform which is a list of all of the line items of the invoice
form.
So...
frmInvoice has subform frmInvoiceLineAddSubform (in form view) based on
invoiceID
frmInvoiceLineAddSubform has a subform frmInvoiceLineItemListSubSub (in
datasheet view) based on InvoiceID

If the user selects an item in the list from frmInvoiceLineItemListSubSub,
I
want to change the record on frmInvoiceLineAddSubform base on LineItemID

I tried this which gives an error "You canceled the previous operation"
#2001 so I commented it out. How can I select a record in a parent form
based on the record selected in the child form?

Private Sub Form_Current()
' Dim rs As Object

' Set rs = Me.Parent.Recordset.Clone
' rs.FindFirst "[LineItemID] = " & Str(Me![LineItemID])
' Me.Parent.Bookmark = rs.Bookmark
End Sub
Click to expand...
Click to expand...
 
S

SuzyQ

At a quick glance I though I could use the simple method, but I didn't read
it close enough. The code gave me some ideas, but I'm still having problems.

Here are the particulars...

The Parent form and subform use the same table as the record source.
tblInvoiceLineItems - there is actually a parent to the parent that uses the
table tblInvoice but that is irrelevant to this scenario (I think)

In the subParent I have it set up to actually edit/add line items. Upon
refresh of the form the line item appears in the child form (which is in
datasheet view). If the user selects a record on the datasheet, I want to
filter it's parent to show that record for editing. I have the following
code in the child's on current event. It was working ok except that when the
parent record was filtered, the child records reset and the pointer was no
longer on the field that is displayed on the editing portion of the form (the
subParent). I added code to save the current record and return to the record
after the subParent form was filtered, but now I get a message that access
cannot open any more tables or queries

How can I save the current record on the datasheet (subform) to return to
that record once the parent's record has changed?

Private Sub Form_Current()

'ADDED TO SAVE CURRENT REC
Dim CR As Long
Dim rs As Object
CR = Me.LineItemID 'save current line item id

Set rs = Me.Recordset.Clone 'line item list record set 'Error on this
line - cannot open any more tables or queries
'END ADDED TO SAVE CURRENT REC - PART ONE

'THIS PORTION OF CODE WORKS TO FILTER THE PARENT FORM
'set parent filter to current line item
Me.Parent.txtLineControl = Me.LineItemID
Me.Parent.Filter = "LineItemID = " & Me.LineItemID
Me.Parent.FilterOn = True
' THIS CODE ALONE WORKS EXCEPT THAT THE CHILD RECORD CHANGES ONCE THE PARENT
FILTER IS SET


'ADDED TO SAVE CURRENT REC - PART TWO
'go back to current line item (setting parent filter changed record in
this child)
rs.FindFirst "[LineItemID] = " & CR
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
'END ADDED TO SAVE CURRENT REC
End Sub







Jeanette Cunningham said:
Hi SuzyQ,
try this link for filtering a parent form based on a selection on a child
form
http://allenbrowne.com/ser-28.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

SuzyQ said:
I have an invoice form which has a line item subform. The line item
subform
has a subform which is a list of all of the line items of the invoice
form.
So...
frmInvoice has subform frmInvoiceLineAddSubform (in form view) based on
invoiceID
frmInvoiceLineAddSubform has a subform frmInvoiceLineItemListSubSub (in
datasheet view) based on InvoiceID

If the user selects an item in the list from frmInvoiceLineItemListSubSub,
I
want to change the record on frmInvoiceLineAddSubform base on LineItemID

I tried this which gives an error "You canceled the previous operation"
#2001 so I commented it out. How can I select a record in a parent form
based on the record selected in the child form?

Private Sub Form_Current()
' Dim rs As Object

' Set rs = Me.Parent.Recordset.Clone
' rs.FindFirst "[LineItemID] = " & Str(Me![LineItemID])
' Me.Parent.Bookmark = rs.Bookmark
End Sub
Click to expand...
Click to expand...
 
S

SuzyQ

I got it now. I took the code out of on current and put it into double-click
and it works perfect. The user will just have to double click on the record
they want to edut.

SuzyQ said:
At a quick glance I though I could use the simple method, but I didn't read
it close enough. The code gave me some ideas, but I'm still having problems.

Here are the particulars...

The Parent form and subform use the same table as the record source.
tblInvoiceLineItems - there is actually a parent to the parent that uses the
table tblInvoice but that is irrelevant to this scenario (I think)

In the subParent I have it set up to actually edit/add line items. Upon
refresh of the form the line item appears in the child form (which is in
datasheet view). If the user selects a record on the datasheet, I want to
filter it's parent to show that record for editing. I have the following
code in the child's on current event. It was working ok except that when the
parent record was filtered, the child records reset and the pointer was no
longer on the field that is displayed on the editing portion of the form (the
subParent). I added code to save the current record and return to the record
after the subParent form was filtered, but now I get a message that access
cannot open any more tables or queries

How can I save the current record on the datasheet (subform) to return to
that record once the parent's record has changed?

Private Sub Form_Current()

'ADDED TO SAVE CURRENT REC
Dim CR As Long
Dim rs As Object
CR = Me.LineItemID 'save current line item id

Set rs = Me.Recordset.Clone 'line item list record set 'Error on this
line - cannot open any more tables or queries
'END ADDED TO SAVE CURRENT REC - PART ONE

'THIS PORTION OF CODE WORKS TO FILTER THE PARENT FORM
'set parent filter to current line item
Me.Parent.txtLineControl = Me.LineItemID
Me.Parent.Filter = "LineItemID = " & Me.LineItemID
Me.Parent.FilterOn = True
' THIS CODE ALONE WORKS EXCEPT THAT THE CHILD RECORD CHANGES ONCE THE PARENT
FILTER IS SET


'ADDED TO SAVE CURRENT REC - PART TWO
'go back to current line item (setting parent filter changed record in
this child)
rs.FindFirst "[LineItemID] = " & CR
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
'END ADDED TO SAVE CURRENT REC
End Sub







Jeanette Cunningham said:
Hi SuzyQ,
try this link for filtering a parent form based on a selection on a child
form
http://allenbrowne.com/ser-28.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

SuzyQ said:
I have an invoice form which has a line item subform. The line item
subform
has a subform which is a list of all of the line items of the invoice
form.
So...
frmInvoice has subform frmInvoiceLineAddSubform (in form view) based on
invoiceID
frmInvoiceLineAddSubform has a subform frmInvoiceLineItemListSubSub (in
datasheet view) based on InvoiceID

If the user selects an item in the list from frmInvoiceLineItemListSubSub,
I
want to change the record on frmInvoiceLineAddSubform base on LineItemID

I tried this which gives an error "You canceled the previous operation"
#2001 so I commented it out. How can I select a record in a parent form
based on the record selected in the child form?

Private Sub Form_Current()
' Dim rs As Object

' Set rs = Me.Parent.Recordset.Clone
' rs.FindFirst "[LineItemID] = " & Str(Me![LineItemID])
' Me.Parent.Bookmark = rs.Bookmark
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Naming of Form Button 3
DLookup in Continuous form 0
find record in main form then find record in the subform 7
After update find record on subform 5
Locate record in a subform 0
Creating a Chart on form from selected data in query. 0
Subform Select record 1
Problem refreshing child form 1

Top