Question Help needed.

[LennyNero]

Hi all,

I've been putting some questions, and developing as i get some answers and find solutions, although i dont know if im making a big turn around to get where i want when someone with expert knowledge would get there in no time, so im asking for sugestions.

I have an old database (access2), that has now to comply with certain entities that require that i send data electronicaly.

The format they want is txt, all lines must have 125 chars.

Every month i need to filter the data from the previous month and create a new invoice with all records from my invoices.
I must then have a header like:

TIPOLINHANUMBER2FILERNUMBER1CODENTIDANUMBER9CODTIPENTNUMBER8NUMFACTNUMBER8ANOMESFACTNUMBER6DATAENVIODATE8CODREJEICAONUMBER2FILERNUMBER81

All of this has default values except for NUMFAC (number of the main invoice that can start at 1) ANOMESFACT (year and month of all my invoices), and DATAENVIO (the date ill send the txt).
So I created a new table with all these fields.

Then the details:

TIPOLINHANUMBER2NUMUNIBENNUMBER9SIGLAVARCHAR2CODCSAUDENUMBER12DTACSAUDEDATE8QTDNUMBER4FILERNUMBER16VALPAGAR_EUROSNUMBER16NUMDOCNUMBER4CODREJEICAONUMBER2AREALIVREVARCHAR50

On this part it comes the data i linked from access 2.0, i created a quary that makes a new table with the data i want (filtered by date).
Then i made an update query with the values i want to update the details table.

Then the footer

TIPOLINHANUMBER2QTDTOTBENNUMBER7FILERNUMBER16VALTOTAL_EUROSNUMBER16CODREJEICAONUMBER2FILERNUMBER82

The footer line also has a lot of default values, the 2 i need most is the QTDTOTBEN number of record lines and total value of all records VALTOTAL_EUROS, this i made using a subform to calculate the values then pass them to the main form.

The purpose of it all is then to create that txt with al this data.
I can elaborate more on what i did, but i reaaly need some advice.

Thank you,
Luis

 

[LennyNero]

For example, when i create a new Invoice, i need it to get the last one issued then increment one, then go to a temp table and fill the header number for that recordset

 

[LennyNero]

this is the code i used to export just the header:
Public Function CreateTextFile()
Dim strTIPOLINHAH As String * 2
Dim strFILERH1 As String * 1
Dim strCODENTIDA As String * 9
Dim strCODTIPENT As String * 8
Dim strNUMFACT As String * 8
Dim strANOMESFACT As String * 6
Dim strDATAENVIO As String * 8
Dim strCODREJEICAO As String * 2
Dim strFILERH2 As String * 81
Dim mydb As DAO.Database, myset As DAO.Recordset
Dim intFile As Integer
Set mydb = CurrentDb()
Set myset = mydb.OpenRecordset("header", dbOpenTable)
intFile = FreeFile
Open "C:\Users\luis.palha\TESTE7.txt" For Output As intFile
myset.MoveFirst
Do Until myset.EOF
LSet strTIPOLINHAH = myset![TIPOLINHAH]
RSet strFILERH1 = Format(myset![FILERH1], "0")
RSet strCODENTIDA = Format(myset![CODENTIDA], "000000000")
RSet strCODTIPENT = Format(myset![CODTIPENT], "00000000")
RSet strNUMFACT = Format(myset![NUMFACT], "00000000")
RSet strANOMESFACT = Format(myset![ANOMESFACT], "yyyymm")
RSet strDATAENVIO = Format(myset![DATAENVIO], "yyyymmdd")
RSet strCODREJEICAO = Format(myset![CODREJEICAO], "00")
RSet strFILERH2 = myset![FILERH2]
Print #intFile, strTIPOLINHAH & strFILERH1 & strCODENTIDA & strCODTIPENT & strNUMFACT & strANOMESFACT & strDATAENVIO & strCODREJEICAO & strFILERH2
myset.MoveNext
Loop
Close intFile
myset.Close
mydb.Close
MsgBox "O ficheiro foi criado!"
End Function


How do i get the details and footer here?