Numbering Labels

  • Thread starter Thread starter Jason F
  • Start date Start date
J

Jason F

I need to print labels that consist only of a single number! When the report
is opened a dialog box "frmMaxNo" opens and asks for the Last number and that
value is inputted in text box "txtMaxNo". When you click "OK this form is
minimized and the report opens in print preview.

I have a text box in the details part of the report.
Name: txtCount
Control Source: =1
Running Sum: over all

I have added an event procedure to the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount > Forms![frmMaxNo]![txtMaxNo]
End Sub

When I run this report and input 42 in frmMaxNo, I get just 1 label with a
number "1". I need 42 labels with 1-42 on them. Without referencing a table
or query with a set number of records, how can I make this work? I know I
need to set me record count to 42. How do I accomplish this?

Thanks in advance for your advice and guidance.
 
Hi Jason,

If you do not want to use a recordset, you will never get more than one
"record"/detail section. That said, if you still do not want to use a
recordset, how about creating an unbound text box that can grow and using
code to populate it with the appropriate numbers. Something like this
(untested) in the report's on open event:

Dim intIndex As Integer
Dim strNumbers As String
strNumbers = "1" ' Assume at least one
For intIndex = 2 to Forms![frmMaxNo]![txtMaxNo]
strNumbers = strNumbers & vbCrLf & intIndex
Next intIndex
txtCount = strNumbers

Hope that helps,

Clifford Bass
 
The simple answer is to just create a table of numbers. With some fancy SQL,
your table only needs values 0 through 9.
--
Duane Hookom
Microsoft Access MVP


Clifford Bass said:
Hi Jason,

If you do not want to use a recordset, you will never get more than one
"record"/detail section. That said, if you still do not want to use a
recordset, how about creating an unbound text box that can grow and using
code to populate it with the appropriate numbers. Something like this
(untested) in the report's on open event:

Dim intIndex As Integer
Dim strNumbers As String
strNumbers = "1" ' Assume at least one
For intIndex = 2 to Forms![frmMaxNo]![txtMaxNo]
strNumbers = strNumbers & vbCrLf & intIndex
Next intIndex
txtCount = strNumbers

Hope that helps,

Clifford Bass

Jason F said:
I need to print labels that consist only of a single number! When the report
is opened a dialog box "frmMaxNo" opens and asks for the Last number and that
value is inputted in text box "txtMaxNo". When you click "OK this form is
minimized and the report opens in print preview.

I have a text box in the details part of the report.
Name: txtCount
Control Source: =1
Running Sum: over all

I have added an event procedure to the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount > Forms![frmMaxNo]![txtMaxNo]
End Sub

When I run this report and input 42 in frmMaxNo, I get just 1 label with a
number "1". I need 42 labels with 1-42 on them. Without referencing a table
or query with a set number of records, how can I make this work? I know I
need to set me record count to 42. How do I accomplish this?

Thanks in advance for your advice and guidance.
Click to expand...
Click to expand...
 
I would not mind using recordset, I know how to generate a record sets from
table/queries. But how would a generate a recordset to work in the
application?
I'm still learing recordsets,
Thanks
Jason

Clifford Bass said:
Hi Jason,

If you do not want to use a recordset, you will never get more than one
"record"/detail section. That said, if you still do not want to use a
recordset, how about creating an unbound text box that can grow and using
code to populate it with the appropriate numbers. Something like this
(untested) in the report's on open event:

Dim intIndex As Integer
Dim strNumbers As String
strNumbers = "1" ' Assume at least one
For intIndex = 2 to Forms![frmMaxNo]![txtMaxNo]
strNumbers = strNumbers & vbCrLf & intIndex
Next intIndex
txtCount = strNumbers

Hope that helps,

Clifford Bass

Jason F said:
I need to print labels that consist only of a single number! When the report
is opened a dialog box "frmMaxNo" opens and asks for the Last number and that
value is inputted in text box "txtMaxNo". When you click "OK this form is
minimized and the report opens in print preview.

I have a text box in the details part of the report.
Name: txtCount
Control Source: =1
Running Sum: over all

I have added an event procedure to the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount > Forms![frmMaxNo]![txtMaxNo]
End Sub

When I run this report and input 42 in frmMaxNo, I get just 1 label with a
number "1". I need 42 labels with 1-42 on them. Without referencing a table
or query with a set number of records, how can I make this work? I know I
need to set me record count to 42. How do I accomplish this?

Thanks in advance for your advice and guidance.
Click to expand...
Click to expand...
 
Hi Jason,

Here is an example. You will need to add the most recent "Microsoft
ActiveX Data Objects 2.n Library" reference (Tools menu, References).

Public Sub PopulateLabelNumberTable()

Dim rstLabelNumbers As New ADODB.Recordset
Dim intIndex As Integer

' Delete any current records
CurrentProject.Connection.Execute "delete from tblLABEL_NUMBERS", , _
ADODB.CommandTypeEnum.adCmdText
With rstLabelNumbers
' Open the table as a recordset
.Open "tblLABEL_NUMBERS", CurrentProject.Connection, _
ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockOptimistic, _
ADODB.CommandTypeEnum.adCmdTable
' Add a number of records
For intIndex = 1 To 25
.AddNew
!LABEL_NUMBER.Value = intIndex
.Update
Next intIndex
.Close
End With

End Sub

If you then use that table as your record source you can get rid of
your current coding; none will be needed.

Hope that helps,

Clifford Bass
 
Hi Jason,

Oh yes, this assumes the existance of a table named "tblLABEL_NUMBERS"
that has a column "LABEL_NUMBER".

Clifford Bass
 
Back
Top