Results 1 to 13 of 13
  1. #1
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47

    Item not found in this collection

    Hi guys, I am facing a issue whereby if I would like to edit the recordset on my form it will prompt me this error and bring me to a line of code which i have attached a image above.
    The image shows that my Nettweight = 222.294293............ whereas the nettweight on my form show 222.29
    Attached Thumbnails Attached Thumbnails Code.jpg   form.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    when asking for the field by name, you must have quotes around it, (as all text does)
    .fields("grossweight")

    if you use a column number, NO quotes,
    .fields(3)

    as well as variables posing as numbers
    .fields(MyFieldCol)

    so
    .fields("Nettweight") is prob the answer.

  3. #3
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Even if i put .fields("Nettweight") it still prompt me the same error till i have to comment it out which leave a #Type when i clear my record set on my form

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then check spelling. Are you sure it's not "NetWeight" ?
    Or try using the correct field number. Note - this is zero based, so first field of the recordset is 0, third field is 2, etc.
    If it works with the field number, then the field name you've used is the problem.
    Last edited by Micron; 10-09-2017 at 09:29 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Yes, i am sure there is no spelling mistake

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So what happened when you used the field's index number instead?

  7. #7
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by Micron View Post
    So what happened when you used the field's index number instead?
    Same issue as currently my field name is set as number.

    Is kind of funny tho, that all my other fields are working fine except for nettweight and loss and i set both of this field as number which is the same for my grossweight, lbs and yds.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You'll have to explain that more clearly. It sounds as though you're talking about the field data type (number, text etc.) which is irrelevant of course.

  9. #9
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Quote Originally Posted by Micron View Post
    You'll have to explain that more clearly. It sounds as though you're talking about the field data type (number, text etc.) which is irrelevant of course.
    My main concern is to remove #type error on two of my textbox (Nettweight & Loss) on my form after i click on the "Clear" command button on my form.
    I have attached a few images for your reference.
    The first picture show the result after i calculate my grossweight through command button on my form.
    The second picture is my code for my clear button, after the first picture i would like to clear my result and the code highlight are error whenever i click on the "Clear" button.
    The third picture is the error message that pop up.

    Click image for larger version. 

Name:	form.png 
Views:	13 
Size:	3.8 KB 
ID:	30744Click image for larger version. 

Name:	Code.jpg 
Views:	13 
Size:	19.8 KB 
ID:	30745Click image for larger version. 

Name:	Error.png 
Views:	13 
Size:	19.6 KB 
ID:	30746

    The four picture shows that i have comment out my code to prevent getting the error above.
    The last picture show that after i comment out the nettweight and loss code, when i click on clear it will show #type error on my textbox instead of being empty

    Click image for larger version. 

Name:	Code after comment.png 
Views:	13 
Size:	2.5 KB 
ID:	30747Click image for larger version. 

Name:	#TYPE.png 
Views:	13 
Size:	2.7 KB 
ID:	30749

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the control you're trying to update has an expression as its control source (e.g. = txtNettweight + txtLoss), you cannot simply update a value in it.
    Also, you're still not using the quotes around the field name as instructed. The fact that doing so changed the error to some other error doesn't negate the need to follow the instruction. It simply means you solved one error and exposed another. I think it's time that you posted a compacted and zipped db copy for analysis. Not sure I will be able to help further since I still use 2007.

  11. #11
    Niko is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    47
    Hi, I have attached my database above
    Attached Files Attached Files

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was able to open it.
    My previous answers were primarily based on the title of your post "Item not found in this collection" which doesn't seem to have much to do with your problem. Basically, you just cannot set whatever value you want into a calculated control - as noted in post #10.
    If your issue is the number of decimal places, format the control to Fixed or whatever works for you. If all you want to do is clear the form, change these lines like so
    Code:
    Me.txtPound = Null
    Me.txtGrossweight = Null
    'Me.txtNettweight = ""
    'Me.txtLoss = ""
    Don't try to set controls to empty strings ("") when other controls rely on them having numbers in them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have other problems with the code module that need to be fixed....

    The top two lines of EVERY Module should be:
    Code:
    Option Compare Database
    Option Explicit
    The line "Option Explicit" requires variables to be declared - which is one of the problems.

    You have a line "cmdClear_Click" in "Sub btnAdd_Click()". There is no "cmdClear_Click" routine. (maybe it should be "btnClear_Click"???)

    The next error is
    Code:
    Private Sub btnNew_Click()
        'Increment of PONumber by 1
        Dim PONO As String
        PONumber = Me.txtPONO
        Me.txtPONumber = Me.txtPONO + 1
    End Sub
    There is no variable/control named "PONumber". Maybe you should use the declared variable "PONO"?
    There is also no control named "Me.txtPONumber". There is only a control named "Me.txtPONO".

    After fixing the above errors, the form opens with no errors. But it doesn't make sense to me (not that it needs to). You open a recordset that returns 12 records and you arbitrarily fill controls with data from a random record. Weird.


    Also note that you never close and destroy the ADOB recordset.
    (I would use DAO rather than ADODB because the data/tables are in the same database.)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-Time Error '3265': Item Not Found In This Collection
    By Voodeux2014 in forum Programming
    Replies: 3
    Last Post: 01-29-2016, 09:04 AM
  2. Item Not Found In This Collection (ListBox)
    By Voodeux2014 in forum Forms
    Replies: 8
    Last Post: 10-19-2015, 11:09 AM
  3. item not found in this collection
    By rockell333 in forum Queries
    Replies: 1
    Last Post: 09-24-2015, 03:20 PM
  4. Replies: 8
    Last Post: 08-18-2015, 04:35 PM
  5. Item Not Found In Collection For TableDefs
    By gammaman in forum Modules
    Replies: 2
    Last Post: 06-17-2015, 07:55 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums