form instances sharing actions

[JaxIE]

Hello,
I am creating a production time database where a workstation logs in and a form is created for that login. I've used Allen Browne's sample to create the instances, however the forms are not just searching for a record. The time form includes a subform that records the start and end time based upon button programs. The programming I have works for a single instance, however when I have multiple instances running, the fields in the parent and child form are overwritten by the changes in other instances. I'm a novice at programming. How do I identify each by the instanceID?

Here is the code for the clockin or start button:

Me.frmClockIn_SUB.SetFocus
Me.frmClockIn_SUB!TimeID.SetFocus
DoCmd.GoToRecord , , acNewRec
Me.frmClockIn_SUB!WorkstationID = Me.cboWorkstationID
Me.frmClockIn_SUB!InstanceID = Me.Hwnd
Me.frmClockIn_SUB!TimeIN = Now
Me.frmClockIn_SUB!DayName = Date
Me.frmClockIn_SUB!cboProduct = Me.Product_Number
Me.frmClockIn_SUB!Rework = Me.Rework
Me.frmClockIn_SUB!CurrentStatus = "In Progress"
RunCommand acCmdSaveRecord

 

[NauticalGent]

Happy New Year JaxIE and welcome to AWF. I see you are diving right in and getting real dirty real fast.

Not a bad thing, just be patient and don't get discouraged.

As Allen Browne explains, you need to use the form's hWnd value to identify which form you want to work with.

 

[arnelgp]

how are you creating the new instance?
i have no chance to see Browne's site
but the idea i think is same.

you put the instance of the form
in a Collection.
in a New Module you create this:

Private gFormInstances As Collection

Public Sub FormInstanceAdd(frm As Form, frmName as string)
	If gFormIntances is Nothing Then
		Set gFormInstances = New Collection
	End If
	gFormInstances.Add frm, frmName
End Sub

Public SubFormInstanceRemove(frmName As String)
	gFormInstances.Remove frmName
End Sub

you create a Form-wide variable on the code-behind
the Form and on the Open Event of your form you set the value of this
variable.

Dim mdlFormName As String

Private Sub Form_Open(Cancel As Integer)
	mdlFormName = Format(Now, "nnss")
	FormInstanceAdd Me, mdlFormName
End If

Private Sub Form_Close()
	FormInstanceRemove mdlFormName
End Sub

on the click event of the button that Create New
Instance of the Form:

Private Sub NewIntanceButton_Click()
	Dim frm As Form_yourFormName
	Set frm = New Form_yourFormName
	frm.Visible = True

	
End Sub

lastly on the code that add new record to the form:

	Dim rs As DAO.Recordset
	Set rs = Me.frmClockIn_Sub.Form.RecordsetClone
	With rs
        	.AddNew
	        !WorkStationID = Me.cboWorkStationID
        	!INSTANCEID = Me.hwnd
	        !TIMEIN = Now
        	!DAYNAME = Date
		!cboProduct = Me.Product_Number
		!Rework = Me.Rework
		!CurrentStatus = "In Progress"
        	.Update
	        .Close
	End With
	set rs=Nothing

 

[Pat Hartman]

For someone with no programming experience, you've chosen a pretty advanced task. It is also a very rare one. I've created hundreds of Access apps and only in one case did I even consider opening multiple instances of the same form. In the end, I didn't release it to production because it was just too confusing for the user. He kept loosing track of what he was doing.

 

[Uncle Gizmo]

You might find this article "Take Advantage Of The Class Module Features Of Your Forms"
by Garry Robinson useful

 

[JaxIE]

Thank you for the feedback and help so far. After review I discovered that the subform was the issue. I had initially developed this using another sample database I found online. Since discovering that it didn't work right with the multiple instances, I removed the subform. I am closer to getting it working properly now, but am having an issue finding the record before entering the end time.

This is my coding for the start time:

Private Sub cmdClockIn_Click()
    Dim frm As Form
    Dim lngHwnd As Long
        
    If Me.RecordsetClone.RecordCount > 0 And IsNumeric(Me.Hwnd) Then
        'Find the calling form
        lngHwnd = Me.Hwnd
        For Each frm In Forms
            If frm.Hwnd = lngHwnd Then
                With frm.RecordsetClone
                        .AddNew
                        !WorkstationID = Me.cboWorkstationID
                        !InstanceID = Me.Hwnd
                        !TimeIn = Now()
                        !DayName = Date
                        !ProductNumber = Me.cboProduct
                        !Rework = Me.Rework
                        !CurrentStatus = "In Progress"
                        MsgBox "You have successfully clocked in at " & Now(), vbOKOnly
                        .Update
                        .Close
                End With
                Exit For
                
            End If
        Next
    End If
    Set frm = Nothing

