Help with loop code please

[sprinklerhead]

I used this code and it worked, but now I need to change [Roll Number] and [txtEnd] to a letter ie. A B C etc... and don't know how to do the loop. Could someone please explain?

Private Sub cmdPrint_Tickets_Click()
On Error GoTo Err_cmdPrint_Tickets_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Do Until [Roll Number] = [txtEnd]  + 1
    
    If optP1 = -1 Then
    DoCmd.OpenReport "rptTicketP1", acViewPreview, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP2 = -1 Then
    DoCmd.OpenReport "rptTicketP2", acViewPreview, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP3 = -1 Then
    DoCmd.OpenReport "rptTicketP3", acViewPreview, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP4 = -1 Then
    DoCmd.OpenReport "rptTicketP4", acNormal, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP5 = -1 Then
    DoCmd.OpenReport "rptTicketP5", acNormal, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP6 = -1 Then
    DoCmd.OpenReport "rptTicketP6", acNormal, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP7 = -1 Then
    DoCmd.OpenReport "rptTicketP7", acNormal, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    If optP8 = -1 Then
    DoCmd.OpenReport "rptTicketP8", acNormal, "", "[IndexNo]=[Forms]![frm9].[IndexNo]"
    End If
    [Roll Number] = [Roll Number] + 1
    Exit Do
    Loop
           
Exit_cmdPrint_Tickets_Click:
    Exit Sub

 

[PHV]

Have a look at the Chr() and Asc() functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sprinklerhead]

Hi PHV,
Thanks for your quick reply, unfortunately I am a beginner and don't grasp algebra concepts well. I have looked at Chr() and Asc() but can't figure out how to apply them to this situation. Is there a way I can simply convert the Letter A to the Number 1? What I'm trying to do is to automatically advance the text field [Roll Number] to the next letter in the alphabet when a user clicks on the print button.I'd like to advance the letter until it is the same letter as the [txtEnd] field and then stop advancing or printing.When using Numbers this worked fine but my company recently changed their roll numbering code to include letters as the set number.Maybe there is a better solution, let me explain.

We make paper, it comes of the machine in a reel. 130" wide by 80" diameter. We call them Reels and assign a Reel number and date code. ie. WAJ07L0601 this code breaks down to:
WA=Corperation J=Mill 0=Machine 7=year L=Month 06=day and 01=first reel of the day.
Next the reel is put on a rewinder and cut down to smaller rolls. These rolls take the parent code and the first set gets an A designation, if two rolls are cut in first set then each roll would recieve the code as follows;
WAJ07L0601A00 and WAJ07L0601A50 the Reel is big enough for two sets so the next set is WAJ07L0601B00 and WAJ07L0601B50
as you can see the Set Letter changes but parent code remains the same. Breaking the roll number code down further ; WAJ070601 same as above, A00 & A50= two rolls cut from the 130" width but half the dia. so 40" 00 and 50 is the percent of the width where the roll begins, ie. two 65" rolls, and A is the first set cut from the reel, B is the second set cut.The next reel would be WAJ07L0602 and rolls would be
WAJ07L0602A00 etc...
I have a form set up where the user enters all the pertinant information and the label is created. I am trying to make it so when the user prints the label the Set Letter will change to the next set automatically until they change the Reel number(01)in the above example. I am currently using a calculation that figures the roll positions based on roll size and percent of trim. everything works well but the letter designation is throwing me for a loop.
Optionally I could add a field on the form asking for the number of sets per reel and perform a routine that would take the [number of sets] and put the appropriate letter in the label field for the number ie. [number of sets] is 3 so I would need Two tickets printed for set A and two tickets for set B and two for set C. I can't figure out how to accomplish this either.
I hope I'm not breaking any posting conventions with this long post, Thanks for listening.

 

[PHV]

convert the Letter A to the Number 1
Asc("A") -64

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886