Use subform on multiple forms? (1 Viewer)

[Lissa]

Hi Everyone!
I have a question about using a subform on multiple subforms.
I have a main form that contains a command button that calls a form that contains a "pick" list of entries. When the user double clicks on one of the entries, the entry is entered into a table. The main form then has a subform that displays all entries that the user has selected. I got this to work perfectly... the requery statement at the end ensures that the user can see the entry from the main form immediately.
I would like to use the same form that contains the "pick" list entries and the subform that displays the entries on different forms. When I tested it out it errors out on the requery statement - which is what I expected since the form name is different.
How can I reuse the form without having to copy the form multiple times just to change one line (requery statement)?
Thanks!

code from the "pick" list of entries that user can select to add to a table...

Private Sub ListNMR_DblClick(Cancel As Integer)
Dim strSQL As String
Dim NMR As String
Dim Item As String
Dim Description As String
Dim ChgDT As Date
Dim key As Integer
NMR = Me.ListNMR.Column(0)
Item = Me.ListNMR.Column(1)
Description = Me.ListNMR.Column(2)
strSQL = "INSERT INTO tblNMR_HISTORY (PartStatusId, NCRNumber, ItemName, DiscrepancyDescription, Changed_Time) VALUES (" & NMRKey & ", '" & NMR & "', '" & Item & "','" & Description & "', # " & ChgDT & "#);"
'MsgBox "SQL:" & strSQL
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close

Forms!frmTestStatus!sbfNMRHistory.Form.Requery
End Sub

 

[Galaxiom]

I'm not clear on where you are running the code.
If it is on the mainform then just refer to the main form as Me!
Me!sbfNMRHistory.Form.Requery

If it is running on one subform and you want to requery the other use:
Me.Parent!othersubform.Form.Requery

On every main form ensure you always use the same name for the subform control.

 

[boblarson]

Also, remember you want to refer to the subform CONTAINER on the main form (control on the main form which houses the subform) and NOT the subform name itself (unless the container and the subform are named the same, which is okay to do).

So with Galaxiom's: Me!sbfNMRHistory.Form.Requery the sbfNMRHistory is the name of the subform CONTAINER and not the subform itself (of course unless the container and the subform are both named sbfNMRHistory).

 

[Lissa]

Let me explain again...
My main form called frmTestStatus contains a command button that the user clicks to bring up another form called sbfAdd_NMR.
1) sbfAdd_NMR does not reside on frmTestStatus
2) sbfAdd_NMR is a form that contains a list box that contains entries the user can select from.
3) User selects entry and the entry is inserted into a table called tblNMR_History.. then sbfAdd_NMR is closed... then once it is closed it requeries a subform on frmTestStatus.
On frmTestStatus there is a subform (sbfNMRHistory) that DISPLAYS the entries that the user has selected. The code provided from my first post was the code from the OnDoubleClick event of sbfAdd_NMR. There is no problem with this code - it works fine.
I have a series of other forms that I need to place the need to allow the user to select entries and display what they have selected. I would like to reuse the forms I have created (sbfAdd_NMR and sbfNMRHistory) but using it on other forms causes an error because of the statement Forms!frmTestStatus!sbfNMRHistory.Form.Requery.
I want to avoid having to create multiple copies of sbfAdd_NMR just to change one line of code....

 

[boblarson]

When you open the new form, use OpenArgs and pass the current form to it so you can use that later.

So, when opening the form use:

Docmd.OpenForm "sbfAdd_NMR",acNormal, , , , ,Me.Name

and then in the close event of the sbfAdd_NMR, you can use:

Forms(Me.OpenArgs).sbfNMRHistory.Form.Requery

and that will requery sbfNMRHistory in whatever form you have it on when you opened sbfAdd_NMR.

 

[boblarson]

Also, you should be wary of your use of the term "SUBFORM." sbfNMRHistory IS a subform. sbfAdd_NMR is NOT. sbfAdd_NMR is just another form. It is not a subform.

 

[Lissa]

