Value auto-calculated via query doesn't "stick" in field

[lwwhite]

Access 2003. I have a form (form view) with a subform (datasheet view). The form has a map_id field and each record on the subform also has a map_id field. I want the fields on the form and subform to automatically display the next available number in a defined sequence when I add a new record to either. I have been able to successfully generate and display the next available number in the form and datasheet (using a query), but I can't make the value "stick." That is, I add a new record and "1234" appears in the field. I save the record. I add another new record. "1234" appears in that field, too, when it should be "1235." I have found that if I actually click in the field and then Save, the value does stick. However, this is not an intuitive workflow for my users. Any suggestions on how to resolve this issue are greatly appreciated.

[nico5038]

To prevent trouble, I generally INSERT such a record from code and then show an update form. When the user cancels the form I issue a DELETE. The only problem can be that when multiple users are inserting rows, a gap can be created this way...
The other solution would be to issue the new number in the BeforeUpdate/BeforInsert event of the form.
To get the next number use a DMAX() in the event like:
Me.ID = DMAX("ID","tblYours") + 1
This will however "hide" the new value for the user.

Nic;o)

[MMcCarthy]

Access 2003. I have a form (form view) with a subform (datasheet view). The form has a map_id field and each record on the subform also has a map_id field. I want the fields on the form and subform to automatically display the next available number in a defined sequence when I add a new record to either. I have been able to successfully generate and display the next available number in the form and datasheet (using a query), but I can't make the value "stick." That is, I add a new record and "1234" appears in the field. I save the record. I add another new record. "1234" appears in that field, too, when it should be "1235." I have found that if I actually click in the field and then Save, the value does stick. However, this is not an intuitive workflow for my users. Any suggestions on how to resolve this issue are greatly appreciated.

What is the control source of these fields set to. Are they correctly set to the relevant fields?

[NeoPa]

You'll need to test this, but I think this code may do the trick :

Private Sub Form_AfterUpdate()
  Call Me.Requery
End Sub

The problem is due to the underlying recordset (of the form) not reflecting the extra record until a ReQuery is done.