Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Module won't allow any pasting, additional variables etc?

Discussion in 'Information Technology' started by bradjensmith@gmail.com, Jul 28, 2006.

  1. bradjensmith@gmail.com

    bradjensmith@gmail.com
    Expand Collapse
    Guest

    I'm a novice and I'm probably missing something obvious, but I have a
    module that I cannot add any more variables or paste into etc. It
    errors with an Out of Memory error (although I've got a gig of ram on
    my laptop). Could it be that I didn't close an object correctly or I'm
    doing something else wrong?

    Any help would be greatly appreciated. Basically the module reads in
    data from a query and outputs to two text files and a table within the
    access database. Here's the module as it is now (forgive the crudeness
    due to inexperience).

    Function export_JOB_future()

    'Dim db As Database
    Dim EFF_DT As String
    Dim ACTIONDT As String
    Dim JOBENTRYDT As String
    Dim DEPTENTRYDT As String
    Dim GRADEENTRYDT As String
    Dim STEPENTRYDT As String
    Dim UNIONCD As String
    Dim USCAN As String

    '================================================================================================
    'SET DATABASE AND RECORDSETS
    '================================================================================================
    Set db = CurrentDb 'set the database to the current database

    JOB_tbl = "SELECT C.EMPLID,
    C.COMPANY,IIf(C.COMPANY='097','CAN',IIf(C.COMPANY='008','CAN','USA'))
    AS US_CAN_IND, " + _
    "GBL_XREF.D_GBL_EMPLID AS CANEMPLID,
    IIf([US_CAN_IND]='CAN',[D_GBL_EMPLID],C.EMPLID) AS ALTEMPLID, JOB.*,
    JOB.EFFDT " + _
    "FROM (JOB AS C LEFT JOIN GBL_XREF ON C.EMPLID = GBL_XREF.EMPLID) INNER
    JOIN JOB ON C.EMPLID = JOB.EMPLID " + _
    "WHERE (((C.EFFDT)=(SELECT MAX(Y.EFFDT) FROM JOB Y WHERE Y.EMPLID =
    C.EMPLID AND Y.EMPL_STATUS IN ('A','L','P') " + _
    "AND Y.EFFDT <= NOW())) AND ((C.EFFSEQ)=(SELECT MAX(Z.EFFSEQ) FROM JOB
    Z WHERE Z.EMPLID = C.EMPLID AND " + _
    "Z.EMPL_STATUS IN ('A','L','P') AND Z.EMPL_RCD = C.EMPL_RCD AND
    Z.EFFDT = C.EFFDT AND Z.EFFDT <= NOW())) " + _
    "AND ((JOB.EFFDT)= #6/5/2006#)); "

    Set in_recs = db.OpenRecordset(JOB_tbl)

    Set out_recs = db.TableDefs("DATASRCE_ID FUTURE TEMP
    TABLE").OpenRecordset()

    in_recs.MoveFirst

    '================================================================================================
    'OUTPUT TO TEXT FILE
    '================================================================================================
    Close #1 ' Close file.
    'create a text file
    'Open "H:\Docs\MPHRO\duke\siebel.txt" For Output As #1 Len = 400
    Open "C:\docs\US LOAD\CNVR_JOBfuture.txt" For Output As #1 Len = 1500

    Do While Not in_recs.EOF

    If IsNull(in_recs.Fields("JOB.EFFDT").Value) Then
    EFF_DT = "01Jan1900"
    Else
    EFF_DT = Format(CDate(in_recs.Fields("JOB.EFFDT").Value),
    "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("ACTION_DT").Value) Then
    ACTIONDT = ""
    Else
    ACTIONDT = Format(CDate(in_recs.Fields("ACTION_DT").Value),
    "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("JOB_ENTRY_DT").Value) Then
    JOBENTRYDT = ""
    Else
    JOBENTRYDT =
    Format(CDate(in_recs.Fields("JOB_ENTRY_DT").Value), "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("DEPT_ENTRY_DT").Value) Then
    DEPTENTRYDT = ""
    Else
    DEPTENTRYDT =
    Format(CDate(in_recs.Fields("DEPT_ENTRY_DT").Value), "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("GRADE_ENTRY_DT").Value) Then
    GRADEENTRYDT = ""
    Else
    GRADEENTRYDT =
    Format(CDate(in_recs.Fields("GRADE_ENTRY_DT").Value), "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("STEP_ENTRY_DT").Value) Then
    STEPENTRYDT = ""
    Else
    STEPENTRYDT =
    Format(CDate(in_recs.Fields("STEP_ENTRY_DT").Value), "ddmmmyyyy")
    End If

    If IsNull(in_recs.Fields("UNION_CD").Value) Then
    UNIONCD = "N"
    Else
    UNIONCD = "Y"
    End If


    Print #1, Tab(1); _
    in_recs.Fields("JOB.EMPLID").Value & "|" &
    in_recs.Fields("EMPL_RCD").Value & "|" & EFF_DT & "|" &
    in_recs.Fields("EFFSEQ").Value & "|" & "HRMS01" & "|" &
    in_recs.Fields("EMPL_STATUS").Value & "|" &
    in_recs.Fields("ACTION").Value & "|" & ACTIONDT & "|" &
    in_recs.Fields("ACTION_REASON").Value & "|" & _
    in_recs.Fields("JOB.COMPANY").Value & "|" &
    in_recs.Fields("PAYGROUP").Value & "|" &
    in_recs.Fields("ACCT_CD").Value & "|" &
    in_recs.Fields("FLSA_STATUS").Value & "|" &
    in_recs.Fields("FULL_PART_TIME").Value & "|" &
    in_recs.Fields("REG_TEMP").Value & "|" &
    in_recs.Fields("STD_HOURS").Value & "|" & _
    in_recs.Fields("JOBCODE").Value & "|" & JOBENTRYDT & "|" &
    in_recs.Fields("SAL_ADMIN_PLAN").Value & "|" &
    in_recs.Fields("GRADE").Value & "|" & GRADEENTRYDT & "|" &
    in_recs.Fields("STEP").Value & "|" & STEPENTRYDT & "|" &
    in_recs.Fields("TAX_LOCATION_CD").Value & "|" &
    in_recs.Fields("COMPRATE").Value & "|" & _
    in_recs.Fields("COMP_FREQUENCY").Value & "|" &
    in_recs.Fields("CHANGE_PCT").Value & "|" &
    in_recs.Fields("CHANGE_AMT").Value & "|" &
    in_recs.Fields("DEPTID").Value & "|" & DEPTENTRYDT & "|" & UNIONCD &
    "|" & in_recs.Fields("UNION_CD").Value & "|" &
    in_recs.Fields("LOCATION").Value & "|" & in_recs.Fields("SHIFT").Value
    & "|" & _
    in_recs.Fields("EMPL_TYPE").Value & "|" &
    in_recs.Fields("OFFICER_CD").Value & "|" &
    in_recs.Fields("CURRENCY_CD").Value & "|" & " " & "|" & " " & "|" & " "
    & "|" & " " & "|" & _
    in_recs.Fields("HOURLY_RT").Value & "|" &
    in_recs.Fields("MONTHLY_RT").Value & "|" &
    in_recs.Fields("ANNUAL_RT").Value & "|" &
    in_recs.Fields("GL_PAY_TYPE").Value & "|" &
    in_recs.Fields("BEN_STATUS").Value & "|" &
    in_recs.Fields("EMPL_CLASS").Value & "|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD";

    in_recs.MoveNext

    Loop

    Close #1 ' Close file.

    '================================================================================================
    'OUTPUT TO TEXT FILE
    '================================================================================================
    Close #1 ' Close file.
    in_recs.MoveFirst

    'Reset DataSrce Temp Table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "RESET FUTURE TEMP", acViewNormal, acEdit

    'create a text file
    Open "C:\docs\US LOAD\CNVR_PERS_TRNSLTNfuture.txt" For Output As #1 Len
    = 1500

    Do While Not in_recs.EOF

    If in_recs.Fields("US_CAN_IND") = "USA" Then
    USCAN = "HRMS01"
    Else
    USCAN = "HRMS02"
    End If

    Print #1, Tab(1); _
    USCAN & "|" & _
    in_recs.Fields("C.EMPLID").Value & "|" & _
    in_recs.Fields("ALTEMPLID").Value & "|" & _
    in_recs.Fields("C.EMPLID").Value & "|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD";


    With out_recs
    .AddNew
    !EMPLID = in_recs.Fields("C.EMPLID").Value
    !US_CAN_IND = in_recs.Fields("US_CAN_IND").Value
    !USCAN = USCAN
    !COMPANY = in_recs.Fields("C.COMPANY").Value


    .Update
    On Error Resume Next
    End With

    in_recs.MoveNext

    Loop

    Close #1 ' Close
    End Function
     
  2. Loading...


  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi.

    > It
    > errors with an Out of Memory error


    That's not really the underlying cause of the error. You're trying to
    concatenate too much into a string. Cut out the entire print statement that
    starts with:

    Print #1, Tab(1); _
    in_recs.Fields("JOB.EMPLID").Value & "|" &

    .. . . from the procedure and paste it into Notepad so that you can refer
    back to it and ensure that you still have the correct field names. Next,
    paste the following in its place within the module:

    sTemp1 = in_recs.Fields("JOB.EMPLID").Value & "|" & _
    in_recs.Fields("EMPL_RCD").Value & "|" & EFF_DT & "|" & _
    in_recs.Fields("EFFSEQ").Value & "|" & "HRMS01" & "|" & _
    in_recs.Fields("EMPL_STATUS").Value & "|" & _
    in_recs.Fields("ACTION").Value & "|" & ACTIONDT & "|" & _
    in_recs.Fields("ACTION_REASON").Value & "|" & _
    in_recs.Fields("JOB.COMPANY").Value & "|" & _
    in_recs.Fields("PAYGROUP").Value & "|" & _
    in_recs.Fields("ACCT_CD").Value & "|" & _
    in_recs.Fields("FLSA_STATUS").Value & "|" & _
    in_recs.Fields("FULL_PART_TIME").Value & "|" & _
    in_recs.Fields("REG_TEMP").Value & "|" & _
    in_recs.Fields("STD_HOURS").Value & "|" & _
    in_recs.Fields("JOBCODE").Value & "|" & JOBENTRYDT & "|" & _
    in_recs.Fields("SAL_ADMIN_PLAN").Value & "|" & _
    in_recs.Fields("GRADE").Value & "|" & GRADEENTRYDT & "|" & _
    in_recs.Fields("STEP").Value & "|" & STEPENTRYDT & "|" & _
    in_recs.Fields("TAX_LOCATION_CD").Value & "|" & _
    in_recs.Fields("COMPRATE").Value & "|"

    sTemp2 = in_recs.Fields("COMP_FREQUENCY").Value & "|" & _
    in_recs.Fields("CHANGE_PCT").Value & "|" & _
    in_recs.Fields("CHANGE_AMT").Value & "|" & _
    in_recs.Fields("DEPTID").Value & "|" & _
    DEPTENTRYDT & "|" & UNIONCD & "|" & _
    in_recs.Fields("UNION_CD").Value & "|" & _
    in_recs.Fields("LOCATION").Value & "|" & _
    in_recs.Fields("SHIFT").Value & "|" & _
    in_recs.Fields("EMPL_TYPE").Value & "|" & _
    in_recs.Fields("OFFICER_CD").Value & "|" & _
    in_recs.Fields("CURRENCY_CD").Value & "|" & _
    " " & "|" & " " & "|" & " " & "|" & " " & "|" & _
    in_recs.Fields("HOURLY_RT").Value & "|" & _
    in_recs.Fields("MONTHLY_RT").Value & "|" & _
    in_recs.Fields("ANNUAL_RT").Value & "|" & _
    in_recs.Fields("GL_PAY_TYPE").Value & "|" & _
    in_recs.Fields("BEN_STATUS").Value & "|" & _
    in_recs.Fields("EMPL_CLASS").Value & "|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    Format(Date, "ddmmmyyyy") & "|INITLOAD"

    Print #1, Tab(1); sTemp1 & sTemp2;

    Next, declare two more variables in your procedure's declarations section:

    Dim sTemp1 As String
    Dim sTemp2 As String

    .. . . and then finish your coding in this procedure.

    BTW, you should replace the "+" concatenation characters with ampersand
    characters, because when Jet optimizes SQL statements, it might add things
    together, instead of concatenating a string. This is a bug that's usually
    hard to detect. Always use the ampersand character for string concatenation
    in VBA and Jet SQL.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    <bradjensmith@gmail.com> wrote in message
    news:1149523162.831014.210970@j55g2000cwa.googlegroups.com...
    > I'm a novice and I'm probably missing something obvious, but I have a
    > module that I cannot add any more variables or paste into etc. It
    > errors with an Out of Memory error (although I've got a gig of ram on
    > my laptop). Could it be that I didn't close an object correctly or I'm
    > doing something else wrong?
    >
    > Any help would be greatly appreciated. Basically the module reads in
    > data from a query and outputs to two text files and a table within the
    > access database. Here's the module as it is now (forgive the crudeness
    > due to inexperience).
    >
    > Function export_JOB_future()
    >
    > 'Dim db As Database
    > Dim EFF_DT As String
    > Dim ACTIONDT As String
    > Dim JOBENTRYDT As String
    > Dim DEPTENTRYDT As String
    > Dim GRADEENTRYDT As String
    > Dim STEPENTRYDT As String
    > Dim UNIONCD As String
    > Dim USCAN As String
    >
    > '================================================================================================
    > 'SET DATABASE AND RECORDSETS
    > '================================================================================================
    > Set db = CurrentDb 'set the database to the current database
    >
    > JOB_tbl = "SELECT C.EMPLID,
    > C.COMPANY,IIf(C.COMPANY='097','CAN',IIf(C.COMPANY='008','CAN','USA'))
    > AS US_CAN_IND, " + _
    > "GBL_XREF.D_GBL_EMPLID AS CANEMPLID,
    > IIf([US_CAN_IND]='CAN',[D_GBL_EMPLID],C.EMPLID) AS ALTEMPLID, JOB.*,
    > JOB.EFFDT " + _
    > "FROM (JOB AS C LEFT JOIN GBL_XREF ON C.EMPLID = GBL_XREF.EMPLID) INNER
    > JOIN JOB ON C.EMPLID = JOB.EMPLID " + _
    > "WHERE (((C.EFFDT)=(SELECT MAX(Y.EFFDT) FROM JOB Y WHERE Y.EMPLID =
    > C.EMPLID AND Y.EMPL_STATUS IN ('A','L','P') " + _
    > "AND Y.EFFDT <= NOW())) AND ((C.EFFSEQ)=(SELECT MAX(Z.EFFSEQ) FROM JOB
    > Z WHERE Z.EMPLID = C.EMPLID AND " + _
    > "Z.EMPL_STATUS IN ('A','L','P') AND Z.EMPL_RCD = C.EMPL_RCD AND
    > Z.EFFDT = C.EFFDT AND Z.EFFDT <= NOW())) " + _
    > "AND ((JOB.EFFDT)= #6/5/2006#)); "
    >
    > Set in_recs = db.OpenRecordset(JOB_tbl)
    >
    > Set out_recs = db.TableDefs("DATASRCE_ID FUTURE TEMP
    > TABLE").OpenRecordset()
    >
    > in_recs.MoveFirst
    >
    > '================================================================================================
    > 'OUTPUT TO TEXT FILE
    > '================================================================================================
    > Close #1 ' Close file.
    > 'create a text file
    > 'Open "H:\Docs\MPHRO\duke\siebel.txt" For Output As #1 Len = 400
    > Open "C:\docs\US LOAD\CNVR_JOBfuture.txt" For Output As #1 Len = 1500
    >
    > Do While Not in_recs.EOF
    >
    > If IsNull(in_recs.Fields("JOB.EFFDT").Value) Then
    > EFF_DT = "01Jan1900"
    > Else
    > EFF_DT = Format(CDate(in_recs.Fields("JOB.EFFDT").Value),
    > "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("ACTION_DT").Value) Then
    > ACTIONDT = ""
    > Else
    > ACTIONDT = Format(CDate(in_recs.Fields("ACTION_DT").Value),
    > "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("JOB_ENTRY_DT").Value) Then
    > JOBENTRYDT = ""
    > Else
    > JOBENTRYDT =
    > Format(CDate(in_recs.Fields("JOB_ENTRY_DT").Value), "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("DEPT_ENTRY_DT").Value) Then
    > DEPTENTRYDT = ""
    > Else
    > DEPTENTRYDT =
    > Format(CDate(in_recs.Fields("DEPT_ENTRY_DT").Value), "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("GRADE_ENTRY_DT").Value) Then
    > GRADEENTRYDT = ""
    > Else
    > GRADEENTRYDT =
    > Format(CDate(in_recs.Fields("GRADE_ENTRY_DT").Value), "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("STEP_ENTRY_DT").Value) Then
    > STEPENTRYDT = ""
    > Else
    > STEPENTRYDT =
    > Format(CDate(in_recs.Fields("STEP_ENTRY_DT").Value), "ddmmmyyyy")
    > End If
    >
    > If IsNull(in_recs.Fields("UNION_CD").Value) Then
    > UNIONCD = "N"
    > Else
    > UNIONCD = "Y"
    > End If
    >
    >
    > Print #1, Tab(1); _
    > in_recs.Fields("JOB.EMPLID").Value & "|" &
    > in_recs.Fields("EMPL_RCD").Value & "|" & EFF_DT & "|" &
    > in_recs.Fields("EFFSEQ").Value & "|" & "HRMS01" & "|" &
    > in_recs.Fields("EMPL_STATUS").Value & "|" &
    > in_recs.Fields("ACTION").Value & "|" & ACTIONDT & "|" &
    > in_recs.Fields("ACTION_REASON").Value & "|" & _
    > in_recs.Fields("JOB.COMPANY").Value & "|" &
    > in_recs.Fields("PAYGROUP").Value & "|" &
    > in_recs.Fields("ACCT_CD").Value & "|" &
    > in_recs.Fields("FLSA_STATUS").Value & "|" &
    > in_recs.Fields("FULL_PART_TIME").Value & "|" &
    > in_recs.Fields("REG_TEMP").Value & "|" &
    > in_recs.Fields("STD_HOURS").Value & "|" & _
    > in_recs.Fields("JOBCODE").Value & "|" & JOBENTRYDT & "|" &
    > in_recs.Fields("SAL_ADMIN_PLAN").Value & "|" &
    > in_recs.Fields("GRADE").Value & "|" & GRADEENTRYDT & "|" &
    > in_recs.Fields("STEP").Value & "|" & STEPENTRYDT & "|" &
    > in_recs.Fields("TAX_LOCATION_CD").Value & "|" &
    > in_recs.Fields("COMPRATE").Value & "|" & _
    > in_recs.Fields("COMP_FREQUENCY").Value & "|" &
    > in_recs.Fields("CHANGE_PCT").Value & "|" &
    > in_recs.Fields("CHANGE_AMT").Value & "|" &
    > in_recs.Fields("DEPTID").Value & "|" & DEPTENTRYDT & "|" & UNIONCD &
    > "|" & in_recs.Fields("UNION_CD").Value & "|" &
    > in_recs.Fields("LOCATION").Value & "|" & in_recs.Fields("SHIFT").Value
    > & "|" & _
    > in_recs.Fields("EMPL_TYPE").Value & "|" &
    > in_recs.Fields("OFFICER_CD").Value & "|" &
    > in_recs.Fields("CURRENCY_CD").Value & "|" & " " & "|" & " " & "|" & " "
    > & "|" & " " & "|" & _
    > in_recs.Fields("HOURLY_RT").Value & "|" &
    > in_recs.Fields("MONTHLY_RT").Value & "|" &
    > in_recs.Fields("ANNUAL_RT").Value & "|" &
    > in_recs.Fields("GL_PAY_TYPE").Value & "|" &
    > in_recs.Fields("BEN_STATUS").Value & "|" &
    > in_recs.Fields("EMPL_CLASS").Value & "|" & _
    > Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    > Format(Date, "ddmmmyyyy") & "|INITLOAD";
    >
    > in_recs.MoveNext
    >
    > Loop
    >
    > Close #1 ' Close file.
    >
    > '================================================================================================
    > 'OUTPUT TO TEXT FILE
    > '================================================================================================
    > Close #1 ' Close file.
    > in_recs.MoveFirst
    >
    > 'Reset DataSrce Temp Table
    > DoCmd.SetWarnings False
    > DoCmd.OpenQuery "RESET FUTURE TEMP", acViewNormal, acEdit
    >
    > 'create a text file
    > Open "C:\docs\US LOAD\CNVR_PERS_TRNSLTNfuture.txt" For Output As #1 Len
    > = 1500
    >
    > Do While Not in_recs.EOF
    >
    > If in_recs.Fields("US_CAN_IND") = "USA" Then
    > USCAN = "HRMS01"
    > Else
    > USCAN = "HRMS02"
    > End If
    >
    > Print #1, Tab(1); _
    > USCAN & "|" & _
    > in_recs.Fields("C.EMPLID").Value & "|" & _
    > in_recs.Fields("ALTEMPLID").Value & "|" & _
    > in_recs.Fields("C.EMPLID").Value & "|" & _
    > Format(Date, "ddmmmyyyy") & "|INITLOAD|" & _
    > Format(Date, "ddmmmyyyy") & "|INITLOAD";
    >
    >
    > With out_recs
    > .AddNew
    > !EMPLID = in_recs.Fields("C.EMPLID").Value
    > !US_CAN_IND = in_recs.Fields("US_CAN_IND").Value
    > !USCAN = USCAN
    > !COMPANY = in_recs.Fields("C.COMPANY").Value
    >
    >
    > .Update
    > On Error Resume Next
    > End With
    >
    > in_recs.MoveNext
    >
    > Loop
    >
    > Close #1 ' Close
    > End Function
    >
     

Share This Page