Set listbox rowsource from collection - form won't stay open

[Anita]

I have a "Last Updated" subform that is embedded in several data entry forms.
In case a user has several data entry forms open at once, I need to capture
which particular data entry form is being updated so that the Last Updated
info is correctly assigned to the right record.

My idea was to create a collection that captures the name of all the open
data entry forms, populate a list box with this collection and allow the user
to manually pick the right form name.

I got the collection to work, but when I try to open "frmSelectParentForm"
and populate it using the .Rowsource method, my code starts breaking.

The main problem I'm encountering is that frmSelectParentForm will not stay
open -- despite the fact that I haven't written any code to close it. After
the subroutines run that populate the listbox's rowsource with values, the
form closes before I can select a value.

Any ideas for why this is occurring?


**********Main procedure******************
Public Function Store_Form_Name(Optional FormName As String)

Static MyParentFormName As String

If Not FormName = "" Then
MyParentFormName = FormName
ElseIf MyOpenFormsCollection.Count > 1 Then
DoCmd.OpenForm "frmSelectParentForm" ****opens subform that's closing

unexpectedly
Form_frmSelectParentForm.Populate_List_Box
MyParentFormName = "Need to select"
Else
MyParentFormName = MyOpenFormsCollection.Item(1)
End If

Store_Form_Name = MyParentFormName

End Function

***********Collection Function*****************************
Public Function MyOpenFormsCollection() As Collection

Dim counter As Integer
Dim colOpenFormNames As New Collection
Dim OpenFormName As Variant

Dim intForms As Integer
Dim frm As Form

intForms = Forms.Count ' Number of open forms.
If intForms > 0 Then
For counter = 0 To intForms - 1
Set frm = Forms(counter)

With colOpenFormNames
If Not (frm.Name = "frmSelectParentForm") Then
.Add Item:=frm.Name
End If
End With
Next counter
End If

Set MyOpenFormsCollection = colOpenFormNames

End Function

*********frmSelectParentForm .Rowsource subroutine**************
Public Sub Populate_List_Box()
'Created November 2006 - Currently in testing

Me.lboxMyOpenForms.RowSource = GetOpenFormNames
Me.lboxMyOpenForms.SetFocus
If Me.lboxMyOpenForms.ListIndex = -1 Then
MsgBox "Click the appropriate form.", , "Data Entry Form not selected"
Else
Call btnLast_Update_Source_OK_Click
End If

End Sub

*******GetOpenFormNames subroutine that finds the Rowsource values****

Public Function GetOpenFormNames()
Dim MyList As String
Dim OpenFormName As Variant

MyList = ""

For Each OpenFormName In MyOpenFormsCollection
MyList = MyList & OpenFormName & ";"
Next OpenFormName
GetOpenFormNames = MyList

End Function

 

[kingston via AccessMonster.com]

I think your problem stems from having multiple instances of the subform open
and trying to modify the subform's design while they're all open. I'm
probably missing something, but why are you trying to accomplish this through
forms? Couldn't you simply use a form or control's AfterUpdate event to
update a record in a LastUpdated table?

 

[Anita]

The problem is that my "last updated" event doesn't reside in a single table
or control -- it's actually a combination of records from two different
tables (Event table -- Last Updated is "event type" ; and a Notes tables --
stores names of different information sources). So a Last Updated event is
actually an Event ID and a Notes ID that's tied to a particular entity
through a three-prong join table (Entity ID, Event ID, Notes Id).

I set my database up to store all events in an event table (from last
updated to marketing tactics to meetings, phone calls, etc.), all contact
info (address, email, etc.) in a contacts table, all notes (from names of
information sources, to descriptions of marketing tactics to keywords to
"about this person" comments, etc.) in a notes table.

Did it this way on advice of a guy I know who is a database guru at his
company, but am finding it excruciating to construct queries, forms and
subforms that place all the right data in the various tables and join tables
- and even more excruciating coding and debugging everything.

Any thoughts?

