I'm building an automated report and need to be able to update a table with update queries before exporting the text files through my module. I'm still a bit of a noob when it comes to VBA and I've listed my code below at the bottom of this post.
The queries work as planned when ran on test tables seperately but I'm not sure how or where to call them in the VBA module. I tried setting the queries up as public functions and calling them in the export public function but the exported text file still has the wrong format. However, the DET Table is updated by the queries.
How do I get the updated DET table to export after the queries run?
Also, how do I get rid of all the warning pop-ups, i.e. "You're about to change 5497 records. Do you want..."
The queries work as planned when ran on test tables seperately but I'm not sure how or where to call them in the VBA module. I tried setting the queries up as public functions and calling them in the export public function but the exported text file still has the wrong format. However, the DET Table is updated by the queries.
How do I get the updated DET table to export after the queries run?
Also, how do I get rid of all the warning pop-ups, i.e. "You're about to change 5497 records. Do you want..."
Code:
'This query updates the DET table and replaces "000000000000" with "0.00"
Public Function qryReplaceAllZeros()
DoCmd.OpenQuery "qryReplaceAllZeros", acViewNormal, acEdit
End Function
'This query updates the DET table and replaces all leading zeros
Public Function qryRemoveLeadingZeros()
DoCmd.OpenQuery "qryRemoveLeadingZeros", acViewNormal, acEdit
End Function
Public Function CreateTextFile()
'This function creates a fixed-width text file using the Orders table
'in CDA Fixed File Import tool. The CHRGAMT field will be Right-aligned
Dim strRECTYPE As String * 4 'specifies width of 4 characters
Dim strPROVNUM As String * 20 'specifies width of 20 characters
Dim strPCN As String * 50 'specifies width of 50 characters
Dim strCHRGCODE As String * 12 'specifies width of 12 characters
Dim strFILLER1 As String * 18 'specifies width of 18 characters
Dim strCHRQTY As String * 7 'specifies width of 7 characters
Dim strCHRGAMT As String * 15 'specifies width of 15 characters
Dim strSRVCDATE As String * 8 'specifies width of 8 characters
Dim strPROC As String * 8 'specifies width of 8 characters
Dim strORDERMD As String * 22 'specifies width of 22 characters
Dim strORDERMDTYPE As String * 2 'specifies width of 2 characters
Dim strFILLER2 As String * 34 'specifies width of 34 characters
Dim var1 As Variant
Dim result As String
Dim mydb As DAO.Database, myset As DAO.Recordset
Dim intFile As Integer
var1 = ""
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("DET_STAR", dbOpenTable)
intFile = FreeFile
Open "C:\Daily_Files\DET.txt" For Output As intFile
'This section puts the records from the DET table in the text
'file.
myset.MoveFirst
Do Until myset.EOF
LSet strRECTYPE = myset![RECTYPE] 'Field name in brackets
LSet strPROVNUM = myset![PROVNUM]
LSet strPCN = myset![PCN]
LSet strCHRGCODE = myset![CHRGCODE]
RSet strCHRQTY = myset![CHRQTY]
RSet strCHRGAMT = myset![CHRGAMT]
LSet strSRVCDATE = myset![SRVCDATE]
LSet strPROC = Nz(myset![PROC], [var1])
LSet strPROVNUM = Nz(myset![PROVNUM], [var1])
LSet strORDERMD = Nz(myset![ORDERMD], [var1])
LSet strORDERMDTYPE = Nz(myset![ORDERMDTYPE], [var1])
LSet strFILLER2 = Nz(myset![FILLER2], [var1])
'Concatenate all of the variables together as in the following:
Print #intFile, strRECTYPE & strPROVNUM & strPCN & strCHRGCODE & strFILLER1 & strCHRQTY & strCHRGAMT & strSRVCDATE & strPROC & strORDERMD & strORDERMDTYPE & strFILLER2
myset.MoveNext
Loop
Close intFile
myset.Close
mydb.Close
MsgBox "Text file has been created!"
End Function
Comment