Thanks for your reply. I agree that there must be a better way to do
this than changing code on the fly. I have a Form where the user can
input the number of labels they want to print for each category, for
example ten labels for books, 6 labels for shoes, etc. There are 24
categories and I want to make it possible for the user to create a new
category instead of getting a database programmer involved every time
they want a new category. On the Form there is a textbox and label for
each category and a textbox for the total. The user types the number
of labels wanted in each texbox. I could add some extra textboxes and
labels that are reserved for new categories and make them invisible
until the user needs them. But each category is hard-coded in the VBA
code (see below). Do you think I should re-write the while-wend part
so that it pulls the data for each category from a table has the data
for each category? For example, something like
strCategorySpanish = DLookup("CategoryNameSpanish", "CategoriesTable",
"RowID = Books") ?
I included more of the code below.
Public Sub BarCodePrintCategories_Click()
Dim lngBooks As Long
Dim lgnNewRecords As Long
lgnNewRecords = (Nz(TotalLabelsNumberBox, 0))
' The line below is repeated for each category
lngBooks = (Nz(BooksNumberBox, 0))
' the while-wend statement below is reapeated for each category
While lngBooks > 0
If lngBooks >= 1 Then
strCategory = "Books"
strCategorySpanish = "Libros"
dblPriceUS = 50
dblPriceBolivia = 0.5
Call BarCodeLabelMakerFormPrintButton_Click
lngBooks = (lngBooks - 1)
End If
Wend
Dim MyDB As Database, MyRecords As Recordset, Total As Long
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyRecords = MyDB.OpenRecordset("Products")
MyRecords.MoveLast
Total = MyRecords.RecordCount
Dim intPrintStartNumber As Long
Dim strLabelReportName As String
intPrintStartNumber = (Total - lgnNewRecords)
intPrintStartNumber = (intPrintStartNumber + 1)
strLabelReportName = "ReportForPrintingBarcodeLabels"
Application.Echo False
DoCmd.SelectObject acReport, strLabelReportName, True
'###################
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
If lgnNewRecords = 1 Then
Msg = "Is it OK to print " & lgnNewRecords & " label ?" ' Define
message.
Else: Msg = "Is it OK to print " & lgnNewRecords & " labels ?" '
Define message.
End If
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = " OK to Print Labels?" ' Define title.
Response = msgbox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
DoCmd.PrintOut acSelection, intPrintStartNumber, Total
End If
DoCmd.OpenForm "BarCodeLabelMakerForm"
Application.Echo True
End Sub
Private Sub BarCodeLabelMakerFormPrintButton_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim lngNewRecords As Long
Dim lngCount As Long
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("Products")
lgnNewRecords = (Nz(TotalLabelsNumberBox, 0))
Dim intLarge As String
Dim intLargest As String
intLarge = DMax("BoxNumber", "Products")
intLargest = (intLarge + 1)
strADDAsterisks = Eval(intLargest)
rs.AddNew
rs.Fields("BoxNumber") = (intLarge + 1)
rs.Fields("ItemCategoryNameProducts") = strCategory
rs.Fields("ItemCategoryNameProductsSpanish") =
strCategorySpanish
rs.Fields("PriceUS") = dblPriceUS
rs.Fields("PriceBolivia") = dblPriceBolivia
rs.Update
Set rs = Nothing
Set dbs = Nothing
End Sub
Larry Linson wrote:
It is not at all clear to me what you are trying to _accomplish_ with what
you describe (that is, you describe how you are thinking to go about it, not
what you are trying to do). Assuming a user types in a new category, say
"Shoes", and you changed this VBA so that "lngReservedForNewCategory01"
reads "lngShoes", there won't be a value in "lngShoes", so the code wouldn't
execute, in any case . . . unless there are details you haven't included.
I suspect there is a simpler way to accomplish what you want to do, if only
we knew what that is. (That's based on almost 50 years in the software
business, during which time I have observed that "there is almost always a
better way to accomplish any given thing than changing code 'on the fly'".)
Larry Linson
Microsoft Access MVP
<rc*********@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...I have a form that lists several different categories for labels. I
made a form that will let the user add a new category. On the Form the
user can type in the name for the new category, then I thought I would
search the code below and replace "ReservedForNewCategory01" (or the
next available category, possibly "ReservedForNewCategory02"), with the
name of the new category that the user typed into the Form. How can I
search and replace the VBA code from the button-click on the Form?
While lngBooks > 0
If lngBooks >= 1 Then
strCategory = "Books"
strCategorySpanish = "Libros"
dblPriceUS = 50
dblPriceBolivia = 0.5
Call BarCodeLabelMakerFormPrintButton_Click
lngBooks = (lngBooks - 1)
End If
Wend
While lngReservedForNewCategory01 > 0
If lngReservedForNewCategory01 >= 1 Then
strCategory = "Books"
strCategorySpanish = "Libros"
dblPriceUS = 50
dblPriceBolivia = 0.5
Call BarCodeLabelMakerFormPrintButton_Click
lngReservedForNewCategory01 = (lngReservedForNewCategory01 - 1)
End If
Wend
While lngReservedForNewCategory02 > 0
If lngReservedForNewCategory02 >= 1 Then
strCategory = "Books"
strCategorySpanish = "Libros"
dblPriceUS = 50
dblPriceBolivia = 0.5
Call BarCodeLabelMakerFormPrintButton_Click
lngReservedForNewCategory02 = (lngReservedForNewCategory02 - 1)
End If
Wend
