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

    Unanswered: dinamical population of tables

    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
    Dim Uffa As Boolean
    Uffa = False
    Do While Uffa = False
    If rs_org.Fields(5).Value = -1 Then
    Uffa = False
    Uffa = True
    Do Until Count = 10
    rs_exp_org!Org_ID = rs_org!Org_ID
    rs_exp_org!Org_Longname = rs_org!Org_Longname
    rs_org.Fields(5).Value = -1
    Count = Count + 1
    End If

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

    'loop to add the activities
    Do Until rs_exp_org.EOF Or rs_act.EOF
    If rs_act!Tra_OrganizationID = rs_exp_org!Org_ID Then
    rs_exp_act!Tra_id = rs_act!Tra_id
    rs_exp_act!Tra_OrganizationID = rs_exp_org!Org_ID
    End If

    'close recordsets and clear variables
    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
    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