By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

multiple instances of a form - selected records ms access 97

P: 59
can anyone point me in the right direction re:
handling multiple instances of a form opened from selected records in a search form

I can get multiple forms thanks to allen browne & msdn
howto get the new instance of the form to move to the record selected from the search form?

thank you your help is muchly appreciated!
Feb 15 '08 #1
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,628
can anyone point me in the right direction re:
handling multiple instances of a form opened from selected records in a search form

I can get multiple forms thanks to allen browne & msdn
howto get the new instance of the form to move to the record selected from the search form?

thank you your help is muchly appreciated!
Just subscribing, when I get the chance I'll adapt Mr. Browne's code to your specifc needs, but first:
  1. What is the Name of your Search Form?
  2. What is the Name of the Form that will display the Search Results?
  3. What is the Record Source of this Display Form?
  4. What is the Name and Data Type of the Field you will be searching on?
  5. Is this Search Field the Primary Key or at least a Unique Index?
  6. Is there a Maximum number of Instances which should be created?
  7. Any other information you may think is relevant.
Feb 17 '08 #2

P: 59
thanks for your help Adezii

What is the Name of your Search Form?
frmsearch
frmsearch uses the logic based on this example

What is the Name of the Form that will display the Search Results?
frmsearch

What is the Name and Data Type of the Field you will be searching on?
Multi type filter (text/date/combo) - refer to example above

What is the Record Source of this Display Form?
tbltime

Is this Search Field the Primary Key or at least a Unique Index?
NO ^see above

Applying filter to frmsearch user can then select a record from list of records displayed on frmsearch. This opens the chosen record in a new form - frmtime - for editing.

Is there a Maximum number of Instances which should be created?
NO

Any other information you may think is relevant.

quick background
v.simple database
search form shows filtered list
user selects record from list for editing opens new form to edit record
I want to allow multiple instances of this edit form to allow multiple simultaneous edits.

I can get multiple forms using this example

I can get form to display selected record with DoCmd.Open ,,stLinkCriteria method on the primary key using a modal form.

what I am looking for is a way to combine these two things - multiple instances of frmtime which display the record selected from frmsearch

thanks
Feb 18 '08 #3

ADezii
Expert 5K+
P: 8,628
thanks for your help Adezii

What is the Name of your Search Form?
frmsearch
frmsearch uses the logic based on this example

What is the Name of the Form that will display the Search Results?
frmsearch

What is the Name and Data Type of the Field you will be searching on?
Multi type filter (text/date/combo) - refer to example above

What is the Record Source of this Display Form?
tbltime

Is this Search Field the Primary Key or at least a Unique Index?
NO ^see above

Applying filter to frmsearch user can then select a record from list of records displayed on frmsearch. This opens the chosen record in a new form - frmtime - for editing.

Is there a Maximum number of Instances which should be created?
NO

Any other information you may think is relevant.

quick background
v.simple database
search form shows filtered list
user selects record from list for editing opens new form to edit record
I want to allow multiple instances of this edit form to allow multiple simultaneous edits.

I can get multiple forms using this example

I can get form to display selected record with DoCmd.Open ,,stLinkCriteria method on the primary key using a modal form.

what I am looking for is a way to combine these two things - multiple instances of frmtime which display the record selected from frmsearch

thanks
As long as you are not in that great of a hurry, I'll see what I can do, but if you are in a rush, I'm sorry but I cannot be of much assistance to you. I'll hold off until I hear from you.
Feb 18 '08 #4

P: 59
thanks again for your help ADezii, there is no rush. If I get anywhere I will also postback.
cheers
Feb 20 '08 #5

ADezii
Expert 5K+
P: 8,628
thanks again for your help ADezii, there is no rush. If I get anywhere I will also postback.
cheers
Gotcha, blad3runn69 - see you around.
Feb 20 '08 #6

P: 59
do u lose your newbie tag if you ask a ? which nobody here can answer (or criticise) easily... ?
Feb 21 '08 #7

ADezii
Expert 5K+
P: 8,628
do u lose your newbie tag if you ask a ? which nobody here can answer (or criticise) easily... ?
I'll have something for you by the end of the day, maybe not the final solution right now, but something to point you in the right direction. BTW, I lost my Newbie Tag after 2,750 Posts. (LOL).
Feb 21 '08 #8

