VBA Record Not Found Error on Subform?

[SRL]

I am a first time poster on here, but have used this site regularly as a great resource to help me find answers to questions other folks have already asked. This time... I need help finding the problem in my own code.

Backstory: I have an Access 2003 mdb front end connected to a similar back end. I am working on a form to handle checking in samples. The main form contains general information about the chain of custody. The form contains a tabbed control. On each tab I have a subform. On the tab "Check-In" the subform (frmSampleCheckinSubform2) is used to handle sample information. It also contains a subform (frmSampleCheckinSubform21) which is used to handle subsample information. Simple enough. For ease I will call these subforms subform2 and subform21 respectively.

Subform2 contains 2 unbound buttons, "New Sample" (cmdNEwSample), "Delete Sample" (cmdDeleteSample) and an unbound checkbox "Duplicate Fields" (chkDupFields).

Open the form for a new sample...
-adding a sample works like a charm
-deleting a sample works like a charm

The problem: If you delete all the samples, then by choosing either the chkbox or the new sample button, I get a vague "No current record" error. I am assuming that this is happening because some control is trying to refernce the record when the recordset has been totally deleted. I however cannot find it.

What I've done: I have attached the code for both the "New Sample" and "Delete Sample" button. I have tried adding stop points within all the code to narrow down the statement that is generating the error. I can't find it. It appears to be happening between the time the Delete Sample code finished and before the first statement in the "new sample" code.

Suggestions?

Thanks for any and all help with this problem.

Delete Button Code:

Private Sub cmdDeleteSample_Click()
On Error GoTo Err_cmdDeleteSample_Click
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Delete Record?") = vbYes Then
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
      DoCmd.SetWarnings False
      DoCmd.RunCommand acCmdSelectRecord 'this command is not normally needed
      DoCmd.RunCommand acCmdDeleteRecord
      DoCmd.SetWarnings True
 
    'Me.Requery
    Me.AllowAdditions = False
End If
 
Exit_cmdDeleteSample_Click:
    Exit Sub
Err_cmdDeleteSample_Click:
    MsgBox Err.Number & ": " & Err.Description, , Me.Name
    Resume Exit_cmdDeleteSample_Click
 
End Sub

New Sample Code:

