Interesting Problem...

[brad.goldberg]

I have a field that auto increments a number for each record. 0001,
0002 and so on. I also have two buttons on the form. One button is used
to "save" the record after all fields are entered. After a record is
saved and the user moves onto a new record all the fields in the
previous record become locked to prevent accidental/or malicious
deletion/editing of fields. If something needs to be changed I have an
Edit button which temporarily unlocks all fields. At this point the
user makes changes, and then clicks the same button which locks the
fields again. Problem is my auto increment number which is like an
invoice number will increment. Is there any way to stop this from
happening, like lock that field out permenently or something???

This is the code for the auto incrementing number:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber",
"fields"), "0")) + 1, "0000")

End Sub

This is the code for the save button:

Private Sub saveRecord_Click()
On Error GoTo Err_saveRecord_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

Exit_saveRecord_Click:
Exit Sub

Err_saveRecord_Click:
MsgBox Err.Description
Resume Exit_saveRecord_Click

End Sub

This is the code for the "edit" unlock/relock fields button:

Private Sub editRun_Click()

If Me.EditRun.Caption = "Edit Run" Then
With Me
.AllowAdditions = True
.AllowEdits = True
.AllowDeletions = True
.EditRun.Caption = "Save Changes"
End With

Else
DoCmd.RunCommand acCmdSaveRecord
With Me
.AllowAdditions = False
.AllowEdits = False
.AllowDeletions = False
.EditRun.Caption = "Edit Run"
End With

End If

End Sub

So basically, whenever a past record is edited and resaved the
displayedRunNumber increments and I need that to stop.

Thanks in advance,

Brad G.

 

[Tom Wickerath]

Hi Brad,

One solution would be to test for a new record, as in:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = True Then
Me!displayedRunNumber = Format(CLng(Nz( _
DMax("displayedRunNumber", "fields"), "0")) + 1, "0000")
End If

End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

This is the code for the auto incrementing number:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber",
"fields"), "0")) + 1, "0000")

End Sub

Try wrapping this in an IsNull:

If IsNull(Me!displayedRunNumber) Then
Me!displayedRunNumber = ...
End If

This will only assign a new number if the current record has none.

John W. Vinson[MVP]

 

[Tom Wickerath]

Access MVP Roger Carlson offers a download that shows an alternate method:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________