+ Reply to Thread
Results 1 to 14 of 14

runtime error 3265 error in a VBA Query to pull info from ERP Database

  1. #1
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    runtime error 3265 error in a VBA Query to pull info from ERP Database

    Hi,

    I am working on another version of this code I am using to extract information from our ERP database, via MSQuery and VBA

    This code is supposed to compare the same table to itself and return workorders that contain all parts listed in cells A1:A10. Basically doing a "Where Used" Query...

    It all seems to run okay.. except it is giving me an error at the red line.
    Run-time error '3265' Item cannot be found in the collection corresponding to the requested name or ordinal.
    I can't get it to recognize and pull the Work Order Base ID.

    Please Login or Register  to view this content.
    Last edited by NBVC; 05-12-2010 at 01:13 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Does this work:
    Please Login or Register  to view this content.
    or this:
    Please Login or Register  to view this content.
    Remember what the dormouse said
    Feed your head

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Hi Romperstomper,

    I had tried the first one before with the same error message.

    The second option, though, does not give the error, but it did not return what I expected. Does the 0 mean the first item from the SELECT part?

    It only returns a couple of 1's which I think relates to the WORKORDER_LOT_ID (the second item)... I tried changing to rsOra(-1) but got error... and there should be a lot more returns.

    The SQL statement on it's own in MSQUERY returned many results.

    I think that is a clue..

    Based on above, I tried

    Please Login or Register  to view this content.
    and it returned the same couple of 1's... so it is not recognizing the first item in the SELECT DISTINCT statement...
    Last edited by NBVC; 05-10-2010 at 10:54 AM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Can it be the DISTINCT causing the problem?

    I tried rearranging the items after SELECT DISTINCT so that REQUIREMENT.PART_ID was first and now the same error is highlighting this part of the code:

    Please Login or Register  to view this content.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    I think I've got a workaround...

    I just added a non-relevant field from the REQUIREMENT table as the first item and then it works to pull the proper fields...

    But now I think my logic in this section is wrong:

    Please Login or Register  to view this content.
    It is only pulling up 2 work orders, which seems to be because I only inputted 2 parts in A1:A10 and it is only looping twice.

    What I want it to do it pull as many work order that are found that share those 2 parts in their respective tables. Not sure this is possible. It is looking like I need to pull as many copies of the Requirements table as there are parts to look for? Is that correct?

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Where do you want to put all the workorder data if there's more than one? You'll need an inner loop, or a copyfromrecordset.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    I just want to list them underneath, so in Row 12 onwards.

    I will need the Workorder_Base_Id, Workorder_Lot_Id, Workorder_Sub_ID.

    There could be hundreds of matching records that contain up to 10 parts being queried. All the parts must be in all the workorders.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Quote Originally Posted by NBVC View Post
    There could be hundreds of matching records that contain up to 10 parts being queried. All the parts must be in all the workorders.
    You lost me with that bit (I'm a bit simple!). Can you give an example of what you mean?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    My database is full of workorders that contain bills of material with many parts within.

    A user want to search for all workorders that contain parts 001XX and 002XX

    There could be hundreds of work orders that have these 2 parts in their respective BoMs. I need to display those.

    the user may also want to search for workorders that all contain up to 10 of the same parts.

    so if I am looking for the parts 001XX and 002XX in the 3 workorders listed below, i would only get WO1 and WO2 returned since they both have both parts.

    WO1
    123AB
    456BC
    001XX
    224RC
    002XX
    005XY

    WO2
    123AC
    002XX
    456BC
    001XX
    245DD
    005XY


    WO2
    123AC
    456BC
    001XX
    245DD
    005XY

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Can one BOM have multiple instances of the same part number?

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Yes, there is on occassion multiple instances of the same product.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    This is not required anymore.

    I figured out another way.

    I just queried each part separately on 10 sheets

    Then combined the queries onto 1 sheet and used formulas to find matches where the part was contained in at least 10 different work orders...

    The speed of results is dependent mostly on how frequent those queried parts are contained in separate workorders... i.e. on number of items gathered from all the sheets.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Glad to hear it - the paths I was going down involved a lot of maketable queries and crosstabs!

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: runtime error 3265 error in a VBA Query to pull info from ERP Database

    Yes, I figured the fact the you hadn't come back with a response meant it was going to be a b..tch! to get to using VBA and all the relational querying that was going to have to go on... so I figured, why not make it easy and make separate queries and put them all together, sort the thing so that the workorders are grouped together and then see if there are at least as many unique parts for the workorders as there are parts queried.

    I am just kicking the tires on it to ensure I covered all possible bases.

    Thanks for the concern and effort though. You never know, if this crashes and burns on me, I may need to revisit another alternative.. but I am feeling confident.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1