Dereference and close objects

[RCurtin]

Hi,
I have read that it is important to close any objects and de-reference them in order to prevent corruption.
Here is some code that may have caused problems in my database (I've only just added the last line). Could someone check it to see if I've closed and defererenced everything I should. For instance do I need to set me.recordsetclone = nothing and what about me.bookmark?

Me.RecordsetClone.FindFirst "[DrawingNum]= '" & Forms!frmDrawingsRegister![txtDrawingNum] & "' And [Rev] = " & Forms!frmDrawingsRegister![txtRevision] & ""
        If Not Me.RecordsetClone.NoMatch Then 'if drawing num AND rev number exists
            Me.Undo
            Me.Bookmark = Me.RecordsetClone.Bookmark    'make record found the current record
            Call disableControls(True, True) 'Disable textfields
        Else
            Me.RecordsetClone.FindFirst "[DrawingNum]= '" & Forms!frmDrawingsRegister![txtDrawingNum] & "'"
            If Not Me.RecordsetClone.NoMatch Then 'if drawing number entered exists
                Me.Undo 'undo add new record
                Me.Bookmark = Me.RecordsetClone.Bookmark    'go to the record in masterform with this drawing num
                Me.frmDrawingRevisions.SetFocus 'add new record to Revisions subform
                DoCmd.GoToRecord , , acNewRec
                msgboxAns = MsgBox("Do you wish to add a new revision of this drawing?", vbYesNo, "Add new revision of drawing?")
                Forms!frmDrawingsRegister!frmDrawingRevisions!txtLatestIssueDate.SetFocus
            Else 'drawing does not exist
                msgboxAns = MsgBox("Do you wish to add a new drawing?", vbYesNo, "Add new drawing?")
            End If
        
            If msgboxAns = vbYes Then
                Me.txtOrderNum.SetFocus
                Forms!frmDrawingsRegister!frmDrawingRevisions!txtRev.Value = Me.txtRevision.Value
                Forms!frmDrawingsRegister!frmDrawingRevisions!txtDrawingStatus.Value = "Latest Revision"
                Call disableControls(False, False)   'enable textfields
                Call updateRevisionStatus(Me.txtDrawingNum.Value)
    
            Else
                Me.Undo  'undo new drawing record record
                DoCmd.GoToRecord , , acPrevious
                Me.txtRevision.Value = ""
                Call disableControls(True, True) 'Disable textfields
            End If
        End If
        Me.txtRevision.Value = ""
    End If
    
    Me.RecordsetClone.Close

 

[RCurtin]

Does anyone have any suggestions on this? The code was working fine before but I think that maybe this code is causing corruption now.

Basically I have a form to input new drawings and new revisions of drawings. The main form is for the drawings and the subform shows the revision information.
tblDrawingsRegister
DrawingNum (pk)
DrawingName
OrderNum

tblDrawingRevisions
DrawingID (pk)
Rev
DrawingNum (fk)
LatestIssueDate
ElectronicCopy
DrawingStatus

A drawing can have many revisions and the drawing status is either 'Superceded' or 'Latest Revision'. This is set automatically.

The idea is that the user clicks on the Find/Add Drawing button, enters the drawing number and revision and then clicks the 'Enter button'.
-If the drawing number AND revision exists it jumps to the correct record in the main form and the corresponding subform record.
-If the drawing number exists but it is a new revision, it jumps to the correct drawing record and goes to a new record in the subform.

Here is the code behind the Find/Add button

DoCmd.GoToRecord , , acNewRec

Then the code in my previous post is behind the 'Enter' button. What is happening now is
- user goes to input an existing drawing with a new revision
- the message "do u want to add a new revision" comes up
- the subform instead of being cleared ready to add the new record - shows the previous revision and sets the status to 'Superceded'.

The behavious varies slightly for different users. Before if kept crashing each time after this. I decompiled the FE and imported everything into a new DB and now it doesn't usually crash.

I have no idea of what is wrong. The users have inputted over 6000 records and it was working perfectly but this has started to happen inthe last 2 weeks and I'm at at dead end now. Would really appreciate any suggestions.

 

[RCurtin]

Ok have just discovered that it always crashes in this case
- user adds a new revision
- user clicks on the received tab and fills in info (num of copies received, date etc)
- user goes back to the first tab - fills in the drawing number and revision
- message comes up asking if they want to add a new revision
- Access crashes "Microsoft Office has encountered a problem and needs to close..

Sometimes it crashes before the message box comes up. It doesn't seem to crash if the user is just adding new revisions but not clicking on the received tab. Here is the code in the click event of the tab control

If Me.tabCtrlDrawings = 1 Then  'if tabReceivedDrawings is shown
        If Me.Dirty Then
            MsgBox "Please save this record before you attempt to requery the data!", vbExclamation, "Save Required"
            Exit Sub
        End If
        
        RevDrawingID = Forms!frmDrawingsRegister!frmDrawingRevisions![txtDrawingID].Value
        DoCmd.Echo False
        Forms!frmDrawingsRegister!subFrmReceivedDrawings.Requery
        Me.Requery
        DoCmd.Requery 'requery the form so that if new drawing is added - it is in recordset
        
        Me.tabReceivedDrawings.SetFocus
        Me.RecordsetClone.FindFirst "[DrawingID]= " & RevDrawingID & ""   'go to the record corresponding to one on first tab
        DoCmd.Echo True
        
        If Not Me.RecordsetClone.NoMatch Then 'if there is a match
            Me.Undo
            Me.Bookmark = Me.RecordsetClone.Bookmark
        Else
            MsgBox "Not found"
        End If
  End If

I am reluctant to mess with the code too much as it was working before - but if this is caused by corruption in the FE - I've done everything I can think of (decompiling, importing into a new DB..)

The only thing I can think of is maybe I am not closing objects propperly - but in that case why did it work before?

 

[RCurtin]

I've just tried reinstalling Office 2003 but it made no difference.

 

[RCurtin]

Have tried importing the tables from the backend into a new database and compacted it using the JET utility. Didn't work.

Have to say I am really at a dead end - does anyone have any experience of anything like this? Its always fine when I enter the first new revision, then I click on the received tab, fill the fields and go back to the first tab. Then it always crashes when I go to add a new revision to another drawing.

Please help..