Fixed view of Form?

[Guest]

I have a purchase order form that contains a continuous "items" subform. The
subform contains records of items to be purchased. It basically represents an
existing paper form. The paper form shows ten empty line items numbered from
one to ten. Usually only two or three items are used. What I'd like to do is
mimic this form.

Questions:

1. How can I automatically add ten empty records numbered from one to ten?

2. how can I fix the length of the Items subform so that it will never cause
a printout to overflow onto another page?

 

[John Vinson]

I have a purchase order form that contains a continuous "items" subform. The
subform contains records of items to be purchased. It basically represents an
existing paper form. The paper form shows ten empty line items numbered from
one to ten. Usually only two or three items are used. What I'd like to do is
mimic this form.

Questions:

1. How can I automatically add ten empty records numbered from one to ten?

Why would you WANT to? To put one to nine empty, meaningless records
into your table whenever you enter an order???

2. how can I fix the length of the Items subform so that it will never cause
a printout to overflow onto another page?

You can automatically add a LineNumber value to the form, so that the
first item entered will get 1, the second 2 and so on, and prevent
adding an eleventh record. To do so, include an Integer field
LineNumber in the table, and in the subform's BeforeInsert event put
code

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext as Integer
iNext = NZ(DMax("[LineNumber]", "ItemsTable", "[OrderNo] = " _
& Me.OrderNo)
If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
Else
Me!txtLineNumber = iNext + 1
End If
End Sub

If you're printing the Form... don't. Forms are optimized for onscreen
data editing. Create a Report for printing.

John W. Vinson[MVP]

 

[Guest]

Why would you WANT to? To put one to nine empty, meaningless records
into your table whenever you enter an order???

yeah...your right. I was trying to think of the form as a piece of paper.
I'll create a report.

2. how can I fix the length of the Items subform so that it will never cause
a printout to overflow onto another page?

You can automatically add a LineNumber value to the form, so that the
first item entered will get 1, the second 2 and so on, and prevent
adding an eleventh record. To do so, include an Integer field
LineNumber in the table, and in the subform's BeforeInsert event put
code

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext as Integer
iNext = NZ(DMax("[LineNumber]", "ItemsTable", "[OrderNo] = " _
& Me.OrderNo)
If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
Else
Me!txtLineNumber = iNext + 1
End If
End Sub

If you're printing the Form... don't. Forms are optimized for onscreen
data editing. Create a Report for printing.

John W. Vinson[MVP]

OK. Here's the code I tested that works.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim iNext As Integer
iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = 1" & Me.PRID))
If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
Else
Me!ItemNo = iNext + 1
End If
End Sub

When I try to specify this in the BeforeInsert property, I keep getting
"Access can't find Macro" error message.
What is the proper way to specify this in the subform BeforeInsert property
field?

 

[John Vinson]

OK. Here's the code I tested that works.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim iNext As Integer
iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = 1" & Me.PRID))

This is rather odd. If the PRID field on the current record in your
Form contains 831, this will find the maximum ItemNo for PRID 1831 in
your table. What's with the apparently inappropriate 1?

If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
Else
Me!ItemNo = iNext + 1
End If
End Sub

When I try to specify this in the BeforeInsert property, I keep getting
"Access can't find Macro" error message.
What is the proper way to specify this in the subform BeforeInsert property
field?

The BeforeInsert property should show [Event Procedure], and the
Form's Module should contain this Sub. You can get there by opening
the form in design view; view its properties; click the ... icon by
the Before Insert event; invoke Code Builder; and copy and paste the
code in place of the Sub and End Sub lines that Access provides.

John W. Vinson[MVP]

 

[Guest]

ItemNo is the field containing the Item number I want to auto increment.
PRItems is the table containing ItemNo and is underlying table for the Items
continuous subform.
PRID is the foreign key contained in PRItems table and is the Primary key in
the Purchase Requisition table.


I guess I don't fully understand what the following code fragment is
supposed to do.

iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = 1" & Me.PRID))

This is rather odd. If the PRID field on the current record in your
Form contains 831, this will find the maximum ItemNo for PRID 1831 in
your table. What's with the apparently inappropriate 1?

If I don't specify a value, a "missing operator" error is generated.

If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
Else
Me!ItemNo = iNext + 1
End If
End Sub

When I try to specify this in the BeforeInsert property, I keep getting
"Access can't find Macro" error message.
What is the proper way to specify this in the subform BeforeInsert property
field?

The BeforeInsert property should show [Event Procedure], and the
Form's Module should contain this Sub. You can get there by opening
the form in design view; view its properties; click the ... icon by
the Before Insert event; invoke Code Builder; and copy and paste the
code in place of the Sub and End Sub lines that Access provides.

John W. Vinson[MVP]

 

[Douglas J. Steele]

:

I guess I don't fully understand what the following code fragment is
supposed to do.

iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = 1" & Me.PRID))

This is rather odd. If the PRID field on the current record in your
Form contains 831, this will find the maximum ItemNo for PRID 1831 in
your table. What's with the apparently inappropriate 1?

If I don't specify a value, a "missing operator" error is generated.

That sounds, then, as though you don't have a value in Me.PRID so that what
you get when you don't have the 1 evalutates to

iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = "))

 

[Guest]

Ok...Here's the correct code.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim iNext As Integer
iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] =" & Forms!PRRequisition!PRID))
If iNext >= 10 Then
MsgBox "Ten items are the limit. Start a new order.", vbOKOnly
Cancel = True
iNext = 0
Else
Me!ItemNo = iNext + 1
End If
End Sub

I didn't have a control named PRID on the (active) sub form, but on the main
form.
It contains the key number for the current items being entered.

Works correctly now.
Thanks everyone for all your help.

:

I guess I don't fully understand what the following code fragment is
supposed to do.

iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = 1" & Me.PRID))

This is rather odd. If the PRID field on the current record in your
Form contains 831, this will find the maximum ItemNo for PRID 1831 in
your table. What's with the apparently inappropriate 1?

If I don't specify a value, a "missing operator" error is generated.

That sounds, then, as though you don't have a value in Me.PRID so that what
you get when you don't have the 1 evalutates to

iNext = Nz(DMax("[ItemNo]", "PRItems", "[PRID] = "))