Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Chapter 3 in the series "Weird Access Issues"....

    I have the following code to prepend data to insert and update strings:

    [codebox] Dim PrependInsert, PrependUpdate As String
    Dim adoRst2 As New ADODB.Recordset
    mySQL = "SELECT [Form Dln],[LN],[FN] from [qryMDS_Items_AA8a Not Null] where [Form Dln] ='" & FormDLN & "';"
    adoRst2.Open mySQL, adoCon

    PrependInsert = "[Form Dln], '" & FormDLN & "', [LN], '" & adoRst2.Fields([LN]) & "', [FN], " & adoRst2.Fields([FN]) & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', [LN] = '" & adoRst2.Fields([LN]) & "', [FN] = " & adoRst2.Fields([FN]) & "', "

    adoRst2.Close
    [/codebox]

    When I click the button I keep getting the error "Item cannot be found in the collection according to the requested name or ordinal." which I take to mean i have mis-named a column specification. HOWEVER, in Debug, I can hover over the adoRst2.Fields and the damned value is in fact present. The code breaks before getting to any actual inserts or updates, so i don't think that is the issue.

    I don't want to weary you will a screen shot for all of them, but am attaching one to indicate that Access appears to have it in for me....

    not sure what is going on, perhaps some kind of late-binding voodoo?

    TIA
    Attached Images Attached Images

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='steve_skelton13' post='798238' date='15-Oct-2009 22:30']Chapter 3 in the series "Weird Access Issues"....

    I have the following code to prepend data to insert and update strings:

    [codebox] Dim PrependInsert, PrependUpdate As String
    Dim adoRst2 As New ADODB.Recordset
    mySQL = "SELECT [Form Dln],[LN],[FN] from [qryMDS_Items_AA8a Not Null] where [Form Dln] ='" & FormDLN & "';"
    adoRst2.Open mySQL, adoCon

    PrependInsert = "[Form Dln], '" & FormDLN & "', [LN], '" & adoRst2.Fields([LN]) & "', [FN], " & adoRst2.Fields([FN]) & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', [LN] = '" & adoRst2.Fields([LN]) & "', [FN] = " & adoRst2.Fields([FN]) & "', "

    adoRst2.Close
    [/codebox]

    When I click the button I keep getting the error "Item cannot be found in the collection according to the requested name or ordinal." which I take to mean i have mis-named a column specification. HOWEVER, in Debug, I can hover over the adoRst2.Fields and the damned value is in fact present. The code breaks before getting to any actual inserts or updates, so i don't think that is the issue.

    I don't want to weary you will a screen shot for all of them, but am attaching one to indicate that Access appears to have it in for me....

    not sure what is going on, perhaps some kind of late-binding voodoo?

    TIA[/quote]

    Ignore that last reply.

    I think if you are using the format adoRst2.Fields([LN])

    The Field name must be quoted

    adoRst2.Fields("[LN]") Unless LN is an available numeric value or String variable or Field that contains the name of the field

    Or maybe I don't understand what it is trying to do
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes, it's defined and set earlier:

    Dim adoCon As New ADODB.Connection
    Dim adoRst As New ADODB.Recordset
    Set adoCon = CurrentProject.Connection
    adoRst.Open mySQL, adoCon

    ...Code to check if there's data to operate on...

    Dim PrependInsert, PrependUpdate As String
    Dim adoRst2 As New ADODB.Recordset
    mySQL = "SELECT [Form Dln],[LN],[FN] from [qryMDS_Items_AA8a Not Null] where [Form Dln] ='" & FormDLN & "';"
    adoRst2.Open mySQL, adoCon

    PrependInsert = "[Form Dln], '" & FormDLN & "', [LN], '" & adoRst2.Fields([LN]) & "', [FN], " & adoRst2.Fields([FN]) & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', [LN] = '" & adoRst2.Fields([LN]) & "', [FN] = " & adoRst2.Fields([FN]) & "', "

    adoRst2.Close

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='steve_skelton13' post='798242' date='15-Oct-2009 22:39']yes, it's defined and set earlier:

    Dim adoCon As New ADODB.Connection
    Dim adoRst As New ADODB.Recordset
    Set adoCon = CurrentProject.Connection
    adoRst.Open mySQL, adoCon

    ...Code to check if there's data to operate on...

    Dim PrependInsert, PrependUpdate As String
    Dim adoRst2 As New ADODB.Recordset
    mySQL = "SELECT [Form Dln],[LN],[FN] from [qryMDS_Items_AA8a Not Null] where [Form Dln] ='" & FormDLN & "';"
    adoRst2.Open mySQL, adoCon

    PrependInsert = "[Form Dln], '" & FormDLN & "', [LN], '" & adoRst2.Fields([LN]) & "', [FN], " & adoRst2.Fields([FN]) & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', [LN] = '" & adoRst2.Fields([LN]) & "', [FN] = " & adoRst2.Fields([FN]) & "', "

    adoRst2.Close[/quote]


    See my amended post re referencing the ado Recordset field
    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    adoRst2.Fields([LN]) is the field whose name is the value of LN.
    According to your screenshot, the value of LN is "BABITZKE".
    So you're referring to a field in adoRst2 named BABITZKE.
    Are you sure that's what you intended?
    If you wanted to refer to a field named LN, you should use adoRst2.Fields("LN") or alternatively adoRst2!LN

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, let me run it with quotes and see if that helps. but I am a little confused that "[FN]='" & adoRst2.Fields([FN]).value & "' would mean anything other than [FN]='<value from db>'

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope! Same thing, even with quotes:

    PrependInsert = "[Form Dln], '" & FormDLN & "', ""LN"", '" & adoRst2.Fields([LN]) & "', ""FN"", " & adoRst2.Fields([FN]) & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', ""LN"" = '" & adoRst2.Fields([LN]) & "', ""FN"" = " & adoRst2.Fields([FN]) & "', "

    Another thing: when I first hover over during Debug, the bubble pop-up says there is indeed no object in the collection, but when i do it again, viola! the data is there.

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='steve_skelton13' post='798246' date='15-Oct-2009 22:51']Well, let me run it with quotes and see if that helps. but I am a little confused that "[FN]='" & adoRst2.Fields([FN]).value & "' would mean anything other than [FN]='<value from db>'[/quote]

    I think you might need to explain exactly what you are trying to do and what is in the fields.
    It's always tough debugging your own code.
    Other peoples is even harder.

    Also, maybe try and just run adoRst2.Fields([FN]) to a variable and [FN] to a variable to see what is in the values.
    Andrew

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try

    PrependInsert = "[Form Dln], '" & FormDLN & "', [LN], '" & adoRst2.Fields("LN") & "', [FN], " & adoRst2.Fields("FN") & "', "
    PrependUpdate = "[Form Dln] ='" & FormDLN & "', [LN] = '" & adoRst2.Fields("LN") & "', [FN] = " & adoRst2.Fields("FN") & "', "

    as suggested by Andrew and me.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    sorry, guys -- that last one worked. friday has come early this week...

Posting Permissions

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