Access 2010 - Unused Records on Sharepoint

[ShelterSteve]

Hello,

Hope this question isn't too vague or confusing, it's a novice question but I'll try to capture everything.

My group is running an Access application where the data resides on Sharepoint. Multiple users are in it simultaneously every day.

The form being used is a bound form. Because I inherited this and there are extensive data fields, making an unbound form at this point is not feasible without a lot of extra work and time.

So when each user goes in, some of the data fields populate. I'm assuming that they lock or reserve the current record that the current user is atached to. It makes sense so they don't all write to the same record.

The user clicks a button when they complete the form and the acNewRec command is executed. Once complete they are assigned a new record (I assume the next free one).

But of course when they "X" out of the application the record they are currently in gets updated with whatever is in the form.

I've tried Undo and so forth, but is there any way to release that record back?

Basically it causes "gaps" in the data, where several legitimate records are followed by one or more blank (or minimally populated) records.

Compact doesn't seem to do anything, but I may be misunderstanding what Compact does.

Thank you!

[Seth Schrock]

Let me make sure that I understand what you are saying that the problem is. I think that you are saying that new records are getting created when they close out of the program and you want this to stop happening.

When you say that the users are assigned a new record, are these records that are already entered into the database that they are just editing, or are these records somewhere else and the users have to enter them in the database?

[ShelterSteve]

When a person opens the application or after Adding a Record they "sit" in the next available record on the file. If the next record is number 5, it is "reserved" for them and they dirty it. And if another person uses the app then they may get record number 6 so they don't conflict with the person at record 5. This is how I interpret it.

Now if the person at record 6 adds a record, they may go on to record 7 even if the person at record 5 hasn't done anything at this point.

And so on. So if the person at record 5 never adds the record (i.e. never executes the acNewRec command) and closes out the application, record number 5 is still out there and blank, or mostly blank.

What I'm wondering if there is a way to "reclaim" record 5 or even delete it. What's happening is I'm getting a series of records with legitimate data. But every so often is an empty record.

So if I have 100 records, 10 may not saved and be junky, I really want to have 90 records when all is said and done.

Hope this makes sense. Thanks!!

[Seth Schrock]

Do you have some code in place that assigns these numbers? By default, no record is created until data is entered. For example, I can have three users go to a new record. No matter what order the users go to a new record, the first person to start typing gets record one. If the other two users haven't starting typing when the first user goes to a new record and starts typing, then the first users also gets record 2. There is no saving of a record number for a person. If you have code that changes fields values when you either change records or go to a new record, then this would trigger the start of a new record. If this is the case, then you could just set default values for fields as these don't trigger a new record when you go to a new record. Or you can create an Undo button that undoes the changes to the records and then closes the form. This would use up a value of the primary key (assuming auto number field), but the record wouldn't be there.

[ShelterSteve]

I see where you're going with that. I'll try to create an Undo and see if that resolves that. Thank you!