+ Reply to Thread
Results 1 to 12 of 12

My Attempt in using an Array but getting an error (3265)

  1. #1
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    My Attempt in using an Array but getting an error (3265)

    I am attempting to use an array to capture the row information based on the comparison match of policy numbers from the spreadsheet against the ADO table.

    The object of this macro is to compare the policy numbers entered on the spreadsheet against the policy numbers in database table. Once they match, I want to get the value of a field called "Block No" for the matched policy numbers from the table.

    Below are the code that I am using:

    Please Login or Register  to view this content.
    Any suggestions are welcomed. Thank you.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: My Attempt in using an Array but getting an error (3265)

    What are you actually trying to do here?
    Please Login or Register  to view this content.
    Also, why does the query appear to be for multiple policies?
    If posting code please use code tags, see here.

  3. #3
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: My Attempt in using an Array but getting an error (3265)

    There is a FormulaArray Statement. You might want to look into that.

    http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

  4. #4
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: My Attempt in using an Array but getting an error (3265)

    why don't you loop through the rows and fire an sql for each row , get the result and display.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: My Attempt in using an Array but getting an error (3265)

    Or use the recordset's Find method to locate each policy number.

  6. #6
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: My Attempt in using an Array but getting an error (3265)

    Hi Norie,

    Thank you for your response.

    The end user will enter the information (check number, policy number, check amount and etc) and once you are done - you will click a button to run the macro that will compare and retreive the information from the table based on the policy number(s) given.

    I wanted the matched data to be a collection.

    I hope that helps.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: My Attempt in using an Array but getting an error (3265)

    So what is the line of code you get the error on meant to be doing?

    Also, what do you mean you want the matched data to be a collection?

    I thought the object of the code was to compare the policy numbers on the worksheet to those in the recordset and where there is a match return the block no.

    If that is what you want to do you could use the Find method to move the recordset that matches a policy no and then you can get the corresponding block no.

  8. #8
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: My Attempt in using an Array but getting an error (3265)

    Thank you everyone for your suggestion and I will look into each of them.

    Below is a sample report of what I am trying to do:

    Check Receipt Transaction Log
    Monday, January 13, 2014

    Control Company Check Policy Check Processor Process to Verfied in Check Identification / Closed Block
    Number Number Number Number Amount to Susp Susp Date 110008 Resolution Comments Yes or No
    030000024
    030000042
    030000165
    030000506
    030000533
    030000560
    030000561
    030000820
    030000839
    030000840
    000000001

    There will be a list of policy numbers entered, once you run the macro -- it should compare against the ODBC table and retreive the following data from that table:

    1. Company
    2. Policy
    3. Status
    4. Plan Code
    5. Block No

    My focus is on the Policy Number and its corresponding Block No. If there is a match in the Policy Number, then my next criteria is to find out if the Block No equals"1011". If it does, then a message "Yes" is posted in the last column (Closed Block Yes or No). If the policy number does not match or the Block No does not "1011", then message is "No".

    I hope this helps in visualizing what I am trying to do. Thank you.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: My Attempt in using an Array but getting an error (3265)

    I think I would maybe do it something like this.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: My Attempt in using an Array but getting an error (3265)

    Hi Norie,

    I am trying to use your suggestion but getting a syntax error on:

    rst.Find("Policy = '" & PN.Value & "'",,,adBookmarkFirst)

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: My Attempt in using an Array but getting an error (3265)

    Oops, remove the ().
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    04-30-2007
    Posts
    20

    Re: My Attempt in using an Array but getting an error (3265)

    Thank you Norie, You are a genius!!

    It look so simple and cleaner than what I had. Here is the updated code:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Run Time Error 3265 - Item not found in this collection
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-23-2013, 07:19 AM
  2. Replies: 4
    Last Post: 03-17-2011, 09:24 AM
  3. Attempt to open file on error popup
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2010, 08:28 PM
  4. runtime error 3265 error in a VBA Query to pull info from ERP Database
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-12-2010, 03:59 PM
  5. error in attempt to warn about macro security
    By Pierre via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

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