Controlling Row/Field updates by field value

[GregoryWest]

Here is the problem. I have a table, it is an on going update for a meter reading. When the last record for a particular meter is closed, a new record is created with updated information and carry forward numbers. Something like this:
Meter# - Opening - Closing - Active - Date
123 - 1776 - 1876 - No - 2014-Jul-04
123 - 1876 - 1976 - No - 2015-Jul-04
123 - 1976 - 0 - Yes - 2016-Jul-04

This is in a table form for the user to see the old data, and be able to edit the last line only. My problem is how to I tell access that that can edit only the last row? Since there are 1,000's of meters involved this is not going to be the last line of the datatable nor is it a new record. The trick is the use can edit only rows where the Active field is True. Once set to false the record should not be changed any more.

Any ideas?

 

[plog]

I would use unbound inputs. Here's what I envision:

Search Form - User chooses what meter to work with. Clicks button and it takes them to Meter Form showing just that meters data.

Meter Form - Top part shows meter data (number, name, date started, etc.) along with unbound inputs for all the data they can change in your MeterReading table.

Meter SubForm - bottom of Meter Form, shows all records from MeterReading table, but is uneditable. Only used for user reference.

Now here's how that unbound section of the Meter Form works. OnLoad it puts all the data from the last record into those unbound inputs. User can make changes to the data and then click a 'Submit' button next to them. When they do that the data is saved to the table and the subform at the bottom refreshes to show the new data.

 

[CJ_London]

I'm surprised you want the users to search through 1000's of records to find one to edit - why not filter them on Active=yes or perhaps closing=0 plus filtering on the meter number would reduce the visible records to just one.

Also, you just need the opening position - the closing position is the opening position of the next record - or the other way round, just the closing position and the opening position is the closing position of the previous record

but to answer your question, in the form current event put the following code

me.allowedits=active

 

[GregoryWest]

London, think I get where you are going. No the user does not have to search 1,000's of meters. They are only shown the records for the meter in question. The need to see the record that are updating, but they also need to see all the previous records which they are not allowed to change.

If some cases this could be 240 fields of locked data, and 10 fields the user can change. Does your example allow for only changing of the records where Active is true? So you put this in the form, or in the table itself?

 

[CJ_London]

Does your example allow for only changing of the records where Active is true? Yes - once Active is ticked and the record saved it cannot be changed (at least not from this form)

So you put this in the form, or in the table itself?

as stated in my post - the form current event

If you want only certain fields to be editable then you need to set each control which you do not want changed so that it is disabled and/or locked

If you need to do this then in each control which the user can edit put in the tag property something like 'CanEdit'

then in the form current event put something like

 dim ctrl as control
 for each ctrl in me.controls
     if ctrl.controltype=actextbox or ctrl.controltype=acheckbox then ctrl.enabled=ctrl.tag="CanEdit"
 next ctrl

Note I have not tested this code - you may have other control types which need to be editable

 

[GregoryWest]

AH I get it, yes all the fields are editable, thank god. Looking at the properties of the form I can find the property "On Current" I am assuming that is what you mean. In there I have Macro Builder, Expression Builder, Code Builder. I am guessing I need to write a quick function to to the me.allowedit=active. This sound about right?

 

[CJ_London]

select the code builder - the vba window will open up with the Private Sub Form Current () already created - just put the code on the line between there and End Sub

 

[GregoryWest]

Works awsome, Thanks CJ, just what I needed!!!!

 

[CJ_London]

happy to help!