Amend Table with SeqNumber

John Wilson I read your code on updating a form in increments of 1 however i
must be doing something incorrect as I get Run Time Errors. I have a group
of products with serial numbers already establishing a history. I am trying
to take this data set and add a sequence number to the existing one. The
data is broken down by Part Name, Part Number and its serial number. I
identified the SNStart as the last record serial number for each specific
part. I tried this code you provided on an earlier blog but am again run
errors. Can anyone provide me with the code to simply update the part number
record and its serial number by one. Next if we make 5 units make the last
record five serial numbers later. Lastly how to generate a summary report
showing the range of the amended serial number records. I have included my
current code. Thanks in advance. Private Sub Form_BeforeInsert(Cancel As
Integer)
Me![txtSNStart] = Nz(DMax("[SNStart]", "[FootSwitch1]"), 11462) + 1
End Sub

On Thu, 3 May 2007 14:15:02 -0700, bbcombo
wrote:

John Wilson

I'm guessing you mean me - the name's Vinson, not Wilson, though.

I read your code on updating a form in increments of 1 however i
must be doing something incorrect as I get Run Time Errors. I have a group
of products with serial numbers already establishing a history. I am trying
to take this data set and add a sequence number to the existing one. The
data is broken down by Part Name, Part Number and its serial number. I
identified the SNStart as the last record serial number for each specific
part. I tried this code you provided on an earlier blog but am again run
errors. Can anyone provide me with the code to simply update the part number
record and its serial number by one. Next if we make 5 units make the last
record five serial numbers later. Lastly how to generate a summary report
showing the range of the amended serial number records. I have included my
current code. Thanks in advance. Private Sub Form_BeforeInsert(Cancel As
Integer)
Me![txtSNStart] = Nz(DMax("[SNStart]", "[FootSwitch1]"), 11462) + 1
End Sub


You don't NEED a separate SNStart field, and should not have one. DMax will
find the largest value of its argument from the table - you're duplicating
effort by calculating it.

If your serial number field is named SN, and you want to store an incremented
SN in your table, and if you have data in the table already, just use

Me!txtSN = DMax("[SN], "[nameofyourtable]") + 1

If FootSwitch1 is the name of the table use it. I though FootSwitch1 was the
name of a field in the table.


John W. Vinson [MVP]