Hi Bob -
I tried adding the OpenArgs statements but I seem to be getting Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist
when it gets to the requery statement of sbfAdd_NMR. (Haven't changed the name to frmAdd_NMR yet). The frmTestStatus remains open when I open sbfAdd_NMR.
I don't get what I"m missing...

Thanks

 

[boblarson]

You might need to add a variable as OpenArgs may not be available at the time you need them. Try adding a

Private strOA As String

in the Declarations section of the code for sbfAdd_NMR and then in the Open event of that form use:

strOA = Me.OpenArgs

And then modify the close code to:

Forms(strOA).sbfNMRHistory.Form.Requery

 

[Lissa]

Sorry for the late response about the outcome - in and out of meetings....
but adding the variable worked!!! Thanks Bob!! This saves me alot of extra work!

 

[Lissa]

Sorry for the late response about the outcome - in and out of meetings....
but adding the variable worked!!! Thanks Bob!! This saves me alot of extra work!

 

[Lissa]

Sorry for the late response about the outcome - in and out of meetings....
but adding the variable worked!!! Thanks Bob!! This saves me alot of extra work!

 

[boblarson]

Sorry for the late response about the outcome - in and out of meetings....
but adding the variable worked!!! Thanks Bob!! This saves me alot of extra work!

Sure thing

 

[Galaxiom]

Also, remember you want to refer to the subform CONTAINER on the main form (control on the main form which houses the subform) and NOT the subform name itself (unless the container and the subform are named the same, which is okay to do).

So with Galaxiom's: Me!sbfNMRHistory.Form.Requery the sbfNMRHistory is the name of the subform CONTAINER and not the subform itself (of course unless the container and the subform are both named sbfNMRHistory).

Bob clarifies a frequent misunderstanding where a subform's Form Object is confused with the Subform Control whose Source Object is that form.

However, despite its common use, I prefer not to refer to the Subform Control as a "Container" because this term already has a specific meaning in Access. A Container is a higher level object. "Forms", "Tables", "Relationships", "Modules" etc are Containers.

A Subform Control (the correct term as used in my post) is not a Container. You will not find a Containers collection in the properties of a form.

We discourage the use of the word "field" when refering to a Textbox Control on a Form because Fields are in Tables. Likewise we should discourage the use of the word Container when what is meant is Subform Control.

 

[boblarson]

Subform Control.

And I avoid that term because it is easy to misunderstand that it is a control on the subform instead of the control that houses the subform.

I've never seen the use of the word CONTAINER as you've described it. I've seen that in Word, but not in Access. In Access there is the Forms Collection, the Queries Collection, the Tables Collection, but I've never seen a Containers. So, I'm probably going to stick with that description unless you can show me valid evidence to do otherwise. It is easier for someone to think of a container holding a subform than using subform control.

In fact, all of the places and forums I've ever been, I've never seen the word CONTAINER used to refer to a collection in Access.

 

[Galaxiom]

I'm probably going to stick with that description unless you can show me valid evidence to do otherwise.

http://msdn.microsoft.com/en-us/library/bb177452.aspx

Run this code to see a list of Containers in the current database.

For x = 0 To CurrentDb.Containers.Count - 1: Debug.Print CurrentDb.Containers(x).Name: Next x

 

[Galaxiom]

It is easier for someone to think of a container holding a subform than using subform control.

Control is the word to use because that is their name. It is a very important word in Access even though controls don't actually "control" anything in the normal non-computer sense of the term. People often incorrectly refer to textbox controls as "fields" because they don't relate to the term "control" for this reason.

The less we use the word "control" in its proper place, the less people will relate to it and the more they will substitute inappropriate terms. We should encourage the use of the correct term. Substituting another word that already has a defined meaning in the same context is definitely a mistake.

We are told to refer to genitals by their proper name when speaking to infants. I don't see why we should treat "Subform Control" any differently when speaking to adults.

Perhaps you could refer to "the Subform Control that encloses the Form" since AFIK "enclose" is not altready used in Access or VBA.

However I still prefer "the Subform Control whose Source Object is the Form". It is absolutely unambiguous because all terms in the phrase have well defined meanings.

 

[Galaxiom]

In Access there is the Forms Collection, the Queries Collection, the Tables Collection, but I've never seen a Containers.

The difference between the Forms Container and the Forms Collection is subtle.

All forms are held in the Forms Container.
The Forms Collection refers only to the Open Forms.

http://msdn.microsoft.com/en-us/library/aa210667(office.11).aspx

Similarly, biscuits are held in the Biscuit Tin (ie Container).
They join the Biscuits Collection when taken out of the tin and put on the table.

Taking the analogy to the ridiculous. The Lemon Filling is held in the Filling Control on the LemonCreme Biscuit.

 

[boblarson]

I'll be using this screenshot to help explain when I want to:

 

[Galaxiom]

You can't make it much clear than that, Bob. They say a picture paints a thousand words.