Editing records without immediate updating

[Guest]

I would like to set up something on the order of a form that would allow
users to edit an entire record at a time, but without actually affecting the
table until they click an "Update" or "Commit" button at which time the
entire record is updated at once.

Does anyone know how I can accomplish this?

Thanks.

 

[kingston via AccessMonster.com]

Create a client-side table with the same structure as the main table.
Whenever the user selects a record via a combobox or list, copy the record(s)
into the local table. The form should be bound to the local table. Add a
button to the form to check for multi-user conflicts (e.g. via a new field
that adds a specific date and time to the record) and save the changes.

 

[Guest]

I had thought of that approach and I may defer to it. Alternatively, may I
have your opinion on the following:

The form itself would be tied to the table so the record selector would
update. As the user scrolls through the records, use VBA to return a
recordset and update the fields in the form. If the user clicks the update
button, the VBA sends the field updates back to the table using SQL UPDATE.

Does anyone think this will work well? - or would there be too much of a
performance hit?

I invite your opinions...thanks.

 

[kingston via AccessMonster.com]

If you have a lot of fields, it could require a lot of coding. Also, I'm not
sure how you would expose all the records and not perform immediate updates
without using copies.

I had thought of that approach and I may defer to it. Alternatively, may I
have your opinion on the following:

The form itself would be tied to the table so the record selector would
update. As the user scrolls through the records, use VBA to return a
recordset and update the fields in the form. If the user clicks the update
button, the VBA sends the field updates back to the table using SQL UPDATE.

Does anyone think this will work well? - or would there be too much of a
performance hit?

I invite your opinions...thanks.

Create a client-side table with the same structure as the main table.
Whenever the user selects a record via a combobox or list, copy the record(s)

[quoted text clipped - 10 lines]

 

[Guest]

Thanks for your input, and please, bear with me on this.

The record selector exposes all of the records. When the form opens, a text
box linked to the record ID populates; an ADO query retrieves that record and
populates the form.

As the user scrolls through the form, an OnCurrent event fires and triggers
the next ADO query and the form is refreshed with the next record - again
based on the ID.

If the user clicks the "Update" button, the form fields are then, and only
then, written back into the table, again based on the record ID. I only have
six fields.

I just finished coding it and it runs great! The only thing is, I only have
15 records to play with at the moment. I'm wondering if performance will wane
as the table grows to about 1000 +/- records expected at any given time?

Thanks for your further input on this...

If you have a lot of fields, it could require a lot of coding. Also, I'm not
sure how you would expose all the records and not perform immediate updates
without using copies.

I had thought of that approach and I may defer to it. Alternatively, may I
have your opinion on the following:

The form itself would be tied to the table so the record selector would
update. As the user scrolls through the records, use VBA to return a
recordset and update the fields in the form. If the user clicks the update
button, the VBA sends the field updates back to the table using SQL UPDATE.

Does anyone think this will work well? - or would there be too much of a
performance hit?

I invite your opinions...thanks.

Create a client-side table with the same structure as the main table.
Whenever the user selects a record via a combobox or list, copy the record(s)

[quoted text clipped - 10 lines]

Thanks.

 

[kingston via AccessMonster.com]

Is your query copying the record data to the form? If not, how are you
preventing the record from saving until your "Update" button is clicked?
Anyway, I don't think that you will see a performance issue since you are
using the record ID to retrieve the record. The user can only deal with one
record at a time. You just have to prevent concurrent updates for multiple
users.

Thanks for your input, and please, bear with me on this.

The record selector exposes all of the records. When the form opens, a text
box linked to the record ID populates; an ADO query retrieves that record and
populates the form.

As the user scrolls through the form, an OnCurrent event fires and triggers
the next ADO query and the form is refreshed with the next record - again
based on the ID.

If the user clicks the "Update" button, the form fields are then, and only
then, written back into the table, again based on the record ID. I only have
six fields.

I just finished coding it and it runs great! The only thing is, I only have
15 records to play with at the moment. I'm wondering if performance will wane
as the table grows to about 1000 +/- records expected at any given time?

Thanks for your further input on this...

If you have a lot of fields, it could require a lot of coding. Also, I'm not
sure how you would expose all the records and not perform immediate updates

[quoted text clipped - 18 lines]

 

[Guest]

Yes, the form is updated using a stand alone recordset, the controls, other
than the record ID, are not linked to the table.

As far as concurrency, is there a way I can lock a record based on its ID?
If so, that would fix that potential issue and I'm done.

Do you know if there is a way to programmatically set and release a record
lock?

Thanks again...

Is your query copying the record data to the form? If not, how are you
preventing the record from saving until your "Update" button is clicked?
Anyway, I don't think that you will see a performance issue since you are
using the record ID to retrieve the record. The user can only deal with one
record at a time. You just have to prevent concurrent updates for multiple
users.

Thanks for your input, and please, bear with me on this.

The record selector exposes all of the records. When the form opens, a text
box linked to the record ID populates; an ADO query retrieves that record and
populates the form.

As the user scrolls through the form, an OnCurrent event fires and triggers
the next ADO query and the form is refreshed with the next record - again
based on the ID.

If the user clicks the "Update" button, the form fields are then, and only
then, written back into the table, again based on the record ID. I only have
six fields.

I just finished coding it and it runs great! The only thing is, I only have
15 records to play with at the moment. I'm wondering if performance will wane
as the table grows to about 1000 +/- records expected at any given time?

Thanks for your further input on this...

If you have a lot of fields, it could require a lot of coding. Also, I'm not
sure how you would expose all the records and not perform immediate updates

[quoted text clipped - 18 lines]

Thanks.

 

[Albert D. Kallal]

Does anyone know how I can accomplish this?

Just place a save buttion on the form:

The code behing the button to send the forms data to the table can be:

if me.dirty = True then
me.dirty = false
end if


The above will send your forms data to the table...

 

[kingston via AccessMonster.com]

I think you may be able to use the setting for default record locking (.
RecordLocks = Edited Record). However, this is a global propety and you
cannot rely on the built-in setting in the Tools menu since each client
computer may have a different selection.

Did you try the .Dirty property as someone else suggested? I don't see how
that would allow you to prevent an uncommited transaction at the record level
(e.g. a change is made and the form is closed without clicking a "Save"
button). At least I don't see how it can be done without more supporting
code to prevent uncommited transactions, which is what you're trying to do in
the first place. Please let me know if I'm missing something here.

Yes, the form is updated using a stand alone recordset, the controls, other
than the record ID, are not linked to the table.

As far as concurrency, is there a way I can lock a record based on its ID?
If so, that would fix that potential issue and I'm done.

Do you know if there is a way to programmatically set and release a record
lock?

Thanks again...

Is your query copying the record data to the form? If not, how are you
preventing the record from saving until your "Update" button is clicked?

[quoted text clipped - 28 lines]