Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem writing mutliple copies of recordset to text file 1

Status
Not open for further replies.

Tadynn

Technical User
Oct 8, 2001
72
AU
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





 
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 ..

Code:
[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.
 
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


 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top