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

 

[Excellerant]

All,

I have been in contact with the originator of the code, Allen Browne [allen@allenbrowne.com]. He offers the following information, although I have yet to resolve the issue on my end.

"There was a problem with Access 97 not closing that traced to two different bugs in Access:
1. If code failed to close and dereference recordsets (set to nothing).

2. If you referred to a boolean control (check box, option button, toggle button) like this:
If Me.MyControl Then
which theoretically should work because the control has a true or false value.

AFAIK, Access 2000 and later does not have those bugs."

Since I am running Access 2002, this was not an issue.


"1. Form_Current is setting the value of DefectsFrame, etc.
Are any of these *bound* forms? If so, the code is dirtying the record immediately you arrive at any record. That is likely to cause concurrency issues.

2. Service packs.
Do you have at least SP2 for A2002? (SP3 is out, but you must have at least SP2.)
Also JET 4 SP8?
Both are available from the Downloads link at support.microsoft.com

3. Alternative way to carry over.
Another approach (different to the old article) is to set the DefaultValue of each control in Form_AfterInsert (or Form_AfterUpdate it that's what you intend). DefaultValue is a string, so there's a bit of messing around with quotes to get it right for each of the field data types, but it can work."

I already had the correct SP releases for both programs so this did not change anything. I also tried removing the Form_Current code with no change in the problem.

Still trying anything I can think of. I recently created another form using this code and am having the same problem again. I will be trying to compare the two to look for a commonality.

Excellerant

 

[p27br]

there is another way explained in the Access Developer's handbook if you have a copy. It uses the tags, but the implementation is quite complicated (for me).

 

[RoyVidar]

There's one method described in these two faqs too, faq702-4852 and faq702-5010 (section 5) - Getz et all uses the .Tag property to "save" the old default value, but it can also be used to differ between which control values to "carry over". There's some description/sample of usage of the .Tag property in the latter faq (section 3)

It would be interesting to hear wether any of these "solves" the not closing instance of Access challenge, but I think I'll join MichaelRed in believing the issue is caused by something not yet presented here. Could it be some corruption issues?

Roy-Vidar

 

[CajunCenturion]

I too side with MichaelRed and RoyVidar. I think something else is in play that we're not aware of.

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

 

[p27br]

excellerant

have you managed to fix this issue ?

 

[Excellerant]

p27br,

Unfortunatly no. At least not using the CarryOver(me) code. I have setup the other code that was recommended on new forms using the "CarryForward" tag on each field. That has worked on everything I have added it too so far, with the exception of Access not liking one piece of the error handling code. Haven't had time to change the one form over to this code as of yet but I am confident it will resolve my issue.

Thanks for checking up on this.