CarryOver function issue

[Excellerant]

The previous owner of a database here added the "carryover" function to a form I now use on a daily basis. I have also added it to other forms with success except for one problem. Once the form is used, Access will not fully close. The individual file closes but not the program. I have to use ctrl-alt-del to close Access. Following this, I have to open Access directly in order to use it again. Double-clicking a file will not open the file. This is all corrected after I reboot or log out/in again.

Any ideas for were the issue is would be greatly appreciated.

Below is the code...

Sub CarryOver(frm As Form)
On Error GoTo Err_CarryOver
' Purpose: Carry the values over from the last record to a new one.
' Usage: In a form's BeforeInsert event procedure, enter:
' Call CarryOver(Me)
' Notes: This example limited to text boxes and combo boxes.
' Text/combo boxes must have same Name as the fields they represent.
Dim rst As DAO.Recordset
Dim ctl As Control
Dim i As Integer

Set rst = frm.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveLast
For i = 0 To frm.Count - 1
Set ctl = frm(i)
If TypeOf ctl Is TextBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
ElseIf TypeOf ctl Is ComboBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
ElseIf TypeOf ctl Is CheckBox Then
If Not IsNull(rst(ctl.Name)) Then
ctl = rst(ctl.Name)
End If
End If
Next
End If
rst.Close

Exit_CarryOver:
Set rst = Nothing
Exit Sub

Err_CarryOver:
Select Case Err
Case 2448 'Cannot assign a value
Debug.Print "Value cannot be assigned to " & ctl.Name
Resume Next
Case 3265 'Name not found in this collection.
Debug.Print "No matching field name found for " & ctl.Name
Resume Next
Case Else
MsgBox "Carry-over values were not assigned, from " & ctl.Name & _
". Error #" & Err.Number & ": " & Err.Description, vbExclamation, "CarryOver()"
Resume Exit_CarryOver
End Select
End Sub

 

[CajunCenturion]

Two things that immediately come to mind. First, if you hit the Case Else of the error handler, then you are not closing rst before setting it to nothing.

I would also set Ctl=Nothing at the end of the routine as well.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Excellerant]

CajunCenturion,

Thanks for the reply.

I rarely get the msgbox that results from the Case Else error handler. When I do, it does not work right but that usually only occurs in a new table with no records. Could this be causing a problem even if the error is not present? I also tried it without the case else error handler but no change in the way it responds on exit.

I entered the Ctl=Nothing just before the EndSub in my module and in the Exit_Carryover section, but that still did not resolve the issue.

Thanks for the tips,

 

[MichaelRed]

[COLOR=blue[/color], Eithier my misunderstanding or a small oversight?

The case else ends with "Resume Exit_CarryOver", which is where the rst is closed, so unless the select case continues past that statement, it appears to close rst? Am I misunderstanding the resume? Further, my understanding is that if the form itself is closed, then there are NO objects declared within the form left open. Am I in error on this as well?





MichaelRed
mlred@verizon.net

 

[CajunCenturion]

The most likely reason that Access is not terminating is that there is an outstanding reference that is never cleared by the application. I had a little hope that is was the Ctl object, but we need to look elsewhere.

I don't see anything in the CarryOver that looks offensive. Is what is shown the entire CarryOver procedure? Also, is the CarryOver procedure a public sub in a standard code module, or are you including it as a method in every form that is using it?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Excellerant]

MichaelRed,

I have run the module through step-by-step and it never did go to the case else error but the "resume_ExitCarryOver" does close the rst. The form does actually close. I can also close the file but Access itself stays open. As far as I can tell there are no objects left open.

I hope this helps,

Excellerant

 

[Excellerant]

CajunCenturion,

What I posted is the entire module. The CarryOver procedure is a module by itself called basCarryOver. The procedure is called from the BeforeInsert event procedure of each form that uses it.

If I understand it right, this is a public sub. Not sure what you mean by "standard code module." The code is stored as a module named basCarryOver with the sub within it called CarryOver.

I hope this answers your questions.

Thanks again,

Excellerant

 

[CajunCenturion]

basCarryOver is in a standard code module, that is as you say, a module by itself. It was just a question to ensure we're all on the same page, as module is over-used, and mis-used by many folks. I would write the ending section of the code as follows:

         ...your loop
      Next
   End If

Exit_CarryOver:
   If (rst.State = adStateOpen) Then
      rst.Close
   End If
   Set rst = Nothing
   Set ctl = Nothing
Exit Sub

Err_CarryOver:

Am I correct in the assumption that Access would close until you added this code and the call to it in the Before_Update, but failed to close afterward? There was nothing else done in the project in the meantime?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Excellerant]

