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.
Derek BrownMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
If I am understanding your question...
You need to write out the value of a field for every record, ..to a text file?
Then you can use code like this:

Dim rst As Recordset
Dim strData As String
Set rst = CurrentDb.OpenRecordset("SELECT YourField FROM YourTable")
    Do Until rst.EOF
        strData = strData & vbCrLf & rst!YourField
    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...


Dale FyeOwner, Developing Solutions LLCCommented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Derek BrownMDAuthor Commented:
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.
Derek BrownMDAuthor Commented:
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.
Derek BrownMDAuthor Commented:

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
Jeffrey CoachmanMIS LiasonCommented:
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...
Derek BrownMDAuthor Commented:
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
Derek BrownMDAuthor Commented:
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.

Derek BrownMDAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
What does the actual text in the .txt file look like?

Did you try replacing "Write #1" with "Print #1"?  It has been a while, but I thought that one of them adds the quotes, the other doesn't, could be wrong though.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
That's got it. Fabulous Dale!

Derek BrownMDAuthor Commented:
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
X0 Y0 Z0
Derek BrownMDAuthor Commented:
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"
Dale FyeOwner, Developing Solutions LLCCommented:
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)

Do you really want to do that, or would you prefer to have those values as:
G00 X-50 Y-100 Z (74)
Derek BrownMDAuthor Commented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
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?
Derek BrownMDAuthor Commented:
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

 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?
Dale FyeOwner, Developing Solutions LLCCommented:
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
Derek BrownMDAuthor Commented:
Great thanks again Dale
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.