Need help ????Overwriting the lines in text file

Hi There,

Below source code is creating the text file without problem. But it is overwriting the Data from above lines. Because the first Detail tables are having more field than the trailer table. Let me know how to avoid this???

Here is the code :

Function ExportFileToLoannet(sFileName As String)
'--
'Form Level Function
'Write data into a fixed length ASCII file
'--

Dim sSQL As String
Dim adoRS As ADODB.Recordset
Dim iCount As Long
Dim cmdCommand As ADODB.Command
Dim sOutputFile As String
Static bIsFileCreated As Boolean
Dim ifHandler As Integer
''Dim sFile As String * 250
Dim sCreateFileName As String
Dim Header As Variant
Dim X As String
On Error GoTo errExport
Me.MousePointer = vbHourglass


'--get a handler
ifHandler = FreeFile

'--get a filename
''sFileName = sGetFileNameLexis

sCreatedFileName = sFileName

'--create the file first
Open sFileName For Output As ifHandler
bIsFileCreated = True
'--Header Format


sSQL2 = "SELECT * FROM tbl_detail_96537"

Set adoRS2 = ExecuteSQL(sSQL2, sText, iSybase)

If adoRS2.BOF Or adoRS2.EOF Then
MsgBox "No Detail records found"
Me.MousePointer = vbNormal
Exit Function
End If

With adoRS2
Do While Not .EOF

Mid(sFile, 1, 1) = Trim$(.Fields![det965_record_type])
'--VENDOR NAME
Mid(sFile, 2, 5) = Trim$(.Fields![det965_client_id])
'--INVOICE_NUMBER
Mid(sFile, 6, 14) = Trim$(.Fields![det965_cusip])
'--INVOICE_DATE
Mid(sFile, 15, 18) = Trim$(.Fields![det965_routing_table_id])
'--INVOICE_AMOUNT
Mid(sFile, 19, 27) = Trim$(.Fields![det965_quantity])
'--APPROVED
Mid(sFile, 28, 28) = Trim$(.Fields![det965_collateral_code])
'--CHECK_DATE
Mid(sFile, 29, 48) = Trim$(.Fields![det965_comments])

Mid(sFile, 49, 53) = Format(.Fields![det965_min_rebate_rate], "#####")
'--VENDOR NAMEFormat(CStr(-CDbl(Mid(Data, 2, Len(Data) - 2))), "###,###,###,###,##0.00")
Mid(sFile, 54, 54) = Trim$(.Fields![det965_min_rebate_rate_code])
'--INVOICE_NUMBER
Mid(sFile, 55, 63) = Trim$(.Fields![det965_min_partial_quantity])
'--INVOICE_DATE
Mid(sFile, 64, 71) = Format(.Fields![det965_max_price], "########")
'--INVOICE_AMOUNT
Mid(sFile, 72, 75) = Trim$(.Fields![det965_time_out])
'--APPROVED
Mid(sFile, 76, 76) = Trim$(.Fields![det965_loanet_indicator_code])
'--CHECK_DATE
Mid(sFile, 77, 77) = Trim$(.Fields![det965_batch_code])

Mid(sFile, 78, 78) = Trim$(.Fields![det965_profit_center])
'--VENDOR NAME
Mid(sFile, 79, 79) = Trim$(.Fields![det965_mark_parameter_type])
'--INVOICE_NUMBER
Mid(sFile, 80, 81) = Trim$(.Fields![det965_mark_parameter_amount])
'--INVOICE_DATE
Mid(sFile, 82, 82) = Trim$(.Fields![det965_tracking_indicator])
'--INVOICE_AMOUNT
Mid(sFile, 83, 88) = Format(.Fields![det965_dividend_flow_thru], "######")
'--APPROVED
Mid(sFile, 89, 100) = Trim$(.Fields![det965_blank])
Print #ifHandler, sFile
.MoveNext

Loop
End With

sSQL3 = "SELECT * FROM tbl_trailer_96537"

Set adoRS3 = ExecuteSQL(sSQL3, sText, iSybase)

If adoRS3.BOF Or adoRS3.EOF Then
MsgBox "No Trailer record found"
Me.MousePointer = vbNormal
Exit Function
End If
With adoRS3
Do While Not .EOF
'--CHECK_DATE
Mid(sFile, 1, 1) = Trim$(.Fields![tlr965_record_type])
Mid(sFile, 2, 6) = Trim$(.Fields![tlr965_count])
Mid(sFile, 7, 22) = Trim$(.Fields![tlr965_total_qty])
Mid(sFile, 23, 100) = Trim$(.Fields![hdr965_blank_2])
Print #ifHandler, sFile
.MoveNext
'''
'''''''' Mid(sFile, 125, 10) = Format(.Fields![PAY_AMOUNT], "######.00")

Loop
End With


Close 1
MsgBox _
Prompt:="File Created Successfully", _
Buttons:=vbOKOnly, _
Title:="Export"

Me.MousePointer = vbNormal
ShellAndLoop sBatchFileName, vbNormalFocus

Exit Function
errExport:
If bStopProcess Then
Close
Else
MsgBox Err.description, vbCritical
bStopProcess = True
Me.MousePointer = vbNormal

End If

End Function


Thanks
Ekta

U can define the sfile variable to string(just not string*250)..Because the sfile(string * 250) is too small to the second sql query.