CajunCenturion,

I added the code as you supplied but I am getting a Compile error: Method or data member not found.

I am running Access 2000 and I see the code you are using in the help file. Not sure why I am getting the compile error.

Something I just remembered, if this makes a difference, but for this code to work in an Access file I have to activate "Microsoft DAO 3.6 Object Library" in the references of VB. This is not active in newly created files.

Thanks again,

Excellerant

 

[CajunCenturion]

The .State property belongs to an ADO Recordset, not a DAO recordset. Sorry about that.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

Just an (errant?) thought - but you do not set the other object(s) to nothing (see esp the ctl object, so it MAY the reference to the control hangs up the form ...

MichaelRed
mlred@verizon.net

 

[Excellerant]

MichaelRed,

Not sure what other objects you are referring too, rst is set to nothing and we added set ctl to nothing. These did not help.

Excellerant

 

[Excellerant]

CajunCenturion,

I don't think I answered all your questions. At the time this code was added, I was not involved in using this form. I am not sure if anything else was added at the same time or not. There is some other code within the form, but if I remove the call CarryOver(me) from the BeforeInsert the problem goes away.

I have added the carryover to another form now without this problem, not a clue why a difference.

Thanks for your help,

Excellerant

 

[p27br]

excellerant

I also use this function CarryOver and have been experiencing the same problems.
And like you I have no clue as to why it is happening.

 

[Excellerant]

p27br,

Do you know where this code came from originally? Someone else installed this here and they are no longer with us. If we knew were it came from maybe we can track it to someone who has already found the solution.

Excellerant

 

[MichaelRed]

Not sure what other objects you are referring too

I am not sure either, as the other objects may actually not be declared in this procedure.




MichaelRed
mlred@verizon.net

 

[p27br]

Hi

Here is the link to the code source

http://members.iinet.net.au/~allenbrowne/ser-24.html

HTH

 

[Excellerant]

MichaelRed,

Below is the only other code this form uses. I did not see any other objects being opened.

Thanks for helping,

Excellerant

Private Sub Form_Current()
If Results = "facechip" Then
DefectsFrame = 1
DefectNumber = 1
"2-13 ommitted here"
ElseIf Results = "Too Short" Then
DefectsFrame = 14
DefectNumber = 14
Else
Results = "facecrack"
DefectsFrame = 3
DefectNumber = 3
End If
End Sub


Private Sub DefectNumber_BeforeUpdate(Cancel As Integer)
If DefectNumber = 1 Then
Results = "facechip"
DefectsFrame = 1
"2-13 ommitted here"
ElseIf DefectNumber = 14 Then
Results = "Too Short"
DefectsFrame = 14
ElseIf DefectNumber > 14 Then
Results = "error"
question1 = MsgBox("Invalid Number, try again!", vbOKOnly, "Defect Number")
ElseIf DefectNumber < 1 Then
question1 = MsgBox("Invalid Number, try again!", vbOKOnly, "Defect Number")
Results = "error"
End If
End Sub

 

[Excellerant]

p27br,

Thanks for the information, I just emailed the original creator for his input.

If he has an answer I will post it here as well.

Excellerant

 

[MichaelRed]

Excellerant, thanks for being thorough. Alas, I also sen no other objects or issues with the remaining code, so must assume that the issue lies elsewhere, although obviously intertwined with this.

The last gasp thought is some allocation (instantation) in the declarations section of the module?



MichaelRed
mlred@verizon.net