Incidentally, my Last Updated subform is currently embedded in only one data
entry form (eventually it will be embedded in multiple forms, but for now,
it's only in one), so that can't be the reason it's closing on its own accord.

 

[kingston via AccessMonster.com]

When you say that "frmSelectParentForm will not stay open", do you mean that
the parent form closes when you try to reset the RowSource? It sounds like
you have the form open independently and as a subform.

Have you tried opening the subform independently along with other forms where
it is not embedded and running Populate_List_Box()? Put a break point on the
line Me.lboxMyOpenForms.RowSource = GetOpenFormNames. Check the value of
GetOpenFormNames at that point. If it is a valid string, I don't see why the
row source cannot be set.

Can you pinpoint where the "code starts breaking" if it is not at Me.
lboxMyOpenForms.RowSource = GetOpenFormNames?

The problem is that my "last updated" event doesn't reside in a single table
or control -- it's actually a combination of records from two different
tables (Event table -- Last Updated is "event type" ; and a Notes tables --
stores names of different information sources). So a Last Updated event is
actually an Event ID and a Notes ID that's tied to a particular entity
through a three-prong join table (Entity ID, Event ID, Notes Id).

I set my database up to store all events in an event table (from last
updated to marketing tactics to meetings, phone calls, etc.), all contact
info (address, email, etc.) in a contacts table, all notes (from names of
information sources, to descriptions of marketing tactics to keywords to
"about this person" comments, etc.) in a notes table.

Did it this way on advice of a guy I know who is a database guru at his
company, but am finding it excruciating to construct queries, forms and
subforms that place all the right data in the various tables and join tables
- and even more excruciating coding and debugging everything.

Any thoughts?

Incidentally, my Last Updated subform is currently embedded in only one data
entry form (eventually it will be embedded in multiple forms, but for now,
it's only in one), so that can't be the reason it's closing on its own accord.

I think your problem stems from having multiple instances of the subform open
and trying to modify the subform's design while they're all open. I'm

[quoted text clipped - 97 lines]

 

[Anita]

Kingston, boy do I feel stupid ... and I apologize for wasting your time.

Turns out my form was not closing on its own will at all. When I created the
form, I created it as a copy/paste of an existing form and forgot to change
the caption. So after my code would finish executing, I was completely
puzzled at why my form had mysteriously vanished.

Nothing was wrong with the code, and my form was in fact still there. It was
just showing up with the name of the original form I copied/pasted.

DUH!

Thanks so much for your help!

When you say that "frmSelectParentForm will not stay open", do you mean that
the parent form closes when you try to reset the RowSource? It sounds like
you have the form open independently and as a subform.

Have you tried opening the subform independently along with other forms where
it is not embedded and running Populate_List_Box()? Put a break point on the
line Me.lboxMyOpenForms.RowSource = GetOpenFormNames. Check the value of
GetOpenFormNames at that point. If it is a valid string, I don't see why the
row source cannot be set.

Can you pinpoint where the "code starts breaking" if it is not at Me.
lboxMyOpenForms.RowSource = GetOpenFormNames?

The problem is that my "last updated" event doesn't reside in a single table
or control -- it's actually a combination of records from two different
tables (Event table -- Last Updated is "event type" ; and a Notes tables --
stores names of different information sources). So a Last Updated event is
actually an Event ID and a Notes ID that's tied to a particular entity
through a three-prong join table (Entity ID, Event ID, Notes Id).

I set my database up to store all events in an event table (from last
updated to marketing tactics to meetings, phone calls, etc.), all contact
info (address, email, etc.) in a contacts table, all notes (from names of
information sources, to descriptions of marketing tactics to keywords to
"about this person" comments, etc.) in a notes table.

Did it this way on advice of a guy I know who is a database guru at his
company, but am finding it excruciating to construct queries, forms and
subforms that place all the right data in the various tables and join tables
- and even more excruciating coding and debugging everything.

Any thoughts?

Incidentally, my Last Updated subform is currently embedded in only one data
entry form (eventually it will be embedded in multiple forms, but for now,
it's only in one), so that can't be the reason it's closing on its own accord.

I think your problem stems from having multiple instances of the subform open
and trying to modify the subform's design while they're all open. I'm

[quoted text clipped - 97 lines]

End Function