Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO Seek Function & Array Help (2000)

    I have a question about one of the arguments in Seek function using an Array. Below is a sample of my code and a picture of the recordset. FYI: Field1, Field2, Field3 are the PrimaryKey index on the recordset.
    =========================================
    strField1 =
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ADO Seek Function & Array Help (2000)

    From what I can see I think you need the additional code in bold.
    =========================================
    strField1 =

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    Well I guess my long question paid off! Thanks a bunch, Pat! Adding the code in the appropriate spot worked great.

    Sarah

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ADO Seek Function & Array Help (2000)

    On looking further into this you should be using the .NoMatch test rather than .EOF or .BOF, and I would take out my code and change your code using the adSeekLastEQ parameter and and see what happens.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    I got run-time error 438 Object doesn't support this property or method. below is the code that caused the error.

    ====================================
    With rs
    .Index = "PrimaryKey"
    .Seek Array(strField1, strField2), adSeekFirstEQ
    If .NoMatch Then
    .AddNew
    ![Field1] = strField1
    ![Field2] = strField2
    ![Field3] =

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ADO Seek Function & Array Help (2000)

    Sorry about that, I must admit I looked up the A97 help with this one, maybe it is not liked in ADO but it is ok in DAO? I have done no work yet with ADO.
    Maybe go back to what we decided earlier?
    Pat

  7. #7
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    yeah, I'm using ADO and that' s probably one of the functions that doesn't convert over. I'll do what you originally suggested.

    sarah

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO Seek Function & Array Help (2000)

    NoMatch is a property of a DAO recordset. In ADO you have to use both the BOF and EOF tests to determine the equivalent.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO Seek Function & Array Help (2000)

    In addition to the problem with trying to use NoMatch with an ADO recordset (doesn't work, it's strictly DAO), you've got what may be a logic error in your code. What are you trying to do here?

    <pre> ![Field3] = strField3 + 1</pre>


    If you want to increment the number stored in strField3, it won't work. The result of that should be to concatenate another "1" to the string already in strField3. Is that what you want to do? SQL Server uses the "+" as a concatenation character but it can give you some unexpected results in Access, so it's best to use a "+" for mathematic operations and an "&" for concatenation unless you are doing something tricky with possible Null values.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    Charlotte,

    I am trying to add (not concatenate) 1 to the value in Field3. I'm storing the found value of Field3 in the strField3 variable. Then when I add a new record to the recordset the value of the new Field3 is to be one higher than the found value.

    So I do a seek and find that the value of Field3 in the found record is 2. I store "2" in the strField3 variable. Then add a new record to the recordset and set Fields1&2 to the found values and set Field3 to strField3+1 which equals 3.

    Sarah

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: ADO Seek Function & Array Help (2000)

    Sarah

    I think the question then would be: why would you store the value in a string variable if you want to perform arithmetic on it, not in a numeric data type?

    Best wishes

    Jeremy

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO Seek Function & Array Help (2000)

    If you're using strings, then you have to convert the string to a value in order to increment it. Try:

    = CStr(Val(strField3) + 1)

    Or you could simply use numeric variables and stop messing about with strings. Was there a reason for declaring string variables?
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    I guess I just used a string variable for this because I'm familar with this type of variable. The code is working for me as it is. But I guess a interger data type variable would be better? make sense, store the number as a number. [img]/forums/images/smilies/smile.gif[/img] I guess since my recordset was made up of numbers I should store it the values as numerical variables.

    I'll try it and see if it works any differently/better.

    Sarah

  14. #14
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Seek Function & Array Help (2000)

    Well I changed my string variables to integer variables and Field1 to store in a Long variable (is was string also). Everything still works fine. Not sure what the benefit of changing them was. But it makes sense to store them as numerical variables.

    Sarah

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO Seek Function & Array Help (2000)

    One benefit is speed. It's faster to work with numbers than with text anyhow, and you no longer have to convert them, which also saves time.
    Charlotte

Posting Permissions

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