Link to home
Start Free TrialLog in
Avatar of Derek Brown
Derek BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Text from Access Data

Hi All

How do I get Access to create a text file built up from data from a query?

I have to get a value from a barcode reader that reads a JobNumber open the query with that JobNumber or perhaps a find record? And get the following values as below

The query should find only one record  that shows JobNumber, Length, Width and Thickness in separate columns. (It has more columns than that but it is the procedure I need) I am giving instructions to a computer controlled machine in something called G Code, but I have to create the G Code it should look something like this:

With this code the machine would cut a square, length x width  x thickness

G00 X0 Y0 Z (Thickness +20)     ‘Go to start point’
G01 X (Length) Y0 Z0                  ‘Bring the cutter down and cut along the length
G01 X0 Y-(Width) Z0                   ‘Cut along the width. Etc.
G01 X-(Length) Y0 Z0
G01 X0 Y(Width)  Z0
G01 X(Length)+50 Z0
G01 Z(Thickness)+20
G00 X0 Y0 Z0

I know I have to write everything that is not a field in my query but how do I get the values from the query?

How do I get code to do this, create a text file named JobNumber and put that file into a folder on my C Drive. Do I need to generate it in a memo field and export it or is there another way?

Thinking about it it’s a bit like a mail merge letter really I guess.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

The way I would do this is to create a recordset based on your query, returning the appropriate JobNumber, length, width and thickness values.

I would then create the text you have above through a single statement, something like:

Dim strInstructions as string

strInstructions = "G00 X0 Y0 Z (" & rs!Thickness & " +20)" & vbcrlf _
                           & "G01 X (" & rs!Length & ") Y0 Z0" & vbcrlf _
                           & "G01 X0 Y-(" & rs!Width & ") Z0" & vbcrlf _
                           & "G01 X-(" & rs!Length & ") Y0 Z0" & vbcrlf _
                           & "G01 X0 Y(" & rs!Width & ")  Z0" & vbcrlf _
                           & "G01 X(" & rs!Length & ")+50 Z0" & vbcrlf _
                           & "G01 Z(" & rs!Thickness & ")+20" " & vbcrlf _
                           & "G00 X0 Y0 Z0"

To open a text file, you would use code similar to:

Dim strFilename as string
strFilename = "C:\Somepath\" & rs!JobNumber & ".txt"

Open strFilename For Output As #1
Write #1, strInstructions
Close #1
If I am understanding your question...
You need to write out the value of a field for every record, ..to a text file?
Correct?
Then you can use code like this:

Dim rst As Recordset
Dim strData As String
Set rst = CurrentDb.OpenRecordset("SELECT YourField FROM YourTable")
    rst.MoveFirst
    Do Until rst.EOF
        strData = strData & vbCrLf & rst!YourField
        rst.MoveNext
    Loop
    Open "c:\yourFolder\YourFile.txt" For Output As #1
    Write #1, strData
    Close #1


Now this is very basic code, ...and it will create leave leading and trailing double quotes at the top and bottom of your textfile.
But this should get you started,...we can worry about the quotes when you get it working...

;-)

JeffCoachman
Actually, I think if you use Print #1, it will leave out the quotes, but it has been a while since I have done either.
Avatar of Derek Brown

ASKER

Thanks Dale, Jeff

I am trying to avoid creating a table to store these records because at any point up until manufacture (Scanning of barcode) the length width or thickness could be changed by a salesman. So I need to wait until the barcode is scanned before the code is created. then send the code to a text file that the machine then reads into the controller. I guess it would be possible to have the query filtered by the barcode (RecordNumber) input and find the record delete the contents of the current table or delete the table and create a one record table if that is how it needs to be done.

That gives another question. How would the scanning of a barcode generate a procedure in access, hopefully in the background.

Jeff if it would be great to create all text files that way but as I have said above any record could be changed right up until the point of manufacture.
A query selects data from a table.  If the data isn't in a table, where is it?  

If the barcode contains the information you need (length, width, thickness) and your code will generate the desired output, then you don't need a table or a query unless you want to store the generated instructions.  Dale's initial suggestion should be what you need.
Hi Pat

I thought that Dale's idea was to create a table "create a recordset based on your query" but I guess a recordset is not a table? correct? if I can access that recordset then that will solve the problem. As I said there are a lot more fields, probably a hundred plus. I do not know if a bar code (or that new barcode thingy, probably called a blobcode) can handle that quantity of data. In any case the data from the computer would be editable and correct right until the barcode scan which is great.