Private Sub cmdNewSample_Click()
On Error GoTo Err_cmdNewSample_Click
 
    Dim o_LabProjectID, o_Type, o_SampleDate, o_BeginCompositeDate, o_CollectionMethod, o_Collector, o_Containers
    Dim o_ContType, o_Preservation, o_TurnAround, o_StationID, o_Replicate, o_SamplePoint, o_Investigators, o_Reason, o_SampleInfo_COCID
    Dim o_ShippedTo, o_ShipmentMethod, o_comments, o_Samples, o_SampleCount
 
 
    'Check if recordset is empty and if not duplicate fields
 
    If Me.Recordset.RecordCount = 0 Then Me.ChkDupFields = 0
    If Me.Parent!cboProject = 0 Then
        MsgBox "You must select the Project First", , "Select Project"
        Cancel = True
        Me.Parent!cboProject.SetFocus
    Else
        If Me.ChkDupFields = -1 Then
            'check if a current record
            'Save current Record
            If Me.Dirty Then Me.Dirty = False
            Me.AllowAdditions = True
            'get current values current records
            o_LabProjectID = Me.[Lab Project ID]
            o_Type = Me.Type
            o_SampleDate = Me.SampleDate
            o_BeginCompositeDate = Me.BeginCompositeDate
            o_CollectionMethod = Me.CollectionMethod
            o_Collector = Me.Collector
            o_Containers = Me.Containers
            o_ContType = Me.ContType
            o_Preservation = Me.Preservation
            o_TurnAround = Me.TurnAround
            o_StationID = Me.StationID
            o_Replicate = Me.Replicate
            o_SamplePoint = Me.SamplePoint
            o_Investigators = Me.Investigators
            o_Reason = Me.Reason
            o_SampleInfo_COCID = Me.txtCOCSampleInfoCOCID
            o_ShippedTo = Me.ShippedTo
            o_ShipmentMethod = Me.ShipmentMethod
            o_comments = Me.Comments
            o_Samples = Me.Samples
            o_SampleCount = Me.SampleCount
 
            'Create new Primary Record
            If Not Me.NewRecord Then
                DoCmd.GoToRecord , , acNewRec
            End If
 
            'add default values
            Me.txtContainers = 1
            Me.SampleCount = 1
            Me.Samples = Me.txtSampleInfoSampleID
            Me.txtLabProjectID = Me.Parent.Form.ProjectID
            Me.txtCOCSampleInfoSampleID = Me.txtSampleInfoSampleID
 
            'add cloned record values
            Me.[Lab Project ID] = o_LabProjectID
            Me.Type = o_Type
            Me.SampleDate = o_SampleDate
            Me.BeginCompositeDate = o_BeginCompositeDate
            Me.CollectionMethod = o_CollectionMethod
            Me.Collector = o_Collector
            Me.Containers = o_Containers
            Me.ContType = o_ContType
            Me.Preservation = o_Preservation
            Me.TurnAround = o_TurnAround
            Me.StationID = o_StationID
            Me.Replicate = o_Replicate
            Me.SamplePoint = o_SamplePoint
            Me.Investigators = o_Investigators
            Me.Reason = o_Reason
            Me.txtCOCSampleInfoCOCID = o_SampleInfo_COCID
            Me.ShippedTo = o_ShippedTo
            Me.ShipmentMethod = o_ShipmentMethod
            Me.Comments = o_comments
            Me.Samples = o_Samples
            Me.SampleCount = o_SampleCount
 
            If Me.Dirty Then Me.Dirty = False
            Me.AllowAdditions = False
 
            'add new subsample too
            Me.frmSampleCheckInSubform21.SetFocus
            Me.frmSampleCheckInSubform21.Form.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
            Me!frmSampleCheckInSubform21.Form.Subsample = 1
            Me!frmSampleCheckInSubform21.Form.txtSubandSample = Trim(Str(Me.txtSampleInfoSampleID) + "-" + Chr$(64 + Me!frmSampleCheckInSubform21.Form.Subsample))
            Me.frmSampleCheckInSubform21.Form.AllowAdditions = False
        Else
            'Add new Record
            Me.AllowAdditions = True
            If Not Me.NewRecord Then
                DoCmd.GoToRecord , , acNewRec
            End If
            Me.txtContainers = 1
            Me.SampleCount = 1
            Me.Samples = Me.txtSampleInfoSampleID
            Me.txtLabProjectID = Me.Parent.Form.ProjectID
            Me.txtCOCSampleInfoSampleID = Me.txtSampleInfoSampleID
            If Me.Dirty Then Me.Dirty = False
            Me.AllowAdditions = False
 
            'add new subsample to
            Me.frmSampleCheckInSubform21.SetFocus
            Me.frmSampleCheckInSubform21.Form.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
            Me!frmSampleCheckInSubform21.Form.Subsample = 1
            Me!frmSampleCheckInSubform21.Form.txtSubandSample = Trim(Str(Me.txtSampleInfoSampleID) + "-" + Chr$(64 + Me!frmSampleCheckInSubform21.Form.Subsample))
            Me.frmSampleCheckInSubform21.Form.AllowAdditions = False
 
 
        End If
    End If
Exit_cmdNewSample_Click:
    Exit Sub
Err_cmdNewSample_Click:
    If Err.Number <> 2499 Or Err.Number <> 3167 Then
        MsgBox Err.Number & ": " & Err.Description, , Me.Name
        Resume Exit_cmdNewSample_Click
    End If
End Sub

 

[CJ_London]

Try commenting out your on error lines and running the code again.

You probably know this but if you click in the grey vertical bar to the left (displays a maroon dot) the code will stop there and using F8 will then step through the code

 

[SRL]

To add a little more to my question...

Is it possible that the code generating the error may be in some other event sequence?
I am not sure of the exact sequence of events that are triggered from the time I click the Delete button and the time I click any other control on the form.

On the subform2 I also have vba in the following events.

OnCurrent: changes the visibility of a couple of controls based on content
BeforeInsert: modifies Audit Fields with username and timestamp for new records
After insert: Locks some fields on the parent form to make sure they are not changed once sample data is entered.
BeforeUpdate: modifies Audit Fields with username and timestamp for modified records
AfterDeleteConfirm: locks/unlocks some fields on parent form to prevent/allow editing if there are no more records (samples) in Subform2.

