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

B

bradjensmith

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
 
6

'69 Camaro

Hi.
It
errors with an Out of Memory error
Click to expand...

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.


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field Size Dilemma 12
Adding True/False condition to my code 2
Multiple Report Filter using form 1
Date Time Picker : Error 35788 1
Can somebody help me with this code? 5
How to use a combo box variable in a VBA module 1
QBF on Steroids with List Control --almost there, but problem 3
Viewing Names 3

Top