Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    45

    Unanswered: dinamical population of tables

    hallo,
    I have two linked tables (organisations and activities), I'm trying to export the content of this two tables to other two tables (organisation_exp and activities_exp). Not all the records are exported, only the ones who respect some criterias. It exports only 10 organisations each time.
    I'm doing this in this way:
    I added a field "exported(yes/no)" in the source organisation table. During the exportation the field change from no (default) to yes. during the exportation the code also check this yes-no field and will export only the organisations that are not yet exported.
    For the organisation table there is no problem, everything works like it should. They are exported ten at the time to the organisation_exp table. Then I have to add the activities, but since the source tables are linked I have to export only the activities linked to the exported organisations. I compare the exported org table with the activity source table and then export it. It works fine but only for the first time (first ten organisation's activities). If I repeat the operation it will add only the next 10 organisations but not the linked activities.

    Thanks for suggestions


    Here is the code I'm working on:

    Sub Export_CallCenter()

    'recordsets variables
    Dim Count As Integer
    Dim StrSQL_exp_org As String
    Dim StrSQL_exp_act As String
    Dim dBase As DAO.Database
    Dim rs_org As DAO.Recordset
    Dim rs_exp_org As DAO.Recordset
    Dim rs_act As DAO.Recordset
    Dim rs_exp_act As DAO.Recordset


    'organization query variables
    Dim StrSELECT1 As String
    Dim StrSELECT2 As String
    Dim StrSELECT3 As String
    Dim StrFROM1 As String
    Dim StrFROM2 As String
    Dim StrWHERE1 As String
    Dim StrWHERE2 As String
    Dim StrWHERE3 As String
    Dim StrORDERBY1 As String
    Dim StrORDERBY2 As String

    'activity query variables
    Dim StrSELECT_act As String
    Dim StrFROM1_act As String
    Dim StrFROM2_act As String
    Dim StrWHERE_act As String

    'query the organizations
    StrSELECT1 = "SELECT to.Org_ID, to.Org_Longname, to.Org_OTypeID, tof.Off_ID, tc.Cou_ID, to.Org_Exported, "
    StrSELECT2 = "IIf([Cou_ID]=206,'" & Forms!frm_Export_CallCenter.[cbo_ch] & "',IIf([Cou_ID]=14, '" & Forms!frm_Export_CallCenter.[cbo_au] & "',"
    StrSELECT3 = "IIf([Cou_ID]=74,'" & Forms!frm_Export_CallCenter.[cbo_fr] & "',IIf([Cou_ID]=81,'" & Forms!frm_Export_CallCenter.[cbo_de] & "',0)))) AS Expr1 "
    StrFROM1 = "FROM tbl_Organization AS [to] LEFT JOIN (tbl_Country AS tc RIGHT JOIN tbl_Office AS tof "
    StrFROM2 = "ON tc.Cou_ID = tof.Off_CountryID) ON to.Org_ID = tof.Off_OrganizationID "
    StrWHERE1 = "WHERE (((to.Org_OTypeID)=5) AND ((tc.Cou_ID)=206)) OR (((to.Org_OTypeID)=5) AND ((tc.Cou_ID)=81)) "
    StrWHERE2 = "OR (((to.Org_OTypeID)=5) AND ((tc.Cou_ID)=74)) OR (((to.Org_OTypeID)=5) AND ((tc.Cou_ID)=14)) "
    StrORDERBY1 = " ORDER BY IIf([Cou_ID]=206,'" & Forms!frm_Export_CallCenter.[cbo_ch] & "',IIf([Cou_ID]=14, '" & Forms!frm_Export_CallCenter.[cbo_au] & "',"
    StrORDERBY2 = "IIf([Cou_ID]=74,'" & Forms!frm_Export_CallCenter.[cbo_fr] & "',IIf([Cou_ID]=81,'" & Forms!frm_Export_CallCenter.[cbo_de] & "',0)))), to.Org_Longname;"

    'query the activities
    StrSELECT_act = "SELECT tor.Org_ID, tt.Tra_ID, tt.Tra_Description, tt.Tra_Date, tt.Tra_OfagID, tt.Tra_ToWhom, tt.Tra_Content, tt.Tra_Feedback, tt.Tra_AndNow , tt.Tra_OrganizationID, tt.Tra_AndNowDate "
    StrFROM1_act = "FROM (tbl_Organization AS tor LEFT JOIN (tbl_Country AS tc RIGHT JOIN tbl_Office AS tof ON tc.Cou_ID = tof.Off_CountryID) ON "
    StrFROM2_act = "tor.Org_ID = tof.Off_OrganizationID) RIGHT JOIN tbl_TrackedActivity AS tt ON tor.Org_ID = tt.Tra_OrganizationID "
    StrWHERE_act = "WHERE (tor.Org_OTypeID=5 AND tc.Cou_ID=206) OR (tor.Org_OTypeID=5 AND tc.Cou_ID=81) OR (tor.Org_OTypeID=5 AND tc.Cou_ID=74) OR (tor.Org_OTypeID=5 AND tc.Cou_ID=14);"

    'query the export tables
    StrSQL_exp_org = "SELECT * FROM tbl_Organization_exp"
    StrSQL_exp_act = "SELECT * FROM tbl_TrackedActivity_exp"

    'open recordsets
    DoCmd.SetWarnings False
    Set dBase = CurrentDb()
    Set rs_org = dBase.OpenRecordset(StrSELECT1 & StrSELECT2 & StrSELECT3 & StrFROM1 & StrFROM2 & StrWHERE1 & StrWHERE2 & StrORDERBY1 & StrORDERBY2)
    Set rs_exp_org = dBase.OpenRecordset(StrSQL_exp_org)
    Set rs_act = dBase.OpenRecordset(StrSELECT_act & StrFROM1_act & StrFROM2_act & StrWHERE_act)
    Set rs_exp_act = dBase.OpenRecordset(StrSQL_exp_act)

    'loop to add the organizations and tick them as exported
    Count = 0
    rs_org.MoveFirst
    Dim Uffa As Boolean
    Uffa = False
    Do While Uffa = False
    If rs_org.Fields(5).Value = -1 Then
    Uffa = False
    rs_org.MoveNext
    Else
    Uffa = True
    Do Until Count = 10
    rs_exp_org.AddNew
    rs_exp_org!Org_ID = rs_org!Org_ID
    rs_exp_org!Org_Longname = rs_org!Org_Longname
    rs_org.Edit
    rs_org.Fields(5).Value = -1
    rs_org.Update
    rs_org.MoveNext
    rs_exp_org.Update
    Count = Count + 1
    Loop
    End If
    Loop

    'Reopen the populated organizations recordset
    Set rs_exp_org = dBase.OpenRecordset(StrSQL_exp_org)

    'loop to add the activities
    rs_exp_org.MoveFirst
    rs_act.MoveFirst
    Do Until rs_exp_org.EOF Or rs_act.EOF
    If rs_act!Tra_OrganizationID = rs_exp_org!Org_ID Then
    rs_exp_act.AddNew
    rs_exp_act!Tra_id = rs_act!Tra_id
    rs_exp_act!Tra_OrganizationID = rs_exp_org!Org_ID
    rs_exp_act.Update
    rs_act.MoveNext
    rs_exp_org.MoveNext
    Else
    rs_act.MoveNext
    End If
    Loop

    'close recordsets and clear variables
    rs_exp_org.Close
    rs_org.Close
    Set rs_exp_org = Nothing
    Set rs_org = Nothing
    Set dBase = Nothing

    MsgBox "10 organizations were exported successfully"
    Forms!frm_Export_CallCenter!frm_export_prova.Form. Requery

    End Sub

  2. #2
    Join Date
    Jun 2004
    Posts
    92
    Messy. Why don't you use an unmatched query to find which ones have been exported and then go from there. I think you are going overboard with your function.

Posting Permissions

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