Updating subform records after closing popup form

[Macbane]

Hi,

I have a 'main' form called frmIssues which has a subform control
(named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs.
A control button on the main form opens a pop-up form which allows me
to edit the record in the subform. What I want to happen is for
subform with the new edits to be updated on the main form when I close
the popup. I'm sure this is a very small bit of code in the the 'On
close' event for the popup form, but I can't work it out. Any
guidance please!

Thanks in advance,

MacBane

[salad]

Macbane wrote:

Hi,

I have a 'main' form called frmIssues which has a subform control
(named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs.
A control button on the main form opens a pop-up form which allows me
to edit the record in the subform. What I want to happen is for
subform with the new edits to be updated on the main form when I close
the popup. I'm sure this is a very small bit of code in the the 'On
close' event for the popup form, but I can't work it out. Any
guidance please!

Thanks in advance,

MacBane

Could you provide some more information? You have a
form/subform/subsubform. The popup is to update which suform; sub or
subsub?

Forms can be bound (a table or query) or unbound (no table or query).
In the main form's forms are they bound and the popup unbound? If both
the form and popup are bound, the results should update correctly
without going thru any jumps thru hoops.

[Macbane]

On Jan 31, 5:18 pm, salad <[e-mail]:

Macbane wrote:

Hi,

I have a 'main' form called frmIssues which has a subform control
(named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs.
A control button on the main form opens a pop-up form which allows me
to edit the record in the subform. What I want to happen is for
subform with the new edits to be updated on the main form when I close
the popup. I'm sure this is a very small bit of code in the the 'On
close' event for the popup form, but I can't work it out. Any
guidance please!

Thanks in advance,

MacBane

Could you provide some more information? You have a
form/subform/subsubform. The popup is to update which suform; sub or
subsub?

Forms can be bound (a table or query) or unbound (no table or query).
In the main form's forms are they bound and the popup unbound? If both
the form and popup are bound, the results should update correctly
without going thru any jumps thru hoops.- Hide quoted text -

- Show quoted text -

Hi,

OK, probably over complicated it and under-explained it. Right, I
have a main form frmIssus that has 1 subform called frmDrugs which is
linked using the ID field IssuesID (Mentioning the subform control
might have suggested that the subform has a subform but it doesn't).
Both forms are bound to SQL statements based on a combination of the
linked tables tblIssues, tblDrugs and tblLinkIssuesDrugs (Many-to-many
relationship) Fairly straight forward so far until I got a bit fancy
and just didn't like the look of the subform. What I did was to
change the format of the subform so that it just displays the subform
field (Drugname) but you cannot edit or add (changed combo boxes to
text boxes, disallowed Edits and Additions) . To edit or add, I put a
button control on the main form beside the subform which opens a copy
of the subform (frmDrugsEdit) as a popup form. I can then edit or add
new data and I think it looks much better. What I want is a way to
requery the subform imbedded on the main form with the changes made on
the popup when I close the popup. Do I have to requery the whole main
form or can I 'requery' the 'imbedded' subform. I need a bit of code
in the On-close event for the popup form. Any ideas? God help me if
this doesn't make sense.

Thanks,

Macbane

[salad]

Macbane wrote:

On Jan 31, 5:18 pm, salad <[e-mail]:

>>Macbane wrote:

>>>Hi,

>>>I have a 'main' form called frmIssues which has a subform control
(named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs.
A control button on the main form opens a pop-up form which allows me
to edit the record in the subform. What I want to happen is for
subform with the new edits to be updated on the main form when I close
the popup. I'm sure this is a very small bit of code in the the 'On
close' event for the popup form, but I can't work it out. Any
guidance please!

>>>Thanks in advance,

>>>MacBane

Could you provide some more information? You have a
form/subform/subsubform. The popup is to update which suform; sub or
subsub?

Forms can be bound (a table or query) or unbound (no table or query).
In the main form's forms are they bound and the popup unbound? If both
the form and popup are bound, the results should update correctly
without going thru any jumps thru hoops.- Hide quoted text -

- Show quoted text -

Hi,

OK, probably over complicated it and under-explained it. Right, I
have a main form frmIssus that has 1 subform called frmDrugs which is
linked using the ID field IssuesID (Mentioning the subform control
might have suggested that the subform has a subform but it doesn't).
Both forms are bound to SQL statements based on a combination of the
linked tables tblIssues, tblDrugs and tblLinkIssuesDrugs (Many-to-many
relationship) Fairly straight forward so far until I got a bit fancy
and just didn't like the look of the subform. What I did was to
change the format of the subform so that it just displays the subform
field (Drugname) but you cannot edit or add (changed combo boxes to
text boxes, disallowed Edits and Additions) . To edit or add, I put a
button control on the main form beside the subform which opens a copy
of the subform (frmDrugsEdit) as a popup form. I can then edit or add
new data and I think it looks much better. What I want is a way to
requery the subform imbedded on the main form with the changes made on
the popup when I close the popup. Do I have to requery the whole main
form or can I 'requery' the 'imbedded' subform. I need a bit of code
in the On-close event for the popup form. Any ideas? God help me if
this doesn't make sense.

Thanks,

Macbane

I'm not sure if my solution will help, maybe you can glean something
from it that will help your situation.

If I had a form/subform that was bound (which I believe is your case)
and I called another form with something like
Docmd.Openform "UpdateIt",,,"ID = " & Me.Subform!ID,acdialog
I'd expect the dialog form to open to my current record and if I
modifyied/saved the record in the dialog the data would be updated on
the main subform.

You are right. You may have to save it and requery. In your
declarations you might declare a variable to hold your ID number of the
record you are editting.
Dim lngID As Long

In the BeforeUpdate event of the dialog you might enter
lngID = Me.ID

And in the OnClose event you might enter
Dim rst As DAO.Recordset

'requery the subform
Forms!MainForm!SubForm.Form.Requery

'now find the record you added/modified
set rst = Forms!MainForm!SubForm.Form.RecordsetClone
rst.findfirst "ID = " & lngID

If not rst.nomatch then
'it was found. move to it.
Forms!MainForm!SubForm.Form.BookMark = rst.Bookmark
Endif
rst.close
set rst = nothing

Hope this helps.

[Macbane]

On Feb 1, 3:19 pm, salad <[e-mail]:

Macbane wrote:

On Jan 31, 5:18 pm, salad <[e-mail]:

>Macbane wrote:

>>Hi,

>>I have a 'main' form called frmIssues which has a subform control
(named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs.
A control button on the main form opens a pop-up form which allows me
to edit the record in the subform. What I want to happen is for
subform with the new edits to be updated on the main form when I close
the popup. I'm sure this is a very small bit of code in the the 'On
close' event for the popup form, but I can't work it out. Any
guidance please!

>>Thanks in advance,

>>MacBane

>Could you provide some more information? You have a
form/subform/subsubform. The popup is to update which suform; sub or
subsub?

>Forms can be bound (a table or query) or unbound (no table or query).
In the main form's forms are they bound and the popup unbound? If both
the form and popup are bound, the results should update correctly
without going thru any jumps thru hoops.- Hide quoted text -

>- Show quoted text -

Hi,

OK, probably over complicated it and under-explained it. Right, I
have a main form frmIssus that has 1 subform called frmDrugs which is
linked using the ID field IssuesID (Mentioning the subform control
might have suggested that the subform has a subform but it doesn't).
Both forms are bound to SQL statements based on a combination of the
linked tables tblIssues, tblDrugs and tblLinkIssuesDrugs (Many-to-many
relationship) Fairly straight forward so far until I got a bit fancy
and just didn't like the look of the subform. What I did was to
change the format of the subform so that it just displays the subform
field (Drugname) but you cannot edit or add (changed combo boxes to
text boxes, disallowed Edits and Additions) . To edit or add, I put a
button control on the main form beside the subform which opens a copy
of the subform (frmDrugsEdit) as a popup form. I can then edit or add
new data and I think it looks much better. What I want is a way to
requery the subform imbedded on the main form with the changes made on
the popup when I close the popup. Do I have to requery the whole main
form or can I 'requery' the 'imbedded' subform. I need a bit of code
in the On-close event for the popup form. Any ideas? God help me if
this doesn't make sense.

Thanks,

Macbane

I'm not sure if my solution will help, maybe you can glean something
from it that will help your situation.

If I had a form/subform that was bound (which I believe is your case)
and I called another form with something like
Docmd.Openform "UpdateIt",,,"ID = " & Me.Subform!ID,acdialog
I'd expect the dialog form to open to my current record and if I
modifyied/saved the record in the dialog the data would be updated on
the main subform.

You are right. You may have to save it and requery. In your
declarations you might declare a variable to hold your ID number of the
record you are editting.
Dim lngID As Long

In the BeforeUpdate event of the dialog you might enter
lngID = Me.ID

And in the OnClose event you might enter
Dim rst As DAO.Recordset

'requery the subform
Forms!MainForm!SubForm.Form.Requery

'now find the record you added/modified
set rst = Forms!MainForm!SubForm.Form.RecordsetClone
rst.findfirst "ID = " & lngID

If not rst.nomatch then
'it was found. move to it.
Forms!MainForm!SubForm.Form.BookMark = rst.Bookmark
Endif
rst.close
set rst = nothing

Hope this helps.- Hide quoted text -

- Show quoted text -

Thanks Salad. I had a problem with referring to my subforms but your
advice & instructions on mvps.org (http://mvps.org/access/forms/
frm0031.htm) got me sorted. Highly recommeded reading. Thanks again

Macbane

PS Best of luck flogging your new app!