need help on sequence number between related form and subform

[Eric]

I have the need to create a sequence id on a subform for records that relate
to the master record on the main form. The form and subform linkage works
great, I am building packages of inventory items for later import into a sql
based application. Each inventory package has a parent record, and each
inventory item added to the package needs a sequence ID beginning with 1 and
incrementing to however many items the user owns. I have a package_id field
as the linkage key between the forms and an autonumber on the subform.
Please advise as to what vba code would be most effective at:


1. Establish the max sequence # and increment for new records.
2 Perform a check of the sequence number on current for edits and deletions
by the user.

Many thanks for any help or direction. I am new to access-vb...

 

[John W. Vinson]

I have the need to create a sequence id on a subform for records that relate
to the master record on the main form. The form and subform linkage works
great, I am building packages of inventory items for later import into a sql
based application. Each inventory package has a parent record, and each
inventory item added to the package needs a sequence ID beginning with 1 and
incrementing to however many items the user owns. I have a package_id field
as the linkage key between the forms and an autonumber on the subform.
Please advise as to what vba code would be most effective at:


1. Establish the max sequence # and increment for new records.

Your child link field *CANNOT* be an autonumber. You cannot control it, update
it, or edit it, so it cannot provide a usable link to the mainform.

You can use the Subform's BeforeInsert event to increment the sequence number:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSequenceNo = NZ(DMax("[SequenceNo]", "[ChildTable]", "[package_id] = " _
& Me!package_id)) + 1
End Sub

where MasterID is the linking field to the mainform (and where you need to
edit the table and field names to suit).

2 Perform a check of the sequence number on current for edits and deletions
by the user.

If you mean that you want to resequence all the sequence numbers for a given
master record whenever the user edits or deletes one.... prepare for dealing
with a very nasty can of worms. What if you have users who have printed out an
inventory record and know that sequence number 3 refers to a Left Handed
Widget? Do you want the renumbering to erase that printout, and that knowledge
from the user's mind? If there are 821 items in the inventory, do you want to
renumber 819 of them if the second one should be erased? If the data has
already been uploaded to SQL do you want to renumber it? JUST LEAVE A GAP.
It's a heck of a lot simpler!

John W. Vinson [MVP]

 

[Eric]

Many thanks for your reply. I agree with point number two, I will not try to
resequence, but just leave a gap for the record set. I don't think that will
cause a problem downstream.

I have been struggling with your code now for sometime. I understand the
non-zero function but don't understand why the three fields are grouped
together for the max statement. Can you break down for me what it is mean to
accomplish as I can't put it together with the references that I have.

Also, to clarify my sequence field on the subform is pkg_dtl_seq, it is a
number field. pk_id is the linking field between the subform and the
mainform, it is an autonumber on the mainform, and I don't want to edit it on
the mainform, I simply want to increment the sequence ID as detail items are
added.

Example: I may have package 34 with 4 inventory items added. I will then
have 4 records in my subform, with vairous inventory codes, etc. Each record
will have a pkg_id value of 34, and the pkg_dtl_seq value will increment from
1 to 4 as the records are added.

I have the need to create a sequence id on a subform for records that relate
to the master record on the main form. The form and subform linkage works
great, I am building packages of inventory items for later import into a sql
based application. Each inventory package has a parent record, and each
inventory item added to the package needs a sequence ID beginning with 1 and
incrementing to however many items the user owns. I have a package_id field
as the linkage key between the forms and an autonumber on the subform.
Please advise as to what vba code would be most effective at:


1. Establish the max sequence # and increment for new records.

Your child link field *CANNOT* be an autonumber. You cannot control it, update
it, or edit it, so it cannot provide a usable link to the mainform.

You can use the Subform's BeforeInsert event to increment the sequence number:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSequenceNo = NZ(DMax("[SequenceNo]", "[ChildTable]", "[package_id] = " _
& Me!package_id)) + 1
End Sub

where MasterID is the linking field to the mainform (and where you need to
edit the table and field names to suit).

2 Perform a check of the sequence number on current for edits and deletions
by the user.

If you mean that you want to resequence all the sequence numbers for a given
master record whenever the user edits or deletes one.... prepare for dealing
with a very nasty can of worms. What if you have users who have printed out an
inventory record and know that sequence number 3 refers to a Left Handed
Widget? Do you want the renumbering to erase that printout, and that knowledge
from the user's mind? If there are 821 items in the inventory, do you want to
renumber 819 of them if the second one should be erased? If the data has
already been uploaded to SQL do you want to renumber it? JUST LEAVE A GAP.
It's a heck of a lot simpler!

John W. Vinson [MVP]

 

[Eric]

I got it. My problem was that I was calling the child form in the domain
field instead of the child table. It now appears to work. Many thanks.

Many thanks for your reply. I agree with point number two, I will not try to
resequence, but just leave a gap for the record set. I don't think that will
cause a problem downstream.

I have been struggling with your code now for sometime. I understand the
non-zero function but don't understand why the three fields are grouped
together for the max statement. Can you break down for me what it is mean to
accomplish as I can't put it together with the references that I have.

Also, to clarify my sequence field on the subform is pkg_dtl_seq, it is a
number field. pk_id is the linking field between the subform and the
mainform, it is an autonumber on the mainform, and I don't want to edit it on
the mainform, I simply want to increment the sequence ID as detail items are
added.

Example: I may have package 34 with 4 inventory items added. I will then
have 4 records in my subform, with vairous inventory codes, etc. Each record
will have a pkg_id value of 34, and the pkg_dtl_seq value will increment from
1 to 4 as the records are added.

I have the need to create a sequence id on a subform for records that relate
to the master record on the main form. The form and subform linkage works
great, I am building packages of inventory items for later import into a sql
based application. Each inventory package has a parent record, and each
inventory item added to the package needs a sequence ID beginning with 1 and
incrementing to however many items the user owns. I have a package_id field
as the linkage key between the forms and an autonumber on the subform.
Please advise as to what vba code would be most effective at:


1. Establish the max sequence # and increment for new records.

Your child link field *CANNOT* be an autonumber. You cannot control it, update
it, or edit it, so it cannot provide a usable link to the mainform.

You can use the Subform's BeforeInsert event to increment the sequence number:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSequenceNo = NZ(DMax("[SequenceNo]", "[ChildTable]", "[package_id] = " _
& Me!package_id)) + 1
End Sub

where MasterID is the linking field to the mainform (and where you need to
edit the table and field names to suit).

2 Perform a check of the sequence number on current for edits and deletions
by the user.

If you mean that you want to resequence all the sequence numbers for a given
master record whenever the user edits or deletes one.... prepare for dealing
with a very nasty can of worms. What if you have users who have printed out an
inventory record and know that sequence number 3 refers to a Left Handed
Widget? Do you want the renumbering to erase that printout, and that knowledge
from the user's mind? If there are 821 items in the inventory, do you want to
renumber 819 of them if the second one should be erased? If the data has
already been uploaded to SQL do you want to renumber it? JUST LEAVE A GAP.
It's a heck of a lot simpler!

John W. Vinson [MVP]