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

    matching problem with records... (6.0)

    I want to fill record NDG in table of ANAGRAFICA_2 based field COPE in this same table and other in a COPE_NDG_4580...

    Note:
    matching only the first 8 caharcter becouse COPE of COPE_NDG_4580 not have cin...

    maked this code but naturally not work!!!

    Option Explicit
    Global RSD1 As DAO.Recordset
    Global DB1 As DAO.Database
    Dim DB As DAO.Database
    Dim RSD As DAO.Recordset
    Dim TEST_RECORD, TEST_COUNT As String
    Dim CONTA_RECORD As String
    Dim COPE1, COPE As String
    Sub INSERISCI_NDG()

    'Application.EnableEvents = False
    'Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual

    Set DB = DBEngine.OpenDatabase("gcd01f4500datipubblicaappli cazioniCOPE_NDG_4580.mdb")
    Set RSD = DB.OpenRecordset("CAMPANIA_COPE_NDG")
    Set DB1 = DBEngine.OpenDatabase("gcd01f4500datipubblicaappli cazioniANAGRAFICA_2.mdb")
    Set RSD1 = DB1.OpenRecordset("ANAGRAFICA1")
    RSD1.Index = "COPE"

    CONTA_RECORD = RSD.RecordCount

    Do While Not RSD1.EOF

    COPE = RSD("COPE")
    COPE1 = Left(RSD1("COPE"), 8)
    RSD1.Seek "=", COPE

    If RSD1.NoMatch Then
    GoTo NON_TROVATO
    Else
    RSD1.AddNew
    RSD1!NDG = RSD!NDG
    RSD1.Update
    End If
    NON_TROVATO:

    RSD1.MoveNext

    Loop

    RSD.Close
    Set RSD = Nothing
    DB.Close
    Set DB = Nothing
    RSD1.Close
    Set RSD1 = Nothing
    DB1.Close
    Set DB1 = Nothing

    'Application.EnableEvents = True
    'Application.ScreenUpdating = True
    'Application.Calculation = xlCalculationAutomatic

    End Sub

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

    Re: matching problem with records... (6.0)

    Why are you suddenly using DAO instead of ADO?

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

    Re: matching problem with records... (6.0)

    hummmm.... not a wizard with ADO.
    But if you suggest me a correct way with code in ADO, sure is welcome...
    From you all are welcome!
    Tks for patience.
    Sal.

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

    Re: matching problem with records... (6.0)

    DAO is fine, I was just wondering why you were using it after your previous database code always used ADO.

    I find your code rather confusing, can you try to explain as clearly as you can what you want to do?

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

    Re: matching problem with records... (6.0)

    i want compare "one a one" records in field COPE in table of first mdb and fill NDG based the record matched in filed COPE of second table....
    If you see in the second mdb all record in NDG are blank...

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

    Re: matching problem with records... (6.0)

    Your code does things the wrong way round, and you shouldn't add records, you should update existing records.

    Since you want to search in CAMPANIA_COPE_NDG, you must set an index on the COPE field in that table. Let's say you name this index PrimaryKey. You can then use

    Sub INSERISCI_NDG()
    On Error GoTo ErrHandler

    Set DB = DBEngine.OpenDatabase("...COPE_NDG_4580.mdb")
    Set RSD = DB.OpenRecordset("CAMPANIA_COPE_NDG")
    Set DB1 = DBEngine.OpenDatabase("....ANAGRAFICA_2.mdb")
    Set RSD1 = DB1.OpenRecordset("ANAGRAFICA1")
    RSD.Index = "PrimaryKey"

    Do While Not RSD1.EOF
    COPE1 = Left(RSD1("COPE"), 8)
    RSD.MoveFirst
    RSD.Seek "=", COPE1
    If Not RSD.NoMatch Then
    RSD1.Edit
    RSD1!NDG = RSD!NDG
    RSD1.Update
    End If
    RSD1.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    RSD.Close
    Set RSD = Nothing
    DB.Close
    Set DB = Nothing
    RSD1.Close
    Set RSD1 = Nothing
    DB1.Close
    Set DB1 = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You must fill in the correct path - I had to change it for testing.

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

    Re: matching problem with records... (6.0)

    wORK EXCELLENT...
    1) But why . RSD1.Edit?
    2) and is possible to skip the record if is already filled?

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

    Re: matching problem with records... (6.0)

    1) You had AddNew. That would add a completely new record, but that's not what you want. You want to fill in a field in an existing record. In DAO, you must use Edit before you can change a value in a record.

    2) You could build in a test:

    ...
    Do While Not RSD1.EOF
    If IsNull(RSD1!NDG) Then
    COPE1 = Left(RSD1!COPE, 8)
    RSD.MoveFirst
    RSD.Seek "=", COPE1
    If Not RSD.NoMatch Then
    RSD1.Edit
    RSD1!NDG = RSD!NDG
    RSD1.Update
    End If
    End If
    RSD1.MoveNext
    Loop
    ...

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

    Re: matching problem with records... (6.0)

    no words...
    Tks.

    note:
    i have insert a little, little, progessbar take a look and tell me if is correct.
    i have insert in the userform the call to run code...
    suggest me if you know a must elegant method to insert a progressbar

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

    Re: matching problem with records... (6.0)

    That works well.

    I don't know how many records you have to process in reality. With the sample databases you attached, the update process is so quick that you hardly see the userform before it is closed again, so a progressbar is not really necessary. If the number of records in the real database is much larger, the progressbar is a nice addition.

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

    Re: matching problem with records... (6.0)

    .... in real mdb have in source 234.678 recods and in destination 456.880!
    but this is an old code to fill orogressbar , but dont understand really "/100" , why not /1000 or /10?

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

    Re: matching problem with records... (6.0)

    The progressbar runs from 0% to 100%. When you've processed half the records, I / CONTA_RECORD is 0.5. The *100 converts this to 50 (percent).

Posting Permissions

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