Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find name of filed and insert value from sheet (2000 sr-1)

    In line 2 of sheet have many value (AB,AS,AT,BCR,BE,BL,BNT, BS....)
    in a table of mdb hav evariuos filed named with same value of sheet.
    How to insert the value correspondent for each filed from line 1 of sheet???

    Example:
    cell B2 = AB, find in table the correspondent field and insert 17
    cell C2 = AS, find in table the correspondent field and insert 5
    Ecc...

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

    Re: find name of filed and insert value from sheet (2000 sr-1)

    Open an ADODB connection to the database, and an ADODB recordset rst on the table. You can then use something like this:

    Dim Colonna As Long
    For Colonna = 1 to ...
    rst.Fields(Cells(2, Colonna)) = Cells(1, Colonna)
    Next Colonna
    rst.Update

    Don't forget to close the recordset and the connection at the end.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet (2000 sr-1)

    Tks for suggestion...
    But have error in: RS.Fields(Cells(2, Colonna)) = Cells(1, Colonna)

    code:

    Option Explicit
    Global Const gPROVADatabasePath2 = "Data Source=GCD01F4500DATIPUBBLICAANTIRICOPERATORI.MDB; "
    Sub test()

    Dim Colonna As Long
    Dim CN As ADODB.Connection, RS As ADODB.Recordset, R As Long, N As Long
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    Set RS = New ADODB.Recordset

    RS.Open "TEST", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    For Colonna = 2 To 74
    RS.Fields(Cells(2, Colonna)) = Cells(1, Colonna)
    Next Colonna

    RS.Update
    RS.Close
    Set RS = Nothing
    CN.Close
    Set CN = Nothing

    End Sub

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

    Re: find name of filed and insert value from sheet (2000 sr-1)

    That means that there is a value in row 2 that does not correspond to a field in the table. So you should either add a field to the table for each value that may occur in row 2 of the worksheet, or you must add a line

    On Error Resume Next

    above the line

    For Colonna = ...

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet (2000 sr-1)

    OK...
    Macro run correct but not write number in rekord(?!)
    Option Explicit
    Global Const gPROVADatabasePath2 = "Data Source=GCD01F4500DATIPUBBLICAANTIRICOPERATORI.MDB; "
    Sub TEST_OPERATORI()

    Dim COLONNA As Long
    Dim CN As ADODB.Connection, RS As ADODB.Recordset, R As Long, N As Long
    Set CN = New ADODB.Connection
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    Set RS = New ADODB.Recordset
    RS.Open "TEST", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    On Error Resume Next

    For COLONNA = 2 To 74
    RS.Fields(Cells(2, COLONNA)) = Cells(1, COLONNA)
    Next COLONNA

    RS.Update

    RS.Close
    Set RS = Nothing
    CN.Close
    Set CN = Nothing

    End Sub

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

    Re: find name of filed and insert value from sheet (2000 sr-1)

    Change the line

    RS.Fields(Cells(2, COLONNA)) = Cells(1, COLONNA)

    to

    RS.Fields(Cells(2, COLONNA).Value) = Cells(1, COLONNA)

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    still no work...
    no insert the numeric value from line 1 of sheet in records....

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

    Re: find name of filed and insert value from sheet

    I have attached a zip file with the workbook with the macro and the database with the result of running the macro.
    Attached Files Attached Files

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    excellent!!!!
    only you...
    Tks for patience.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    Hans have a little request..., (Peraphs for you;-))
    I have named all field of table to the hand, but in real project the name of fileds are present in the sheet attached (column A)...
    Now the column A are updated dinamiclly from other application...
    Is possible to add the new field name in the table from column A?
    Example:
    Name of filed = CR not existis in table add a new name filed numeric property, Name of filed = DU not existis in table add a new name filed numeric property... ecc. In this casei am sure all cell value of sheet and all fileds in table are the same.
    Hope you have understand me.
    Tks for all.

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

    Re: find name of filed and insert value from sheet

    See the attached code. You must run this before filling the record. You cannot fill the record and add new fields at the same time, Access does not allow that.
    Attached Files Attached Files

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    NO DUBT...!!!
    You know my reply on your code.
    Tks as usual.
    Note:
    tks always for note "You cannot fill the record and add new fields at the same time, Access does not allow that."
    adding in my mind new knoledgment.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    ahhhhhh.... but now with last update on my project i can comment this line in first part of code:
    On Error Resume Next
    or to be sure for error maintain this line?

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

    Re: find name of filed and insert value from sheet

    That line is not needed any more, since you know that the field exists.

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find name of filed and insert value from sheet

    ok...

Posting Permissions

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