numbering items in a series

[evansc]

I have a library DB. The Main table lists all the items by Number. A second table lists the number of copies I have of each item--linked by the number. A third table (currently) lists the items in the series for each Number. E.G., Item #320 has 4 parts. so the third table would have four records that list "1, 2, 3, 4" with the second field of each record being #320. My query that combines all this finds the sum of the records in the series table, so it knows there are 4.

I print labels for each item. The labels say "Item #320, Copy 1, Piece 1 of 4",etc.


I would like to get rid of the "items in series" table. I want to just have a field in the Main table called "Number of pieces in series" and then put 4. THEN I want the labels to still print the same way. (I can't just use the page numbering system because of the number of copies. I can have up to 5 copies of one item and each one has to be labeled with Piece 1 or 4, 2 of 4, etc.)

Is this possible? I can't figure out how to make the query take the number 4 and then know to list 1 through 4.

 

[randy700]

How about something like this...

For x = 1 to YourItemsField
Insert the code to print
"Copy number " & x & " of " & YourItemsField
Next



Randy

 

[evansc]

Sorry--I'm very much a beginner at code. Where should I put that? In the code that runs when I click the button to preview the report? That code is as follows: (with the exception of the refresh command at the beginning, this was just generated by the button "wizard.")

Private Sub Lending_Copy_Spines_Click()
On Error GoTo Err_Lending_Copy_Spines_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Dim stDocName As String

stDocName = "Lending Video Spines"
DoCmd.OpenReport stDocName, acPreview

Exit_Lending_Copy_Spines_Click:
Exit Sub

Err_Lending_Copy_Spines_Click:
MsgBox Err.Description
Resume Exit_Lending_Copy_Spines_Click

Should I put it after the refresh and before the rest of all this? Or should I put it somewhere else?

 

[evansc]

Okay, I think I'm understanding this a bit better. Here's the code I have now behind the button for my print preview.


Private Sub Lending_Copy_Spines_Click()
On Error GoTo Err_Lending_Copy_Spines_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Dim stDocName As String
Dim x As Integer

stDocName = "Lending Video Spines"
For x = 1 To [Items in Series]
DoCmd.OpenReport stDocName, acPreview
Trim ([x] & " of " & [Items in Series])

Next


Exit_Lending_Copy_Spines_Click:
Exit Sub

Err_Lending_Copy_Spines_Click:
MsgBox Err.Description
Resume Exit_Lending_Copy_Spines_Click

End Sub

I think it's beginning to work. But the labels created by the "x" integer are completely blank. Any ideas?