Multiple Form instances

  • Thread starter Thread starter hje
  • Start date Start date
H

hje

Forgive me if I dont speak the Access lingo as I describe
my problem - fairly new user here!

I have a button on my form, that once clicked, provides a
query screen. The query results are displayed in a form.

My concern is, that the results from the query need to be
displayed in the same form as the one that instigated the
search.

For example, the user is doing data entry into the
form "forma". They need to lookup another record. On
forma I created a command button that launches a query
form "search_item". The results of the query are
displayed in the form "forma".

The above scenario should be able to chain, such that when
a user needs to search records, the display is in the same
form and editable.

The code I have for button is as follows:

Private Sub search_item_button_Click()
On Error Go To Err_search_item_button_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Search_item"
DoCmd.OpenForm stDocName,,,stLinkCriteria

Exit_search_item_button_Click:
Exit Sub

Err_search_item_button_Click:
MsgBox Err.Description
Resume Exit_search_item_button_Click
End Sub

Obviously, this does not work, since once the query is
finished, the form already exists and is open.

Any suggestions on how I can have multiple instances of
the same form open and available for editting? Or am I
looking at a non-doable thing? If so, I would welcome
suggestions on how to work around what the user is trying
to achieve.
 
Following is some information on creating multiple form instances - I'm not
sure how well it will fit your existing situation but you can try it.
You'll have to make some changes - for example, where you are using the
WhereCondition parameter of docmd.openform, you need another way of
filtering each instance of the form. In my example, the form was saved with
no recordsource and then the recordsource is set on the new instance using
an SQL string with the criteria appended into the Where condition.

ACC2000: How to Open Multiple Instances of a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q210248

ACC: How to Open Multiple Instances of a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q135369

For each new instance you need an form object variable which you set using
the New keyword. Once you have the form open you can change it's
recordsource and caption.

Be careful

- the form that you are instancing must have a module (ie it can not be a
lightweight form).

- the form object variable is the best way to refer to this new instance
since you will not be able to find the form in the forms collection (unless
you know it's index).

- As soon as your object variable goes out of scope, the new instance of the
form will automatically be destroyed (and closed).

Here's some sample code that uses the selected rows in a listbox to open
multiple instances of the customer form.

Note that col is declared in the Module header so that it stay's in scope
until the current module terminates.

Dim colForms As Collection
Private Sub Command2_Click()
Dim varItem As Variant
Dim frm As Form
Set colForms = New Collection
For Each varItem In Me.List0.ItemsSelected
Set frm = New Form_Customer
frm.RecordSource = "Select * from Customer " _
& "Where Custid=" & Me.List0.ItemData(varItem)
frm.Caption = "Customer: " & Me.List0.Column(1, varItem)
colForms.Add frm
Next varItem
End Sub
 
Back
Top