ADezii
Expert 5K+
P: 8,628
Just giving you an Overview and Update of where I am at concerning your question. Creating Multiple Instances of a Form is a simple process which can be accomplished with as little as 3 lines of code. The difficulties arise when you attempt to differentiate between the Instances, and also when you try to manage them. Each Instance of a Form has exactly the same name, so you cannot refer to a specific Instance in the conventional manner of Forms!<Your Form Name>. You cannot also refer to a specific Instances within the context of the Forms Collection such as Forms(2), since the Indexes into the Forms Collection is constantly changing as Forms are opened or closed. There is also the problem of Closing a specific Instance of a Form as opposed to all Instances.

There is, however, light at the end of the tunnel. To effectively manage each Form Instance, we can create a Collection in a Standard Code Module. This Collection will contain the Form Instances and manage them: as each new Instance is opened it is added to the Collection, and as each Instance is closed it is removed from the Collection. To uniquely identify each Instance, we can use the value of its hWnd Property when it was created. This hWnd value is a unique Handle (LONG INTEGER) assigned to each Window by the Operating System, and is constant for the lifetime of the Window.

I have used a subset of Mr. Allen Browne's code as a base from which to work, I have made additions, deletions, and modifications to his code, as well as importing the Employees Table from the sample Northwind Database. The data contained within this Table will be the groundwork on demonstrating my solution as to how to open Multiple Instances of a Form based on different search criteria. When finished, I'll post some of my code illustrating these techniques as well as the Test Database itself. Be advised, this will only be a simple Template that hopefully, will provide the groundwork, or at least give you some direction, on how to accomplish what you are requesting.

P.S. - Allen Browne deserves the majority of the credit here. It is essentially his code and ideas which enabled me to arrive at a possible solution to your question.
Feb 21 '08 #9

ADezii
Expert 5K+
P: 8,628
I think I have arrived at a workable solution, so what I'll do is post the relevant code only, and make the actual Test Database for this Thread available as an Attachment. I advise downloading the DB in order to get a much better idea of exactly what is going on.
Expand|Select|Wrap|Line Numbers
  1. 'Create a Collection to hold all Form Instances
  2. Public colInstance As New Collection  'Instances of frmInstance.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOpenAClient_Click()
  2. Dim Msg As String
  3.  
  4. 'Check cboEmployees on frmSearch, if it has an Employee to search
  5. 'for, call the Function fOpenNewInstance(). cboEmployees consists of
  6. '4 Columns, the first 3 of which are hidden
  7. '[EmployeeID], UCase$([LastName]), [FirstName], and UCase$([LastName]) & ", " & [FirstName]
  8. Msg = "You must first select an Employee to search for!"
  9. If IsNull(Me![cboEmployees]) Then
  10.   MsgBox Msg, vbExclamation, "No Employee to Search For"
  11.    Me![cboEmployees].SetFocus
  12.     Me![cboEmployees].Dropdown
  13. Else
  14.   Call fOpenNewInstance
  15. End If
  16. End Sub
Expand|Select|Wrap|Line Numbers
  1. Public Function fOpenNewInstance()
  2. Dim frm As Form
  3.  
  4. 'Open a new instance, show it, and set a caption.
  5. Set frm = New Form_frmInstance
  6. frm.Visible = True
  7. 'Set the Caption of the Form to "Search Results for LASTNAME, FirstName
  8. frm.Caption = "Search Results for [" & Forms!frmSearch![cboEmployees].Column(3) & "]"
  9.  
  10. 'Read the value of the [EmployeeID] Field (Primary Key) in cboEmployees,
  11. 'and set the Record Source equal to it (All Fields)
  12. frm.RecordSource = "Select * From tblEmployees Where [EmployeeID]= " & Forms!frmSearch![cboEmployees].Column(0)
  13.  
  14. 'Cascade New Instances for a better view, because if
  15. 'not, they would be stacked on top of one another
  16. 'DoCmd.RunCommand acCmdWindowCascade
  17.  
  18. 'Append it to our collection.
  19. colInstance.Add Item:=frm, Key:=CStr(frm.Hwnd)
  20. Set frm = Nothing
  21. End Function
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCloseAll_Click()
  2. Dim Msg As String
  3.  
  4. 'Command Button on Main Form, closes 'ALL' Instances if they exist
  5. Msg = "There are no Instances of the Search Results Form to Close!"
  6.  
  7. If colInstance.Count = 0 Then
  8.   MsgBox Msg, vbExclamation, "No Instances Created"
  9. Else
  10.   Call fCloseAllInstances
  11. End If
  12. End Sub
