Updating multiple records on a screen

[exaccess]

I have a screen with multiple records from a database. At the top of the screen I have a button that lets the user change the mode between read only and edit modes. The current mode is displayed near the button. When the user decides to update a record or records he goes into the edit mode and starts changing the records. The user may change any number of records on the screen. When editing is finished he presses a button called save and close gets feedback and leaves the screen. Now in this process how do I keep track of the records that are changed on the screen . Plus he can hit the page down key and go to the next page and make changes there also. Is it possible to stop the hitting of the page down key. What indicators can I use to spot the records changed?
Help is appreciated.

 

[CJ_London]

if you can see multiple records on the screen then presumably you are using a datasheet or continuous form.

If this is the case, the record is automatically updated when the user moves from one row to another or to another page.

So not sure what you are trying to achieve.

 

[exaccess]

Yes indeed. I am using a continuous form. I wish to differentiate between what the user sees on screen and what is actually committed to the database. This is why I am implementing an edit mode. It is possible for the user to quit without committing the changes or with committing the changes. But I do not exactly know how to go about this solution. I have done it already with single form. But this time I have to know which records are updated on the screen and commit them. This is my question. I wish to benefit from the experience of the users of this forum.

 

[CJ_London]

My point is that as soon as you leave the row in the continuous form, the record in the back table will be updated to reflect the changes - of course you can put in some code in the form before update event as you would for a single form to confirm the update but this would be triggered when you move of the row. This is why

It is possible for the user to quit without committing the changes or with committing the changes

is not possible in a 'normal' setup

The only way you could have multiple records, all in 'edit' mode at the same time, is to take a copy of the records you are looking at and store them in a dao recordset object which you can then pass to the form for it to work on.

You will then need some routines which will compare the dao recordset with your original (table based) recordset to determine what has changed.

It is not an area I have had much need to investigate so the best I can do is suggest in your form you remove the recordsource (making a not of the sql) and in the open event you put the following (not tested) pseudocode:

dim sqlstr as string
dim rst as dao.recordset
 
sqlstr=myoldrecordsourcesql
set rst=currentdb.openrecordset(sqlstr)
me.recordset=rst
 
set rst=nothing

However you would be better to start a new thread once you have the above working with a more focused thread title.

 

[nanscombe]

Normally the only way for a user to change their mind and avoid saving changes to the edited record is to press the <escape> key twice otherwise any changes will be changed automatically.

Do you actually want a user to make changes to multiple records but be allowed to throw away any changes?

If you really do want to do this then you could be straying into the realms of Transaction processing and things could start to get rather complicated.

 

[exaccess]

Recognizing that it is getting too complicated to be handled by Access I give up the idea of undoing multiple records. At the top of the screen I have a mode button which toggles between read only and edit modes. On the continuous form itself the user makes multiple changes in different records. Then there is a global SAVE button at the top which saves everything and closes the form. Now my question is how can I make the current record on which the user is working the first record on the screen when I make the form current or repaint the form.

 

[nanscombe]

If you are using a continuous form, connected to a table or query, the changes to the amended record will normally be saved automatically as soon as the user leaves it to go to another record.

 

[CJ_London]

To start with you need a basis to define the current record in a way in which you can have it appear at the top by filtering and/or sorting the recordsource for example which I suspect you can't do.

You seem to be making this overly complicated so I'll make an alternative suggestion.

Have two subform controls, one which is set to edits not allowed and the other set to edits allowed and is just one row deep. When the user selects a record from the not allowed subform, then clicks the edit button, the other subform is then populated with that record.

With proper design you can position the two subforms so they appear as one.

 

[exaccess]

To start with you need a basis to define the current record in a way in which you can have it appear at the top by filtering and/or sorting the recordsource for example which I suspect you can't do.

Do you mean that the query which serves as the recordsource for the continuous form to be redefined on the fly so that the current record becomes the first record on the newly displayed screen?

 

[CJ_London]

no you would need to filter or sort, but I really think you will waste a lot of time trying to get this to work that way. I recommend you look at my suggestion.

both subforms have the same recordsource so you just need to refilter the edit subform when the user clicks the edit button

e.g.

recordsource for both subforms is

SELECT ID, Firstname, Lastname from myTable

when the user clicks the edit button you run the following code in your button click event

editsubform.form.filter="[ID]=" & continuoussubform.form.ID
editsubform.form.filteron=true

you may need a bit more code to protect against the user clicking the button if they haven't selected a record in the continuous subform

 

[exaccess]

Your second idea is plausible. Some questions: Suppose we are in read mode, the user goes to a record in the middle of the screen and wants to update the record and the following 5 records. He hits the mode button at the top of the screen I display the second form which is one row deep. So he sees the editable record in place of the record to update. In such a case do I simply open up the editable record on top of the continuous form? Do they both stay on the screen displayed? Then what happens when the user goes to the next record? The only way to detect this move is to watch where the cursor goes because he does not hit the mode button again it is not necessary. If I can get answers to these questions Then I can go ahead implement the proposed solution.

 

[CJ_London]

By on top I meant at the top, not over the top.

I am struggling to understand what you are trying to achieve

 

[jdraw]

What exactly are you trying to do - in plain English?

If you can identify a set of records to be updated, why not create a recordset of those records and apply the common updates for that set.

If you have custom updates for each record in a set, the I don't see how you could do that en masse. But you could identify each record, select and update each individually.

I really don't think a continuous form is helpful for this.

Anyway, perhaps when we understand your intent, things will be clearer.

 

[nanscombe]

It's difficult to help further without knowing what you actually have.

Anyhow, just out of interest here's one way I would tackle the idea of having lots of records and only editing a single record at a time.

In the example, form frmMaintainPeople_v1, I have used a Listbox to display multiple records, with enough data to identify them, with an edit form beside it.

As one clicks on an item in the list the details of the corresponding record shown in the edit form. There is an edit Toggle button which enables / disables the fields and a new record button which goes to a new record and enables the fields for data entry.

Feel free to have a nose around and see if it gives you any ideas.

 

[exaccess]

Well Gentlemen, I managed to do what I wanted. Here is an explanation. I have multiple records on the screen with every record occupying only one row. This is a list of members of a club. For every member we have 8 fields. 3 of them as identifiers such as card number, first name and last name. 5 remaining fields are financial data such as how much they paid, when, how and comments. The idea is to have all the members visible in one continuous form for easy comparison. But of course the list is long about 1000 members. Thus the user can be updating row number 700 and he goes for coffee or he hits the mode button to see clearly what he has done. The system has to display the screen as he left it. Normally when you refresh or repaint or reload or reopen the form it defaults back to current record 1. This was the main problem I was dealing with. Now I have stored the current record number in a global variable and reused it.
Dim F As Form
Set F = Screen.ActiveForm
G_CurRec = F.CurrentRecord

DoCmd.GoToRecord acDataForm, F.Name, acGoTo, G_CurRec
Now it always comes to the point where the user leaves it.