Handling multi record input in Data Entry only form

[Kemidan2014]

I ran into a little kerfufle that required me to have to correct a record in a table in the Back end

I have Form that is set up for Data Entry only for keying in New Records to a table. When you open then form the 1st field, through macros, will produce a serial number.

What happened was:

I had opened the form to create a new record in my copy of the Secured front end. This form has a button to export the information to an excel file to in a bulletin style. while i was finishing up the excel file. my co worker went in while the form on thier copy of the secured front end while the form was still open on my side and started a new record as well

however it started thier form with the same serial number as what i had used. Thus when they went to create the excel file it correctly errored saying the serial for the excel existed and exited the macro.

by doing so also overwrote all the information i have keyed into on that line in the table.

How can i safe gaurd this from happening again? is there a property i need to change in the form itself or do i need to add VBA?

 

[Micron]

If you can, the best approach IMO is to generate a number at the end of the process, not at the beginning. When record is committed, generate number and lock the form so that the number is at least visible but nothing is editable. If you must know the number at the beginning you may need a different approach and that would also have to consider whether or not the number has to be consecutive or if gaps are allowed.

The most aggressive solution would be to lock the whole table but that would prevent anyone else from creating anything as long as a lock was on the records.

 

[Kemidan2014]

That number is used as saving the excels file name as well as putting that number in a cell in the file itself to identify the bulletin number. although all of that happens when you click the "create alert" button on the access form. so i am sure i could definately change the order in which it determines this number.

is there a property i need to consider changing so that the table doesnt update with data until i dictate it? i am wondering if i am neglecting a very fundemental step that i missed in training heh. or maybe i am not understanding when the data in the form truly appends to the table?

 

[Micron]

As long as we're talking about an Access form, then the time to generate the number would be in the form BeforeUpdate event where you'd set a control value to be that number. I don't really understand how things are handled at your end (like how the worksheet value figures into this creation, if at all) so mostly I'm guessing. Perhaps in that event, call a function that looks at the the last number generated and stored in your Access table (at least I hope that is the case), then calculate the next number and the function returns that number to the BeforeUpdate event. When the field is populated by that number in that event, it will get saved in the new table record.

maybe i am not understanding when the data in the form truly appends to the table

The behaviour is such that a new record (one that is being created) may show values in fields but they do not exist yet - not until the record is saved. That happens as a result of several actions
- leaving the record by choosing/starting a new one (it ceases to be the current record)
- manually saving the form
- user action that causes code to save the record
- can also happen by clicking on a main form control when a subform record was not yet saved
For more info see the following

Order of events for database objects - Microsoft Support

support.microsoft.com

 

[Kemidan2014]

yes I believe we are both talking about the access form. the excel file was just an output

So the code i have in the access form is using Form_Current() i have a macro that also uses a Function in a seperate module to look at the Table and break down the previous ID number and add 1 to and reconstruct the new number to also include current year.

this code uses "Me.NewRecord = True then ...." as the first line

So when this Create alert form (Access) is opened i get 8 blank feilds save the one on top the ID number which has the new alert number that is also locked for editing.

The button on the access form to create the alert using the fields in the access form performs simple open excel, .Range(Cell) = me.<feildname> to place the info into that sheet then saves the file with the alert number.

other than that i dont really have any other form controls besides a "close form" button and the "create alert" button. so i am assuming the table updates in the back end when the user hits close form button? could this be the novice mistake I have rendered to cause the error stated in my original post?

 

[Micron]

so i am assuming the table updates in the back end when the user hits close form button?

Unless one of the other causes I listed has not already happened. Thanks for reminding me that I forgot to add form closing to that list. This reminded me of another approach you can take - your table field should be indexed, no dupes. Then Access will not allow a user to save the form with a number that was already generated.
That should cover what you experienced -
userA opens form, number 123 is generated. userA goes for lunch.
userB opens form, number 123 is generated, record is saved.
userA returns to desk, finishes record, attempts to save, Access complains and stops the save. Except what now? Cancel that record and start again? In that complaint, Access unhides a button that allows you to generate a new number, which you then manually over-write in the form field and hope you're good? IMO generating such numbers is best left to the end unless you just can't.

EDIT - your index can be a compound index (2 or more table fields comprise the index). In such cases, what's allowed:
A 1
A 2
A 3
B 1
B 2
but not B1 or B2 again. Note that multiple A's, B's are allowed as well as multiple numbers but not multiples of the same combinations.

 

[Kemidan2014]

Alert number field properties in the table. but what you described happened to me after my coworker went in and made their alert. when I exited it complained about not being able to update the table. and the table had all of their information in it. so, I exited, corrected the table, asked them to redo their alert and updated correctly.

I really apologize the gears in my brain are skipping teeth and I'm not seeing the answer. but if i start a new record can i not have it update the table on new record so then that line item is there saved in the table even if it is all blanks with just alert number? then when they fill and close it will complete the rest of the record? there has got to be a native setting or control in access for this because it seems like such a fundamental thing to want to avoid.

Edit: actually, should i just consider Record locking?

 

[Micron]

if i start a new record can i not have it update the table on new record so then that line item is there saved in the table even if it is all blanks with just alert number?

I think I covered that in post 2 where I said

If you must know the number at the beginning you may need a different approach and that would also have to consider whether or not the number has to be consecutive or if gaps are allowed.

You can create the number at the beginning. If you save that record but decide not to complete it you'll have an empty record save for the record id and that number. Next record uses next number. If you delete the empty record to prevent it appearing in queries/reports/forms you have non consecutive values. Up to you to determine if either of those results are an issue. If you save at the outset thus create a number I think I would disable that form field upon save.

If by consider record locking you mean the whole table, no one else will be able to create a record as long as you've got a record started or in edit mode on that table. That would likely be frustrating for users.
HTH

 

[Kemidan2014]

I added a "Me.Refresh" at the end of the code that creates the number. this commits the alert number to the table record before the user starts filling in data. this will allow the user to for example start it, goto lunch. then come back.

I tested it with other coworkers and they increment correctly while its open. even before this i anticipated blank records from happening but we will just resolve it by adding a close date in a query i use to reconcile open alerts.

 

[Micron]

Refresh updates changes to the current recordset so if someone else creates a record while you still have yours in create mode (i.e. it hasn't been saved yet) IF any form control provides access to the list of created numbers, new ones will not appear in that control until you do something to cause the form to requery. That won't happen if you just stay on that record and look at the list values in that control. If that can never happen, you should be OK, otherwise use Me.Requery.

Refresh or requery data - Microsoft Support

When multiple users have access to your Microsoft Access database, the requery method shows you the latest data by refreshing data in the Recordset.

support.microsoft.com

If a datasheet or continuous form is involved, the first record will likely become the current record, which can cause you to lose your place. There is a fix for that.
It might be worth your time if you go to the parent page for Office and browse the object model for whichever products you want to get help from. I'm there all the time!

Office Visual Basic for Applications (VBA) reference

This reference is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

learn.microsoft.com