Fixed view of Form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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?
Click to expand...

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]
 
John Vinson said:
Why would you WANT to? To put one to nine empty, meaningless records
into your table whenever you enter an order???
Click to expand...

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?
Click to expand...

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]
Click to expand...

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?
 
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))
Click to expand...

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?
Click to expand...

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]
 
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))
Click to expand...

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?
Click to expand...

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?
Click to expand...

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]
Click to expand...
 
Tec92407 said:
:

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))
Click to expand...

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?
Click to expand...

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

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] = "))
 
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.

Douglas J. Steele said:
Tec92407 said:
:

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?
Click to expand...

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

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] = "))
Click to expand...
 
Back
Top