I am just setting up a trial of Dale's suggestion. Will have more questions later.
Jef

Not quite I need to have the barcode scan to get a value which becomes the filter for the query and then uses the data from that single record to generate the G Code
I'm a little lost here...

Who's suggestions are you trying?

It is still not clear exactly what you have, and the exact output you need...
Sorry I mixed up Dale with Jeff.

Dales suggestion is the one I will try. I have spent the weekend creating the correct data to use and I will create a form that has a control in which to enter the barcode readers results, this will also filter the records and select the correct single record from the underlying tables. from there I will use Dales code and generate a .txt file
With Dales code in my code I get all text highlighted in Red and error notice "Expected line number or lable or end of statement.

Help
There was a space missing after the "vbcrlf_" Should be "vbcrlf  _"
Only took an hour to figure that one out.

This approach seems to work very well. I just need to know how to eradicate the quotation marks in the .txt file and I'm done.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That's got it. Fabulous Dale!

Derek
Hi Dale

there is a bit of a problem How do I remove the brackets as below. If I take them out I get the field name DH instead of it's value.

.txt file

G00 X-50 Y-100 Z (54 + 20)
G01 G41 Y-50 X0 Z-1 F3000
Y0 X0
X & DH Y0 Z-4 F6000
X0 Y-(914) Z-1
X-(2135) Y0 Z-4
X0 Y20(914)  Z-1
Z(54)+20
X0 Y0 Z0
Should have added the code

Dim Code As String
Dim rs As Recordset
Code = "G00 X-50 Y-100 Z (" & Thickness & " + 20)" & vbCrLf _
        & "G01 G41 Y-50 X0 Z-1 F3000" & vbCrLf _
        & "Y0 X0" & vbCrLf _
        & "X (" & DH & ")" & " Y0 Z-4 F6000" & vbCrLf _
        & "X0 Y-(" & DW & ") Z-1" & vbCrLf _
        & "X-(" & DH & ") Y0 Z-4" & vbCrLf _
        & "X0 Y20(" & DW & ")  Z-1" & vbCrLf _
        & "Z(" & Thickness & ")+20" & vbCrLf _
        & "X0 Y0 Z0"
It looks like it should work as written, notice the 2nd line below it.  However, try:

& "X (" & DH & ") Y0 Z-4 F6000" & vbCrLf _

I also notice that you have two different formats using the Thickness variable on the 1st and 2nd to last lines.

G00 X-50 Y-100 Z (54 + 20)
Z(54)+20

Do you really want to do that, or would you prefer to have those values as:
G00 X-50 Y-100 Z (74)
Z(74)
That still has the brackets around the DH Value. The result is

X (2135) Y0 Z-4 F6000

And G00 X-50 Y-100 Z (74) is what I want thank you. But I still need to remove the brackets if that is going to be possible.

Cheers Dale
so you want to remove the parenthesis?  Do you want to remove all of the parenthesis?  What do you want the results to actually look like?
Hi Dale

Think I have it now to get this:

G00 X-50 Y-100 Z64
G01 G41 Y-50 X0 Z-1 F3000
Y0 X0
X2000 Y0 Z-4 F6000
X0 Y-1000 Z-1
X0 Z-4
Y20 Z-1
Z64
Park

 I doTHis:

Code = "G00 X-50 Y-100 Z" & (Thickness + 20) & vbCrLf _
        & "G01 G41 Y-50 X0 Z-1 F3000" & vbCrLf _
        & "Y0 X0" & vbCrLf _
        & "X" & DH & " Y0 Z-4 F6000" & vbCrLf _
        & "X0 Y-" & DW & " Z-1" & vbCrLf _
        & "X0 Z-4" & vbCrLf _
        & "Y20 Z-1" & vbCrLf _
        & "Z" & Thickness + 20 & vbCrLf _
        & "Park"

Thank you so much for your help.
Just in case If I need a message that the .txt file already exists and an option to cancel can this be done?
Yes, to test if the file already exists, use the DIR() function.

The syntax would be something like:

'This will test to see whether the file already exists.  If it does, the Dir() function will return just the file name
'If it does not exist, then the Dir() function will return an empty string.
if Dir("C:\yourPath\yourFieldname.txt") <> "" Then
    if msgbox("File already exists.  Delete existing file?", vbOKCancel) = vbCancel Then Exit Sub
    Kill "C:\yourPath\yourFilename.txt"
End If
Great thanks again Dale