By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,085 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

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

lwwhite
P: 16
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.
Feb 7 '07 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Feb 7 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Feb 8 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
You'll need to test this, but I think this code may do the trick :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.   Call Me.Requery
  3. End Sub
The problem is due to the underlying recordset (of the form) not reflecting the extra record until a ReQuery is done.
Feb 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.