Increment a number field by 1 Save Issues

[mlsammie]

Hello All!

I have a field called MissionNumber in a table called Missions. I want this field to autogenerate the next available number for the user. This field is not the primary key and it is a number field. I have been able to accomplish the increment of the next available by placing the following in the default setting of the field on the form:

DMax("MissionNumber", "Missions") + 1

However, I have a problem with saving the record. Unless I physically click and re-enter the number that was generated by the DMax function. Can anyone explain why this is happening and is there a way around this behavior. I do not want the user to have to type in the number.

Thanks in Advance!

 

[TheAceMan1]

How are ya mlsammie . . . . .

[blue]For a record to be saved it has to be Edited[/blue] (in edit mode) or [blue]Dirty[/blue] as they say. This is the function of [purple]that little pencil icon[/purple] on the record selector. [blue]It tells you if that record is is in edit mode[/blue] or currently being edited.

The Default Value is used for new records, and when its posted, does not trigger edit mode. Hence the reason you have to some how edit the record in order to save it.

Be aware, the same is true for [blue]AutoNumber[/blue] fields. In fact you'll find they won't pop-up [blue]until you start editing.[/blue]

So according to the above, your looking to save without editing.

An excellent locations for your[blue]Dmax Function[/blue] would be the [blue]On Dirty Event[/blue] of the form, which does'nt get triggered until you edit a record:

[blue]Private Sub Form_Dirty(Cancel As Integer)
   
   If Me.NewRecord Then
      Me![purple][b]ControlName[/b][/purple] = DMax("MissionNumber", "Missions") + 1
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[mlsammie]

Thanks AceMan1!

Your suggestion worked great!

Also thanks for the explaination!

mlsammie