Error: Item Cannot Be Found... (1 Viewer)

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
Hi

For some reason my I am getting the "Item cannot be found in the collection corresponding to the requested name or ordinal" error when selecting a button on a form.

The button was created using the wizard so I'm not sure why this is happening.

Can anyone shed any light for me please?

Attached is a screenshot of the macro built by the wizard.
 

Attachments

  • Error.png
    Error.png
    45.5 KB · Views: 209

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Hi. Can you also post an image of the error message? Thanks.
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
Hi. Can you also post an image of the error message? Thanks.

Attached for you.
 

Attachments

  • error2.png
    error2.png
    80.1 KB · Views: 196

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Attached for you.
Hmm, thanks, but it wasn't was I was expecting. I could be wrong but that image looks like a VBA error message rather than a macro one. Which button did you click to get that error message?
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
I've just tested again and the issue is happening with all 5 buttons on the same form.

However, the button actions are working because the table has been updated as expected. It's just that error appears each time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
I've just tested again and the issue is happening with all 5 buttons on the same form.

However, the button actions are working because the table has been updated as expected. It's just that error appears each time.
Do you have any other code on the form like in its Current event?
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
This is the VBA attached to the form:

Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
   [RecordNo] = DMax("[RecordNo]", "Record") + 1
End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then Call AuditChanges("RecordNo", "DELETE")
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Call AuditChanges("RecordNo", "NEW")
    Else
        Call AuditChanges("RecordNo", "EDIT")
    End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
This is the VBA attached to the form:
Hi. Just as a test, for now, try commenting all those code out and see if the error still shows up when you click any of the buttons.
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
Hi. Just as a test, for now, try commenting all those code out and see if the error still shows up when you click any of the buttons.

Hi
I commented all the code and uncommented each one before a test. The top two worked but the error comes with this one:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Call AuditChanges("RecordNo", "NEW")
    Else
        Call AuditChanges("RecordNo", "EDIT")
    End If
End Sub

That code is referencing the below module:
Code:
Sub AuditChanges(IDField As String, UserAction As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAudit", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            End With
    End Select
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub


With my limited knowledge in this area I'm not sure what the issue could be.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Hi
I commented all the code and uncommented each one before a test. The top two worked but the error comes with this one:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Call AuditChanges("RecordNo", "NEW")
    Else
        Call AuditChanges("RecordNo", "EDIT")
    End If
End Sub
Does your form have a control called "RecordNo" on it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
No it doesn't.
Then that's why you're getting the error. Why are you calling the AuditChanges and passing a control name that doesn't exist? Either add the control to the form or pass a different control name - one that exists on the form.
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
Then that's why you're getting the error. Why are you calling the AuditChanges and passing a control name that doesn't exist? Either add the control to the form or pass a different control name - one that exists on the form.

Ahhh! :eek:

I'm confused then. Does the below part of the module code mean my field has to be named IDField or is that part of the VBA language?

Code:
![RecordID] = Screen.ActiveForm.Controls(IDField).Value

Also, I've changed the reference from RecordNo to the form field name that holds the record ID but I still get the error.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Ahhh! :eek:

I'm confused then. Does the below part of the module code mean my field has to be named IDField or is that part of the VBA language?

Code:
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
Also, I've changed the reference from RecordNo to the form field name that holds the record ID but I still get the error.
You might consider posting a demo version of your db, so we can help you trace the problem faster.
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
The code I used for the module includes this explanation.

The AuditChanges code is a self-contained procedure that can be "called" from any form. Along with the name of the procedure I have included a parameter which I have named IDField. When calling the procedure you will need to supply a value for this parameter which will be the name of the field that identifies the current record.

If I'm reading that right, I've changed RecordNo to reference the field on the form that holds the unique ID but I still get the error message.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
The code I used for the module includes this explanation.

If I'm reading that right, I've changed RecordNo to reference the field on the form that holds the unique ID but I still get the error message.
Hi. Try adding the ID field to the form but hide it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
I was just trying that but still getting the error.
Unless I get some new revelation later on, the only thing I could think of right now is to have a copy of your db, so we can look for ourselves why you're getting the error. Otherwise, you might try commenting out the bad code until you can figure out how to fix the error.
 

xyba

Registered User.
Local time
Today, 12:26
Joined
Jan 28, 2016
Messages
189
Hi. Try adding the ID field to the form but hide it.

Sorted!! :)

There was a mismatch in a table field name in the audit table. :banghead:

Thanks for all your help as I wouldn't have got that far without it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Sorted!! :)

There was a mismatch in a table field name in the audit table. :banghead:

Thanks for all your help as I wouldn't have got that far without it.
Congratulations! Glad to hear you got it fixed. Good luck with your project.
 

Users who are viewing this thread

Top Bottom