I have individually bypassed each of these events in order to determine which of these may be causing the error, with no luck.

Thanks for reading and for any help in resolving this issue.:banghead:

 

[SRL]

Thanks for the Tip CJ... I was aware of the stops and steps. I will try commenting out the error statements, but I am not sure I understand the rationale. I know error traps can sometime hide problems with the code, but can they cause them too?

I know the error its generating, I just don't know where. Other than generating the error, the code does exactly what I want it to do. I even tried modifying the error trapping so that It would respond with the event name triggered it... no such luck. I will try your suggestion and respond back here. Thanks again!

 

[SRL]

Try commenting out your on error lines and running the code again.

No joy CJ... It still generates the exact same message. It makes me think that the error is not being generated within these two snippits of code, but rather within some other event code... but alas, the location of the offending code eludes me...

 

[CJ_London]

I know error traps can sometime hide problems with the code, but can they cause them too?

Not so far as I am aware but I guess you could end up passing a null value if the error trapping is not right.

Going back to basics - if you have a filter which means you are only seeing one record and you then delete it then there is no record so any subsequent events based on values in the record may cause the error.

I presume your form allows additions?

You could try getting rid of all those docmds and simply have:

Codedb.Execute("Delete * From Table Where ID=' & ID)
form.requery '(which should refresh it)

Just throwing out ideas...

 

[SRL]

Going back to basics - if you have a filter which means you are only seeing one record and you then delete it then there is no record so any subsequent events based on values in the record may cause the error.

You are absolutely correct. I have searched for actions that interact with fields bound to the recordset, but i haven't found them. I will look some more.

I presume your form allows additions?

I have the forms set for allowadditions = false. However, I change this condition to true before adding a record and after saving the record, change it back to false. I will try it again leaving allow additions to true the entire time. I will respond back here. Thanks for your input...

 

[SRL]

I presume your form allows additions?

Ok... now we are getting somewhere. When I set both subforms to allowadditions the form works wonderfully. now to figure out how to lock it down without throwing an error. I will report back.. Awesomeness...

 

[SRL]

Update: Ok, so now we know that the condition causing the error is that on subforms2 and subform21, the AllowAdditions properties are set to false, except when we are adding records.

I need to protect these subforms from users trying to add sample records if they have not selected a project from the mainform cboProject, or by just entering data into the forms, they must push the "New Sample" button.

I have two choices:

1) Protect using AllowAdditions property. To do this i have to find whatever code is triggering the no record event so that I can either stop it or trap the error. This would seem to me to be the preferred method, because that explicitly stops the action I am trying to stop.

Where can i find the exact sequence of events if i click on an unbound button or chkbox on subform2 when...

main.subform2.allowadditions = false
main.subform21.allowadditions= false
main.allowadditions= true

main.subform2.recordset is empty (would the recordcount be 0 or Null?)?

This always triggers the "NoCurrentRecord" error.

or...

2) Find another way to prevent access to the subforms when conditions are not right. Suggestions????

 

[SRL]

You could try getting rid of all those docmds and simply have:

Codedb.Execute("Delete * From Table Where ID=' & ID)
form.requery '(which should refresh it)

Just throwing out ideas...

I might be opening up a can of worms here but I appreciate your input. Is there a general coding preference for either using or not using docmds vs your codedb.execute?

My intent for this project is ultimately to port the back end to a mysql db and use access as workstation front end within our netowork. Then to develop something similar as a web front end that our clients/contractors can use for accessing/analyzing some of the data. The first part is months down the road, the second part is a year or more down the road.

 

[spikepl]

2) Find another way to prevent access to the subforms when conditions are not right. Suggestions????

1. Make subforms not enabled, until conditions on main form are OK, or
2. In the subforms' Before Insert, or perhaps Before Update, check the conditions on the main form and cancel the event, if conditions do not suit.

 

[SRL]

1. Make subforms not enabled, until conditions on main form are OK, or
2. In the subforms' Before Insert, or perhaps Before Update, check the conditions on the main form and cancel the event, if conditions do not suit.

I thought about that, and in-fact, started down that road. I changed course, because I thought allowadditions made more sense. Maybe I shouldn't have....