SaveRecord when in Form Add

[noelville]

I have searched for a solution and have tried all the suggestions found(e.g. command button to save, setfocus before attempting recordsave, etc).
I've tried updating and saving with a pre-existing record and SaveRecord works fine.

My problem is a Data entry form in a system I inherited.

I've noticed the insert does not happen till the User exits the current form. This has caused problems in the past when things get busy, one User's record overlays another User's record because they did not exit the form soon enough. The problem is the new ID number is max of previous ID# and if insert doesn't happen in a timely manner, the same ID# gets allocated to different Users. I want to minimize changes to this form.

All suggestions are welcome.
Thanks.

 

[jebry]

Hi!

Not knowing you complete situation one potential solution that would be quick to implement is to add a table that stores the current max id. Then use code to read the id from that table and increment it by one for every new record. That way users can't get the same number even if the record is not saved in a timely fashion.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[noelville]

Thanks Jeff, I will take you suggestion into consideration.
Any idea why the saverecord doesn't work?
Regards Alex

 

[jebry]

Well, one question, what does the save button do? In this situation, I would code it to ask if they are ready to save and, upon confirmation, I would save the record and move to a new record. Taking the user off of the current record might help the problem. If it is appropriate, you can just save the record and automatically close the form, this will surely alleviate the problem as you described it, but might frustrate the users if they need to input multiple records.

If the users absolutely need to stay on the current record after saving then we would need to look at the code being used and know more about the form, i.e., is it bound or unbound, how is it opened, can the user access other records or just add, etc.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[noelville]

Hi Jeff,
By executing the saverecord command (DoCmd.RunCommand acCmdSaveRecord), I was hoping to force the INSERT into the table to be done before The User exits the current record.

At the form open, some information is already present (data retrieved from another table). During a trace, I determined that the insert executes almost immediately but actually doesn't put the data in the table until the User exits the current record. The User would not want to exit the current record at this stage because more information must be entered. Forcing the insert would ensure this record got written into the table and assigned a unique id via following command:
Me!IntakeID = DMax("IntakeID", "Intaketbl") + 1

The form is a bound form and is opened thus:
DoCmd.OpenForm strForm, , , , acFormAdd

It's strange that the saverecord doesn't work for a data entry form but does work for a record being updated.It seems like it's a bug because everything I've come across does not state that this command does not apply to a new record.

Thanks for your help. I will simply have to change the code ... possibly execute an insert query rather than waiting for Access to insert into the table on exit.

 

[TheAceMan1]

How are ya noelville . . .

Indications are were dealing with a network here (correct me if I'm wrong). I believe the problem is how ID's are handled over the network (not closing the form). A method to lockout all users while the user with access has control until id is updated and received by the user occurs in the following link (presented for reference):

faq700-184

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[noelville]

Thanks Aceman ... I will take the suggestion under consideration.
It's an old system I do not want to change drastically.
This particular table is not using autonumber but the form is retrieving the last number and incrementing by 1.
Thanks to everyone for all their help.

 

[PHV]

In the Current event procedure of the form:

If Me.NewRecord Then
  Me!IntakeID = DMax("IntakeID", "Intaketbl") + 1
  Me.Dirty = False
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[noelville]

PHV, Thanks for this suggestion. I will give it a try.