autofill form with increasing number

[Lori]

Hello,
I'm a novice and have not used Access in awhile.

tblPurchaseOrder joins tblPOPayments on PO#, 1 to many. When Payments are
added in the subfPOPayments (on frmPurchaseOrder), I would like the Payment#
to autofill with 1,2,3,4, etc., each time a new Payment is added. I have put
a field in tblPOPayments for this, but I do not know what expression to put
in the form field. I made the field a number field.

Thank you for your help,
Lori

 

[golfinray]

Can you use autonumber? Look at help for autonumber. That should do what you
need to do.

 

[Lori]

I guess I don't understand how autonumber can work with each group of
payments for one PO. I have an autonumber in the tblPOPayments just to have
a primary key for the payments. But I'd like the user to see on the form
Payment 1, Payment 2, Payment 3, etc., for each payment of each PO record but
not have to type it in themselves.

 

[John W. Vinson]

Hello,
I'm a novice and have not used Access in awhile.

tblPurchaseOrder joins tblPOPayments on PO#, 1 to many. When Payments are
added in the subfPOPayments (on frmPurchaseOrder), I would like the Payment#
to autofill with 1,2,3,4, etc., each time a new Payment is added. I have put
a field in tblPOPayments for this, but I do not know what expression to put
in the form field. I made the field a number field.

Thank you for your help,
Lori

You can put some VBA code in the subform's BeforeInsert event. Open the form
in design view; view its Properties; find the BeforeInsert property on the
Events tab. Click the ... icon by it and choose Code Builder. Access will give
you the Sub and End Sub lines for free - just add one more line:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!numberfield = NZ(DMax("[numberfield]", "POPayments", _
"[PONumber] = " & Me!PONumber)) + 1
End Sub

I'm making assumptions about your field and tablenames of course...

 

[Lori]

Much thanks.

Hello,
I'm a novice and have not used Access in awhile.

tblPurchaseOrder joins tblPOPayments on PO#, 1 to many. When Payments are
added in the subfPOPayments (on frmPurchaseOrder), I would like the Payment#
to autofill with 1,2,3,4, etc., each time a new Payment is added. I have put
a field in tblPOPayments for this, but I do not know what expression to put
in the form field. I made the field a number field.

Thank you for your help,
Lori

You can put some VBA code in the subform's BeforeInsert event. Open the form
in design view; view its Properties; find the BeforeInsert property on the
Events tab. Click the ... icon by it and choose Code Builder. Access will give
you the Sub and End Sub lines for free - just add one more line:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!numberfield = NZ(DMax("[numberfield]", "POPayments", _
"[PONumber] = " & Me!PONumber)) + 1
End Sub

I'm making assumptions about your field and tablenames of course...

 

[Lori]

Hello,

So, would I use similar code in an update query to fill in numberfield for
all the payments that have already been added? I realized I should do this
first.

Thank you much,
Lori

Hello,
I'm a novice and have not used Access in awhile.

tblPurchaseOrder joins tblPOPayments on PO#, 1 to many. When Payments are
added in the subfPOPayments (on frmPurchaseOrder), I would like the Payment#
to autofill with 1,2,3,4, etc., each time a new Payment is added. I have put
a field in tblPOPayments for this, but I do not know what expression to put
in the form field. I made the field a number field.

Thank you for your help,
Lori

You can put some VBA code in the subform's BeforeInsert event. Open the form
in design view; view its Properties; find the BeforeInsert property on the
Events tab. Click the ... icon by it and choose Code Builder. Access will give
you the Sub and End Sub lines for free - just add one more line:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!numberfield = NZ(DMax("[numberfield]", "POPayments", _
"[PONumber] = " & Me!PONumber)) + 1
End Sub

I'm making assumptions about your field and tablenames of course...