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 van Stiphout]

On 31 Jul 2006 21:48:17 -0700, br***********@gmail.com wrote:

Yes.
If Me.NewRecord then
'get a new ID
end if

Consider using BeforeInsert.

-Tom.

>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.

[pietlinden]

not very... your code is in the wrong event. It should be in the
Before_INSERT, not the BeforeUpdate event. once the record has been
added to the table (inserted), it *already* has an ID number, so
there's no need to assign a new one.

[brad.goldberg]

Ha.. What you said sounds good.. but I have no idea how to do what your
suggesting.. any ideas... Thanks
Tom van Stiphout wrote:

On 31 Jul 2006 21:48:17 -0700, br***********@gmail.com wrote:

Yes.
If Me.NewRecord then
'get a new ID
end if

Consider using BeforeInsert.

-Tom.

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.

[brad.goldberg]

Actually I understood your If statement solution, such an easy fix
thank you..

In regards to the before insert... I like it in beforeupdate that way
my users are forced to fill in ALL the required fields before getting
the Run Number. Basically once they have a Run Number their off the
hook so it forces them to type in all the info before getting that
number.

Make sense or am I still off?
[e-mail] wrote:

Ha.. What you said sounds good.. but I have no idea how to do what your
suggesting.. any ideas... Thanks
Tom van Stiphout wrote:

On 31 Jul 2006 21:48:17 -0700, br***********@gmail.com wrote:

Yes.
If Me.NewRecord then
'get a new ID
end if

Consider using BeforeInsert.

-Tom.

>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.

[RoyVidar]

"br***********@gmail.com" <br***********@gmail.comwrote in message
<11**********************@i3g2000cwc.googlegroups. com>:

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.

Restrict the updating of this control to when there's a new record

Private Sub Form_BeforeUpdate(Cancel As Integer)

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

--
Roy-Vidar

[brad.goldberg]

Actually I understood your If statement solution, such an easy fix
thank you..

In regards to the before insert... I like it in beforeupdate that way
my users are forced to fill in ALL the required fields before getting
the Run Number. Basically once they have a Run Number their off the
hook so it forces them to type in all the info before getting that
number.

Make sense or am I still off?
[e-mail] wrote:

Ha.. What you said sounds good.. but I have no idea how to do what your
suggesting.. any ideas... Thanks
Tom van Stiphout wrote:

On 31 Jul 2006 21:48:17 -0700, br***********@gmail.com wrote:

Yes.
If Me.NewRecord then
'get a new ID
end if

Consider using BeforeInsert.

-Tom.

>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.

[Rick Brandt]

pi********@hotmail.com wrote:

not very... your code is in the wrong event. It should be in the
Before_INSERT, not the BeforeUpdate event. once the record has been
added to the table (inserted), it *already* has an ID number, so
there's no need to assign a new one.

The problem with BeforeInsert is that it won't work reliably in a multi-user
environment. In addition, there are cases where you can create a new record and
save it without BeforeInsert ever firing.

As long as you test for NewRecord BeforeUpdate is the best event for this
because it is the only event that ends with the record being saved.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com