This is the coding so far for the stop time:

Private Sub cmbStop_Click()
Dim frm As Form
Dim lngHwnd As Long
Dim blnFound As Boolean
    
    If Me.RecordsetClone.RecordCount > 0 And IsNumeric(Me.Hwnd) Then
        'Find the calling form
        lngHwnd = Me.Hwnd
        For Each frm In Forms
            If frm.Hwnd = lngHwnd Then
                'Find this record in that form.
                With frm.RecordsetClone
                    .findfirst "InstanceID = " & lngHwnd
                    If Not .NoMatch Then
                        .AddNew
                        !TimeOut = Now()
                        !DayName = Date
                        MsgBox "You have completed the task at " & Now(), vbOKOnly
                        .Update
                        .Close
                        blnFound = True
                    End If
                End With
                
                Exit For
                
            End If
        Next
    End If
    If blnFound Then
        'Focus on the calling form, and close this one.
        frm.SetFocus
        Set frm = Nothing
        DoCmd.Close acForm, Me.Name
    Else
        Beep
    End If
    Set frm = Nothing

 

[arnelgp]

on the ClockIn, there is no need to test if it is a new
record, since every ClockIn is new record.

STRONGLY advice, add Autonumber Field in your Table (eg. ID).
This is the Key field we will search when we add the
ClockOut (stoptime).

you don't need to Search for the Window Handle, since you already have
it in the Form property, Me.hwnd.

The following code Saves the AutoNumber Field (ID), when we
save the ClockIn. then use this number As search criteria to
Update the ClockOut (StopTime):

Dim lngID as long

Private Sub cmdClockIn_Click()
	Dim lngHwnd As Long
	Dim rs As DAO.Recordset
	Dim varBookMark As Variant
	Dim varClockIn As Variant

	lngHwnd = Me.hWnd
	varClockIn = Now()
	Set rs = Me.RecordsetClone

	With rs
		' no need to check if this is new record or not
		' since this is the startTime (clockIn).
		.AddNew
		!WorkStationID = Me.cboWorkstationID
		!InstanceID = lngWwnd
		!TimeIn = varClockIn
		!DayName = Date
		!ProductNumber = Me.cboProduct
		!Rework = Me.Rework
		!CurrentStatus = "In Progress"
		.Update
		' Save the ID for later use
		varBookMark = .LastModified
		.Bookmark = varBookmark		
		lngID = !ID
		.Close
	End With
	set rs = Nothing
	MsgBox " You have successfully clocked in at " & Trim(varClockIn & ""), vbOkOnly
End Sub

now for you TimeOut (ClockOut) code:

		Dim rs As DAO.Recordset
		Dim varClockOut As Variant
		Dim bolFound As Boolean

		varClockOut = Now()
		Set rs = Me.RecordsetClone
		
		bolFound = False
		' search the ID we saved when we clocked In
		With rs
			.FindFirst "ID = " & lngID
			If Not .NoMatch Then
				' we found it, so update the ClockOut (timeOut)
				' we Use Edit not Addnew
				bolFound = True
				.Edit
				!TimeOut = varClockOut
				!DayName = Date
				.Update
				Msgbox "You have completed the task at " & Trim(varClockOut & "") & "." & _
					vbCrLf & "Form will close when you pressed OK.", vbOkOnly
			Else
				Beep
				Msgbox "No TimeIn record found",vbOkOnly

			End If
			.Close
		End With
		

		If bolFound Then
			DoCmd.Close acForm, Me.Name
		End If

 

[Galaxiom]

how are you creating the new instance?
i have no chance to see Browne's site but the idea i think is same.

you put the instance of the form in a Collection.

Browne shows a general solution where any number of instances can be added to a collection.

However a collection isn't essential for multiple instances of an object. If you have a number of specific instances they can each be set as an object variable. As long as the variable remains in scope the instance will stay loaded.

