Group procedures results in text file

I think your best bet will be to create a table with the VPCode # as one field, and the associated text value in another field.

Then, rather then having to recreate the text associated with each you would simply lookup the text associate with each.

You might even want to put columns in this table for the CH# and the CD#, which would allow you to simply lookup the text associated with each CH# and CD#, but this might require revision of CH# and CD# logic.  Any time you have to use variables with #'s in the variable name, it is likely that you have not normalized your data sufficiently.

Fascinating. I will give that some thought.

+1 on Dale's comment.

Should be a simple lookup and your VB code should be nothing more than a single procedure writing the file the same way for every case.

Jim.

The thing to remember here is that when a user swipes a barcode generating the query filter, the single record created has fields that are variables, Height with thickness and every record ever selected could be different.  it then automatically creates the G Code for the job using those variables and creates the .txt file.

So to put the G Code in fields in a table would mean that the G Code has to be created on the fly placed in fields and in the next instant collated with the other relevant fields to create the entire cnc program (G Code for the whole job) in a .txt file.

Is this the way you suggest I do it?

No, that is not the way I envisioned it.  This is a separate question than the one we discussed the other day.  Your description of the problem in your original statement does not reflect a "G Code" or the various thickness, width, length.  It references the values of several different variables and a fixed string relating to those values.

You don't state what CH1, CH2, CD1, and CD2 represent in your original question, but you do state that if those values meet certain criteria then the VP#Code is a fixed string.  Rather than 4 variables CH1, CH2, CD1, CD2, and then 2 VP#Code values, I would have a variable to define whatever the # represents, and set the values in variables CH and CD, and VPCode.  But now that I look at your code more closely, the definitions of the two VP1Code and VP2Codes are identical, and include an underscore at the end of the last line, which will cause an error in your code.

Let's back up a step.  Can you explain what you are trying to do, not with code, but with an explanation?

My company creates software for estimating selling prices and creation of bills of materials for manufacture of fire doors, mainly for office Doors. Some of our customers would like to send CNC Machine code (G Code) directly to the machine that cuts out the holes in a door for vision panels (For the fitting of a glass panel in the door) and trimming the door to final size.

So we already have the individual door sizes and vision panel sizes in a table. Each record in the table describes one door so field names are Height, Width thickness and for vision panels (up to 6 in each door) CH1 (Cut out Height 1) CW1 (Cut out Width 1) and similar for cut out 2,3,4,5,6. (CD1 is Cut out Diameter for circular vision panels) I will have Access produce a barcode (each door reference number) for each door which will be applied to the door blank ready for machining to size and cutting of vision panels. The user will scan the barcode, Access will then use the barcode data (door number) in a query filter field on the form and find the record associated with the barcode. So my query and form are now showing only one record. Now I have the record I have to create the G Code (X,Y,Z coordinates) for instructing the machine to cut Vision Panels and to trim the door to final size. The text file generated then goes to a separate location for picking up by the machines controller.

So each .txt file is generated on the fly. Which means it is always using the latest data (it can be changed by a salesman at any time up until manufacture), So storing this data is not necessarily a good idea unless the data can be updated. if we can update then we may as well wait until the last minute and create it then.

So, the # in CD1 or CH1 or CW1 simply relates to a particular instance of a vision panel in a door, and there may be many (how many) of these in a particular door?

I would expect that you would be storing these values in a table which relates to each specific customer, door order.  And that your sales persons would enter/edit those values right up to the point that the door is cut.  I would probably store this in a table with fields like:

OrderID - relates to a particular order
DoorID   - specifies which door is being cut
VP_Num - defines the cut number for this particular door
VP Type  - Circle or Rectangle
CenterX  - for circles, the X coordinate of the center
CenterY  - for circles, the Y coordinate of the center
Diameter - for circles, the diameter of the cut
TopLeftX
TopLeftY
BottomRightX
BottomRightY
Thickness

With this structure, you would not have to have code for CD1, CD2, CD3..., you would simply have zero, one, or many values for VP_Num, and with the values provide, you would be able to define the cutting instructions (similar to your previous question) with these values.

This is pretty well what I am doing. The question is more about the coding, which is complex. The vision panels are relatively straight forward but when it comes to cutting recesses for locks hinges and other bits of ironmongery it is anything but simple. So what I would like to do is
Code =
VP1 'Vision Panel 1
VP2
VP3
Lock
Hinge
Bolts
Dim strFilename As String
strFilename = "C:\RapidSpecManufacturing\RapidSpecGCode\" & BarCode & ".txt"

Open strFilename For Output As #1
Print #1, Code
Close #1

Can this de done?

Another reason for doing this is that as different tools or cutters are used for each opporation I may want to change the order in which these items are machined. If I have the cutter for vision panels still in the machine from the last door then I would like to start wih machining the vision panels in the next door. Otherwise I have to change tool which takes time.

If you can provide the cutter with a single txt file, then there is no reason that you could not define, at run time, the order of the various cuts.  I assume that you have a technician at the cutter who controls the cutter, changes the cutter heads, or is that all done via the text file?

Generally, what I would do is provide the operator with a list of the cuts that need to be made, and provide them with a way to sort those.  Then, when they press the "Go" button, it would loop through the list and build the text string for all of the cuts, or the cuts that have been tagged, if changing the cutter head is done manually.

I need the operator to do nothing except load the product onto the machine. I can create the logic to do this the problem that I am having is that when in other situations i call a routine the code runs through as if the code is in one proceedure. So jumping from proceedure to procedure makes no difference than if all of the code was in one procedure. But when I do this Print command to create the .txt file it will not collate the generated text into one document. What I am trying to acheive is:

Dim Code AS String

Code = VP1, VP2. Trim, Lock

strFilename = "C:\RapidSpecManufacturing\RapidSpecGCode\" & BarCode & ".txt"

Open strFilename For Output As #1
Print #1, Code
Close #1
End Sub

Private Sub (VP1)
& "X "& CH1 & vbCrLf _
& "Y "CW1 & vbCrLf _
& "X- "& CH1 & vbCrLf _
& "Y- "CW1 & vbCrLf      
End Sub

Private Sub (VP2)
& "X "& CH2 & vbCrLf _
& "Y "CW2 & vbCrLf _
& "X-"& CH2 & vbCrLf _
& "Y- "CW2 & vbCrLf _
end sub

Private Sub (Lock)
& "X "& LH1 & vbCrLf _
& "Y "LW1 & vbCrLf _
& "X- "& LH1 & vbCrLf _
& "Y- "LW1 & vbCrLf _
end sub

Private Sub (Trim)
& "X "& LH1 & vbCrLf _
& "Y "LW1 & vbCrLf _
& "X- "& LH1 & vbCrLf _
& "Y -"LW1 & vbCrLf _
end sub

Result .txt file
X200    'VP1
Y150
X-200  
Y-150
X400    'VP2
Y350
X-400  
Y-350
X300    'Trim
Y100
X-300  
Y-100
X50    'Lock
Y150
X-50    
Y-150

If you change all of those subroutines to functions similar to:

Private Function VP(CH1, CW1) as string

    VP = "X "& CH1 & vbCrLf _
       & "Y "CW1 & vbCrLf _
       & "X- "& CH1 & vbCrLf _
       & "Y- "CW1 & vbCrLf      

End Function

Select allOpen in new window

You could then use multiple print statements:

Print #1 VP(200, 150)
Print #1 VP(400, 350)

Wow, that's even more stuff to think about.

Cheers Dale

Will the print statements be concatenated in the saved .txt file?

I'm still working on this

Derek

Got basics working now. May need some help later but Excellent stuff, solved the problem

My thanks

Derek