Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-18-2019, 06:21 AM   #1
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Error: Item Cannot Be Found...

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.
Attached Images
File Type: png Error.png (45.5 KB, 10 views)

xyba is offline   Reply With Quote
Old 07-18-2019, 06:33 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Hi. Can you also post an image of the error message? Thanks.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 06:44 AM   #3
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by theDBguy View Post
Hi. Can you also post an image of the error message? Thanks.
Attached for you.
Attached Images
File Type: png error2.png (80.1 KB, 5 views)

xyba is offline   Reply With Quote
Old 07-18-2019, 06:46 AM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 06:48 AM   #5
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

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.
xyba is offline   Reply With Quote
Old 07-18-2019, 06:50 AM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 06:50 AM   #7
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

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

xyba is offline   Reply With Quote
Old 07-18-2019, 06:53 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 07:11 AM   #9
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by theDBguy View Post
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.
xyba is offline   Reply With Quote
Old 07-18-2019, 07:17 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 07:22 AM   #11
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by theDBguy View Post
Does your form have a control called "RecordNo" on it?
No it doesn't.
xyba is offline   Reply With Quote
Old 07-18-2019, 07:32 AM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 07:56 AM   #13
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by theDBguy View Post
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!

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.
xyba is offline   Reply With Quote
Old 07-18-2019, 08:19 AM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Error: Item Cannot Be Found...

Quote:
Originally Posted by xyba View Post
Ahhh!

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 07-18-2019, 08:29 AM   #15
xyba
Newly Registered User
 
Join Date: Jan 2016
Posts: 119
Thanks: 31
Thanked 0 Times in 0 Posts
xyba is on a distinguished road
Re: Error: Item Cannot Be Found...

The code I used for the module includes this explanation.

Quote:
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.

xyba is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Item Not Found in this Collection Error Sousuke81 Modules & VBA 3 06-01-2016 07:04 AM
Question Item not found in this collection error? WildmanUK General 3 07-31-2015 05:53 AM
Item not found in collection error ritco Modules & VBA 3 06-11-2012 12:38 PM
Item not found in this collection error SeriesIII Modules & VBA 5 03-04-2009 07:22 AM
Item not Found in this Collection error Kenshiro Modules & VBA 12 05-12-2006 11:09 AM




All times are GMT -8. The time now is 02:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World