Expand|Select|Wrap|Line Numbers
  1. Function fCloseAllInstances()
  2. 'Note: Leaves the copy opened directly from database window.
  3. 'Closes 'ALL' created Instances from Command Button on frmSearch
  4. Dim lngInstanceCount As Long, lngCounter As Long
  5.  
  6. lngInstanceCount = colInstance.Count
  7.  
  8. For lngCounter = 1 To lngInstanceCount
  9.   colInstance.Remove 1
  10. Next
  11. End Function
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. 'Purpose: Remove this specific Instance from the colInstance collection.
  3. Dim obj As Object, blnRemove As Boolean
  4.  
  5. 'Check if this instance is in the collection.
  6. '(It won't be if form was opened directly, or code was reset.)
  7. For Each obj In colInstance
  8.   If obj.Hwnd = Me.Hwnd Then
  9.     blnRemove = True
  10.       Exit For
  11.   End If
  12. Next
  13.  
  14. 'Deassign the object before removing from collection.
  15. Set obj = Nothing
  16. If blnRemove Then
  17.   colInstance.Remove CStr(Me.Hwnd)
  18. End If
  19. End Sub
Feb 22 '08 #10

P: 59
that is cool, thank you and kudos to you ADezii. I will have a long look at your database & script, I have a lot to learn! :)

thank you again for your help ADezii, you are a gentleman and a scholar.
Feb 22 '08 #11

ADezii
Expert 5K+
P: 8,628
that is cool, thank you and kudos to you ADezii. I will have a long look at your database & script, I have a lot to learn! :)

thank you again for your help ADezii, you are a gentleman and a scholar.
Glad to be of service to you, but please do not forget to give credit to Allen Browne, since it was his original Article and code which pointed both of us in the right direction.
Feb 22 '08 #12

P: 59
allen browne is a wise owl (eg. multiple instances of a form pwns ms ... :)... I emailed & thanked mr browne 4 his boolean logic a long time ago, great minds make light work, thankyou ADezii... ;)\\
Feb 23 '08 #13

ADezii
Expert 5K+
P: 8,628
allen browne is a wise owl (eg. multiple instances of a form pwns ms ... :)... I emailed & thanked mr browne 4 his boolean logic a long time ago, great minds make light work, thankyou ADezii... ;)\\
You are quite welcome.
Feb 23 '08 #14

P: 59
managed to get it working using a filter. thought i would share for prosperity and ask about bugs :)

below is the OpenAClient() snippet of allen browne's managing multiple instances of a form code

Public clnClient As New Collection 'Instances of frmClient.
Function OpenAClient()
'Purpose: Open an independent instance of form frmClient.
Dim frm As Form

'Open a new instance, show it, and set a caption.
Set frm = New Form_frmClient
frm.Visible = True
frm.Caption = frm.Hwnd & ", opened " & Now()

'added this line to display the record selected from the search form.
idea from allen browne's search form code

frm.Filter = "tblpatID = " & Forms.frmsearch.frmpatID

'Append it to our collection.
clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)

Set frm = Nothing
End Function

any potential problems with this?

thank you again for all the help, it is muchly appreciated :)
Mar 4 '08 #15

P: 59
Hi ADezii, just wanted to say thank you again for all your help and code examples, it has been invaluable!
Mar 12 '08 #16

ADezii
Expert 5K+
P: 8,628
Hi ADezii, just wanted to say thank you again for all your help and code examples, it has been invaluable!
That's why we are all here, blad3runn69. Take care.
Mar 12 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.