Same with using the Hwnd to identify them. It is just a handy name to use when putting them in a collection but it is a lot simpler to just refer to them with an object name.

BTW Anyone getting into multiple instances of any kind of object, consider persisting them in a Dictionary rather than a Collection. They are have more features then Collections, particularly the Exists Method which will tell if something is in it without having to iterate like you have to do in a Collection.

 

[MarkK]

Also, you don't necessarily need to put non-default instances in a collection or dictionary at all. Yes, the form goes out of scope if there is not a reference to it, but you can put that reference on the form itself. Consider code like this on a form, say, Form1...

private m_me as Access.Form

private sub form_open(cancel as integer)
   set m_me = me   [COLOR="Green"]'form maintains its own reference to itself[/COLOR]
end sub

...then you can open four non-default instances like...

dim i as integer
for i = 0 to 3
   with new Form_Form1
      .visible = true
   end with
next

...and each one stays open without assigning it to an independent variable, collection, or dictionary because each one does so for itself.
hth
Mark

 

[NauticalGent]

...and each one stays open without assigning it to an independent variable, collection, or dictionary because each one does so for itself.
hth
Mark

Now THAT is pretty slick...

 

[arnelgp]

Very good research.

 

[Galaxiom]

Also, you don't necessarily need to put non-default instances in a collection or dictionary at all. Yes, the form goes out of scope if there is not a reference to it, but you can put that reference on the form itself. ...and each one stays open without assigning it to an independent variable, collection, or dictionary because each one does so for itself.

But without an external variable, how would you go about referring to a particular form.

 

[MarkK]

But without an external variable, how would you go about referring to a particular form.

I don't understand. What kind of reference do you mean "to a particular form?"

To me, a form appears on screen and the user interacts with it, and at times it is convenient to have multiple instances of the same form open. I don't understand the need for external process that needs to "refer to a particular form." The form should be able to handle user interaction on its own without regard to its instancing.

And the main problem with non-default instances is managing those instances so they don't go out of scope. This tip solves that problem, because each form maintains it's own reference to itself, eliminating that external management overhead. This makes using non-default instances way, way easier, indeed, makes them function much more like default instances.

Does that make more sense? Or have I misunderstood your question?
Mark

 

[Galaxiom]

I don't understand. What kind of reference do you mean "to a particular form?"

I guess I am stuck on a principle (right or wrong) that every object should have a way for the application to explicitly refer to it. In this case I was thinking of the potential need for another object to refer to a particular instance of the form.

I suppose in some cases the instance could pass a reference to itself.

But there would be some cases where that isn't straightforward. For example, if it were to open another form and that form was expected to interact with it. OpenForm doesn't have an easy way to pass an object (short of passing a pointer but that is relatively complex.)

I guess it doesn't matter if there is no such interaction.

 

[isladogs]

Mark/Greg

I may just be losing the plot but I'm finding it increasingly difficult to follow this exchange.

Would either or preferably both of you be willing to post a very simple example to show how your different approach(es) would work in a real situation

Many thanks in advance

 

[JaxIE]

Wow! Thank you all for the valuable exchange. I've input arnelgp's code example and seem to be getting an error with Dim rs As DAO.Recordset --- Compile error. User-defined type not defined.

 

[arnelgp]

On vbe, tools->reference, check if you have missing reference and try to resolve it.

 

[MarkK]

...difficult to follow this exchange.

Hi Colin, there's a lot going on in this thread, but do you see the little trick for keeping non-default instances of forms open in post #9? I think you might like that.
Mark

 

[jleach]

BTW Anyone getting into multiple instances of any kind of object, consider persisting them in a Dictionary rather than a Collection.

As an aside to the thread, there's a nice drop-in Dictionary class here: https://github.com/VBA-tools/VBA-Dictionary

 

[jleach]

Hi Colin, there's a lot going on in this thread, but do you see the little trick for keeping non-default instances of forms open in post #9? I think you might like that.
Mark

That's the type of approach that would have me cursing the developer who wrote it when I was finally able to track down what was going on (it is quite clever though, I wouldn't have thought of it).

Like Galaxiom, I tend to feel that it's better to be able to explicitly have a reference. Discoverability of the mechanics aside, my major concern would be that some future requirement makes it so I have to somehow refer to this form externally, which would mean some workarounds or replacement of instance management. I'd generally opt for more traditional means, I think.

Neat bit of code for sure though.