Hi all,
I have a query which is a make table one (code is shown below - sorry for it being so big!!)
I want another code now which will export the outputted tables to *.txt, I can do it manually but my issue is say for example invoicenumber max length field in table design view is 10 characters, and the actual invoice number is 6 I want it to export the full 10 characters with the remaining 4 being spaces.
I hope that makes sense and that somebody can help me
thank you ever so much
I have a query which is a make table one (code is shown below - sorry for it being so big!!)
I want another code now which will export the outputted tables to *.txt, I can do it manually but my issue is say for example invoicenumber max length field in table design view is 10 characters, and the actual invoice number is 6 I want it to export the full 10 characters with the remaining 4 being spaces.
I hope that makes sense and that somebody can help me
thank you ever so much
Code:
Private Sub ExportRequests_Click()
On Error GoTo Err_ExportRequests
If vbNo = msgbox("Do you want to create an InvoicesItems Requests File?", vbYesNo + vbExclamation + vbDefaultButton1, "Create Invoice Requests!") Then
Exit Sub
End If
Dim FileName As String
Dim DirectoryPathName As String
Dim MyWorkspace As Workspace
Dim Mydb As DATABASE
Dim InvHead As Recordset, InvLine As Recordset
Dim InvoicesItems As Recordset, Invoices As Recordset, InvUpd As Recordset
Dim lngCount As Long, ByteCount As Long
Dim varReturn As Variant
Dim FinancialYear As String
Dim PayPeriod As String
Dim InvoiceNumber As String
Dim DetailCount As Integer
Dim ValueNetText As String
Dim ValueNetSum As Currency
Dim ValueVATText As String
Dim ValueVATSum As Currency
Dim ValueTotalText As String
Dim ValueTotalSum As Currency
Dim DetailValueText As String
Dim DetailValueSum As Currency
Dim ThisCOST_CENTR As String
Dim ThisSTOCK As String
Dim ThisNON_STOCK As String
Dim YearPeriod As String
Dim SQLquery As String
Dim ValInvNetText As String
Dim ValInvVatText As String
Dim ValInvTotText As String
Dim ValInvNet As Currency
Dim ValInvVat As Currency
Dim ValInvTot As Currency
Dim FinanceCode As String
Dim warehouse As String
Dim ExportNumber As Long
Dim IncExportNumber As Long
Dim strmsg As String
ExportNumber = InputBox("Leave as zero for new creation or Enter Export Number to re-create", "Export Run", 0)
PayPeriod = InputBox("Enter the Pay Period as two characters:", , "04")
If PayPeriod = "" Or Len(PayPeriod) <> 2 Then
msgbox "You must enter 2 Characters!", vbCritical, "Warning"
Exit Sub
End If
FinancialYear = InputBox("Enter the Financial Year as two characters:", , "00")
If FinancialYear = "" Or Len(FinancialYear) <> 2 Then
msgbox "You must enter 2 Characters!", vbCritical, "Warning"
Exit Sub
End If
YearPeriod = FinancialYear & PayPeriod
Set Mydb = CurrentDb
Set MyWorkspace = DBEngine.Workspaces(0)
Set InvHead = Mydb.OpenRecordset("InvHead", dbOpenDynaset)
Set InvLine = Mydb.OpenRecordset("InvLine", dbOpenDynaset)
If Not EmptyThisTable("InvLine") Then
msgbox "Can't empty InvLine table!"
GoTo Exit_ExportRequests
End If
If Not EmptyThisTable("InvHead") Then
msgbox "Can't empty InvHead table!"
GoTo Exit_ExportRequests
End If
'Set InvoicesItems = MyDB.OpenRecordset("SELECT DISTINCTROW InvoicesItems.* FROM Invoices INNER JOIN InvoicesItems ON Invoices.InvoiceID = InvoicesItems.InvoiceID WHERE Invoices.YearPeriod = """ & YearPeriod & """ ORDER BY InvoicesItems.InvoiceID, InvoicesItems.InvoiceItemID;", dbOpenDynaset)
'SqlQuery = "SELECT DISTINCTROW Invoices.InvoiceID as InvoicesInvoiceID, InvoicesItems.InvoiceID, Invoices.CustomerNo, Invoices.DebtorType, Invoices.Directorate, Invoices.TheirOrderNo, Invoices.YearPeriod, Invoices.PayerFAO, Invoices.InvoiceDate, Invoices.PersonName, Invoices.PersonDesignation, Invoices.PersonDept, Invoices.PersonHospital, Invoices.PersonExtension, Invoices.PaymentDueDate, Invoices.CRReason, Sum(InvoicesItems.Net) AS SumOfNet, Sum(InvoicesItems.VAT) AS SumOfVAT, Sum(InvoicesItems.Total) AS SumOfTotal FROM Invoices INNER JOIN InvoicesItems ON Invoices.InvoiceID = InvoicesItems.InvoiceID " _
' & "GROUP BY Invoices.InvoiceID, InvoicesItems.InvoiceID, Invoices.CustomerNo, Invoices.DebtorType, Invoices.Directorate, Invoices.TheirOrderNo, Invoices.YearPeriod, Invoices.PayerFAO, Invoices.InvoiceDate, Invoices.PersonName, Invoices.PersonDesignation, Invoices.PersonDept, Invoices.PersonHospital, Invoices.PersonExtension, Invoices.PaymentDueDate, Invoices.CRReason HAVING (((Invoices.YearPeriod) = """ & YearPeriod & """)) ORDER BY Invoices.InvoiceID, InvoicesItems.InvoiceID;"
'Set Invoices = MyDB.OpenRecordset(SqlQuery, dbOpenDynaset)
Set InvoicesItems = Mydb.OpenRecordset("SELECT DISTINCTROW InvoicesItems.* FROM Invoices INNER JOIN InvoicesItems ON Invoices.InvoiceID = InvoicesItems.InvoiceID WHERE (((Invoices.ExportNumber)=" & ExportNumber & ")) ORDER BY InvoicesItems.InvoiceID, InvoicesItems.InvoiceItemID;", dbOpenDynaset)
SQLquery = "SELECT DISTINCTROW Invoices.InvoiceID as InvoicesInvoiceID, InvoicesItems.InvoiceID, Invoices.CustomerNo, Invoices.DebtorType, Invoices.Directorate, Invoices.TheirOrderNo, Invoices.YearPeriod, Invoices.PayerFAO, Invoices.InvoiceDate, Invoices.PersonName, Invoices.PersonDesignation, Invoices.PersonDept, Invoices.PersonHospital, Invoices.PersonExtension, Invoices.PaymentDueDate, Invoices.CRReason, Invoices.ExportNumber, Sum(InvoicesItems.Net) AS SumOfNet, Sum(InvoicesItems.VAT) AS SumOfVAT, Sum(InvoicesItems.Total) AS SumOfTotal FROM Invoices INNER JOIN InvoicesItems ON Invoices.InvoiceID = InvoicesItems.InvoiceID " _
& "GROUP BY Invoices.InvoiceID, InvoicesItems.InvoiceID, Invoices.CustomerNo, Invoices.DebtorType, Invoices.Directorate, Invoices.TheirOrderNo, Invoices.YearPeriod, Invoices.PayerFAO, Invoices.InvoiceDate, Invoices.PersonName, Invoices.PersonDesignation, Invoices.PersonDept, Invoices.PersonHospital, Invoices.PersonExtension, Invoices.PaymentDueDate, Invoices.CRReason, Invoices.ExportNumber HAVING (((Invoices.ExportNumber)= " & ExportNumber & ")) ORDER BY Invoices.InvoiceID, InvoicesItems.InvoiceID;"
Set Invoices = Mydb.OpenRecordset(SQLquery, dbOpenDynaset)
If InvoicesItems.EOF Or Invoices.EOF Then
msgbox "No Records found in: " & DirectoryPathName & FileName, vbInformation, "Warning"
GoTo Exit_ExportRequests
End If
Invoices.MoveLast
Invoices.MoveFirst
lngCount = Invoices.RecordCount
varReturn = SysCmd(acSysCmdInitMeter, "Creating Invoice Requests ... Please Wait!", lngCount)
lngCount = 0
MyWorkspace.BeginTrans
Do While Not Invoices.EOF
lngCount = lngCount + 1
varReturn = SysCmd(acSysCmdUpdateMeter, lngCount)
ValueNetSum = Nz(Invoices!SumOfNet)
ValueNetText = Space(12 - Len(CStr(Format(ValueNetSum, "0.00")))) & CStr(Format(ValueNetSum, "0.00"))
ValueVATSum = Nz(Invoices!SumOfVAT)
ValueVATText = Space(12 - Len(CStr(Format(ValueVATSum, "0.00")))) & CStr(Format(ValueVATSum, "0.00"))
ValueTotalSum = Nz(Invoices!SumOfTotal)
ValueTotalText = Space(12 - Len(CStr(Format(ValueTotalSum, "0.00")))) & CStr(Format(ValueTotalSum, "0.00"))
InvHead.AddNew
InvHead!CUSTOMER_N = Invoices!CustomerNo
InvHead!INVOICE = Invoices!InvoicesInvoiceID
InvHead!RQN = Invoices!TheirOrderNo
InvHead!PER = PayPeriod
InvHead!YR = FinancialYear
InvHead!ORDER_NUMB = Invoices!TheirOrderNo
InvHead!ANA_A = Invoices!DebtorType
InvHead!ANA_B = Invoices!Directorate
InvHead!Net = ValueNetText
InvHead!VAT = ValueVATText
InvHead!TOT = ValueTotalText
InvHead!InvDate = Format(Invoices!InvoiceDate, "dd/mm/yyyy")
InvHead!DueDate = Format(Invoices!PaymentDueDate, "dd/mm/yyyy")
InvHead.UPDATE
InvHead.Move 0, InvHead.LastModified
' InvLine.AddNew
'InvLine!Invoice = Invoices!InvoicesInvoiceID
'InvLine!LIN = "001"
'InvLine!WH = "di"
'InvLine!DESC = "FAO: " & Invoices!PayerFAO
'InvLine!WH = "di"
'InvLine!CC = " "
'InvLine!NET = " 0.00"
'InvLine!Vat = " 0.00"
'InvLine!TOT = " 0.00"
'InvLine!VAT_CODE = InvoicesItems!VATCode & " "
'InvLine.UPDATE
'InvLine.AddNew
'InvLine!Invoice = Invoices!InvoicesInvoiceID
'InvLine!LIN = "002"
'InvLine!WH = "di"
'InvLine!DESC = DLookup("Value", "TextValues", "Description = ""ThisDepartment""") & " Services Supplied"
'InvLine!CC = " "
'InvLine!NET = " 0.00"
'InvLine!Vat = " 0.00"
'InvLine!TOT = " 0.00"
'InvLine!VAT_CODE = InvoicesItems!VATCode & " "
'InvLine.UPDATE
DetailCount = 0
Do While Invoices!InvoicesInvoiceID = InvoicesItems!InvoiceID
DetailCount = DetailCount + 1
ValInvNet = InvoicesItems!Net
ValInvNetText = Space(12 - Len(CStr(Format(ValInvNet, "0.00")))) & CStr(Format(ValInvNet, "0.00"))
ValInvVat = InvoicesItems!VAT
ValInvVatText = Space(12 - Len(CStr(Format(ValInvVat, "0.00")))) & CStr(Format(ValInvVat, "0.00"))
ValInvTot = InvoicesItems!Total
ValInvTotText = Space(12 - Len(CStr(Format(ValInvTot, "0.00")))) & CStr(Format(ValInvTot, "0.00"))
If InvoicesItems!FinanceCode = "Free Text" Then
warehouse = "di"
FinanceCode = " "
Else
FinanceCode = InvoicesItems!FinanceCode
warehouse = " "
End If
InvLine.AddNew
InvLine!INVOICE = InvHead!INVOICE
InvLine!LIN = zeros(3 - Len(CStr(DetailCount))) & CStr(DetailCount)
InvLine!WH = warehouse
InvLine!SERVICE = FinanceCode
InvLine!DESC = InvoicesItems!Description
InvLine!CC = FinanceCode
InvLine!Net = ValInvNetText
InvLine!VAT = ValInvVatText
InvLine!TOT = ValInvTotText
InvLine!VAT_CODE = IIf(InvoicesItems!VATCode = "Y", "I ", "Z ")
'InvLine!VAT_CODE = InvoicesItems!VATCode & " "
InvLine.UPDATE
InvoicesItems.MoveNext
If InvoicesItems.EOF Then
Exit Do
End If
Loop
Invoices.MoveNext
Loop
If ExportNumber = 0 Then
Set Mydb = CurrentDb
Set InvUpd = Mydb.OpenRecordset("select max(exportnumber) as runnumber from invoices")
IncExportNumber = InvUpd!runnumber + 1
Mydb.Execute "UPDATE DISTINCTROW Invoices SET Invoices.ExportNumber = " & IncExportNumber & " " _
& "WHERE (((Invoices.ExportNumber)=0))", _
dbSQLPassThrough
strmsg = msgbox("Export Number " & Str(IncExportNumber) & " has been assigned to this batch ")
End If
MyWorkspace.CommitTrans
DoCmd.OpenForm "Invoices Imports for Correction"
Forms![Invoices Imports for Correction].Requery
Exit_ExportRequests:
On Error Resume Next
varReturn = SysCmd(acSysCmdRemoveMeter)
InvHead.Close
InvLine.Close
InvoicesItems.Close
Invoices.Close
Mydb.Close
varReturn = SysCmd(acSysCmdRemoveMeter)
Exit Sub
Err_ExportRequests:
Select Case Err
Case Else: msgbox "ERROR " & Err & ": " & Error
End Select
Resume 'ErrorExit
ErrorExit:
On Error Resume Next
MyWorkspace.Rollback
msgbox "All changes have been aborted!", vbInformation, "Warning"
GoTo Exit_ExportRequests
End Sub