Problem writing mutliple copies of recordset to text file 1

[Tadynn]

Hi all,

Using Access 2K

For reference, all fields listed below are text boxes on my form that are used in the code:

LabelName
NoOfLabels - No of times to copy recordset to textfile (loop)
ProperShippingName
UN_No
PrinterName
TextFileName

I am currently have the following code attached to a button that when pressed creates a text file. The code works fine but I have trouble with inserting multiple copies of the recordset to the textfile.

Dim i As Integer
Dim hFileHandle As Long
Dim Path As String

Path = Me.TextFileName.Value
For i = 1 To NoOfLabels
hFileHandle = FreeFile

Open Path For Output As #hFileHandle

Write #hFileHandle, "*FORMAT, " & LabelName & vbCrLf
Write #hFileHandle, "ProperShippingName, " & Me.ProperShippingName.Value & vbCrLf
Write #hFileHandle, "UN_No, " & UN_No & vbCrLf
Write #hFileHandle, "*PRINTERNAME, " & PrinterName & vbCrLf
Write #hFileHandle, "*PRINTLABEL"
Close #hFileHandle

Next i

End Sub


My problem obviously is that if I specify that I need two copies of the label. What should happen is that the record should be copied to the text file twice like so:

*FORMAT, miniQSDSPLBL.lwl
ProperShippingName, Corrosive Liquid N.O.S.(Contains Sodium Hydroxide)
UN_No, 1760
*PRINTERNAME, station4
*PRINTLABEL
*FORMAT, miniQSDSPLBL.lwl
ProperShippingName, Corrosive Liquid N.O.S.(Contains Sodium Hydroxide)
UN_No, 1760
*PRINTERNAME, station4
*PRINTLABEL

Because of the incorrect way that I wrote the code, the first instance is overwritten when the second instance is inserted. So there is always only one record in the text file when I open it.

Does anyone have any better ideas on how I could rewrite my code to achieve this?

Rgrds, Tadynn

 

[TonyJollans]

Hi Tadynn,

Why not just put the Open and Close outside the loop? Depending on your data, you may need to check if there is nothing to write, but otherwise ..

[blue]Path = Me.TextFileName.Value

hFileHandle = FreeFile
Open Path For Output As #hFileHandle

For i = 1 To NoOfLabels

    Write #hFileHandle, "*FORMAT, " & LabelName & vbCrLf
    Write #hFileHandle, "ProperShippingName, " & Me.ProperShippingName.Value & vbCrLf
    Write #hFileHandle, "UN_No, " & UN_No & vbCrLf
    Write #hFileHandle, "*PRINTERNAME, " & PrinterName & vbCrLf
    Write #hFileHandle, "*PRINTLABEL"

Next i

Close #hFileHandle[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Tadynn]

Hi Tony,

I've just given it a quick go and it the looping worked well so thanks for that (I can't believe it was that simple).

Quick question to go with this a bit further........

My output file is coming out like this (I've taken out the carriage returns to get rid of the spaces).

"*FORMAT, "
"ProperShippingName, "
"UN_No, "
"*PRINTERNAME, "
"*PRINTLABEL"
"*FORMAT, "
"ProperShippingName, "
"UN_No, "
"*PRINTERNAME, "
"*PRINTLABEL"

but I need it to output minus the quotations. Is there any way for me to remove this?

*FORMAT, miniQSDSPLBL.lwl
ProperShippingName, Corrosive Liquid N.O.S.(Contains Sodium Hydroxide)
UN_No, 1760
*PRINTERNAME, station4
*PRINTLABEL
*FORMAT, miniQSDSPLBL.lwl
ProperShippingName, Corrosive Liquid N.O.S.(Contains Sodium Hydroxide)
UN_No, 1760
*PRINTERNAME, station4
*PRINTLABEL

Thanks again

Tadynn

 

[RoyVidar]

I like using the filesystemobject. Set a reference to the Microsoft Scripting Runtime library (in VBE - Tools | References), and perhaps try something like this:

[tt]dim fs as scripting.filesystemobject
dim txt as scripting.textstream

set fs=new scripting.filesystemobject
set txt=fs.createtextfile(Me!TextFileName.Value, True)

For i = 1 To NoOfLabels

txt.writeline "*FORMAT, " & LabelName
txt.writeline "ProperShippingName, " & Me.ProperShippingName.Value
txt.writeline "UN_No, " & UN_No
txt.writeline "*PRINTERNAME, " & PrinterName
txt.writeline "*PRINTLABEL"

Next i
txt.close
set txt=nothing
set fs=nothing[/tt]

This shold produse the text without quotes.

Roy-Vidar

 

[Tadynn]

Thanks RoyVidar,

Worked well.