Allowing a field to be changed one time

[kd2017]

See attached for my solution to your problem. I used table data macros to check for changes. DaveDinger.accdb

This will check for changes before a record is updated. If you need it to check after the user physically leaves each text box than you can simply set Me.Dirty=False in the appropriate events of each relevant control in your form to force updates and the data macros to execute.

[davedinger]

Ok so let me explain a bit what I'm doing there are only 3 tables. one for users, one for equipment info, and one for the recording of readings.

First I have a table that has all of the equipment. its named thermometer info the data here will never change, basically its an inventory and static data points used for reference. it will probably be 200 records or so of the equipment they have on hand.

Two times a day they need to record temperatures of all thermometers that are in use. AKA not out of service. they need to have all of the records in service show in a list format so that they can go straight down the row and see which have records completed and which are not. so to make the Batch list I run a append query on the Equipment table looking for all active thermostats. I then send those to a table named Readings, and I update those to all have a common batch name.

The readings table stores all the readings and associated data and allows me to create a continuous form that shows all of the data points needed with just the records required for that batch. this allows the users to go down the continous form like a spreadsheet and enter the readings of each thermostat one after another and they can see what needs to be done and what needs to be read yet. The form Name is Readings. this is the form I'm working on all fields except the reading fields are locked. so that the users can enter the data they need and then when data is entered it locks the field so that they cant back up later and change a reading since the this is all time sensitive.

does that make sense?

I have a safe copy of the database that I can provide but I'm not sure how to make it available. its 3.8 MB

Below is the Relationships view though




Thanks

[moke123]

Your naming convention is going to be a P.I.T.A. to code due to the spaces in names. You'll need to bracket everything.
You may also want to re-think you table structure as having repeating fields like 1st reading, 2nd reading, etc. usually indicates a non - normalized structure.

[isladogs]

@DaveD
1. To reduce file size:

Remove all except a small number of records in each table.
Remove all database objects not needed for the question you are asking.
Compact & zip then upload

2. Also its not always clear who you are answering in your replies e.g.

Sorry about posting this to another forum, but I just didn't understand what you were saying,

at this time I think your solution is the closest to what I need, Can you please help?

Suggest you preface with the respondent's name as I did above

[kd2017]

Did you see my posted #16 where I gave an example on how to lock fields?

Your reading adjustments, does adjustment 1 correspond to reading 1, adjustment 2 to reading 2, etc.? Or are the adjustment numbers independent of the reading numbers?
I see you're duplicating all the data in the thermometer table's fields into the readings table. You shouldn't need to do that. I understand you needed a flattened view of all the data but a query will do that for you, you don't need to duplicate data.

[davedinger]

Thanks Ridder, I compressed the file and Hopefully the attachment works

to see the field im working on
the log on screen should open first
choose a technician and click log on
next choose a batch from the drop down list
Click Show Existing Batch
Im working on the 1st reading field
This is the field that Im trying to get to lock after something is entered in the field and it loose focus.



Thermometer Log 1-8 in progress.zip

---------
@KD2017
I saw your solution and to be honest I have never heard of a data Macro before.. It took me a while to figure out how to view it but i finally figured out how to view the macro, but it didnt make much sense to me, and im a bit concerned about putting something like this in if Im not familiar with it.

I think you also asked me about the 1st reading / 1st time / 1st technician fields. what happens is when a value is placed in the 1st reading field a macro gets the technician name and adds a time stamp to those fields. the same happens to the 2nd 3rd and 4th readings.

[isladogs]

I've only had a quick look at this but from what I've seen, I recommend a significant rewrite
Apologies in advance for sounding critical

As its a continuous form you are seeing multiple copies of the same controls
So if you lock the '1st reading' control it is locked for ALL records

I'm unclear why you're getting errors about being unable to find controls.
Possibly related to your macro on the OnChange event - recommend you only use VBA
However a MAJOR issue is that Access doesn't like spaces in field or control names though [] bracketing fixes that
It also doesn't work well where fields & controls start with a number
So recommend you replace '1st Reading' with Reading1 etc for ALL other objects

Furthermore, your table structure isn't normalised.
You should NEVER have tables with e.g. 1st Technician, 1st Reading, 1st Time of Reading , 1st Lock repeated for 2nd, 3rd, 4th giving 16 fields
Instead you should have 5 fields Technician, Reading, ReadingDateTime , LockValue AND ReadingNumber (with values 1,2,3 or 4)
This gives 4 records but with fewer fields.
There are similar issues in the Adjustment table

If you persevere with this structure EVERY step will be difficult.
Changing to a normalised structure will take time but after you do so EVERY step will be easier & faster to do ...so the time spent will replay itself many times over

Suggest you research normalisation before you go any further with this

EDIT
There is an excellent series of articles about normalisation and related topics starting with http://rogersaccessblog.blogspot.com...on-part-i.html

[ArviLaanemets]

Another reason not have fields like e.g. Entry1, Entry2, Entry3 (either which one of them filled at time, or several of them having entries in same row) in your table is, that maybe after some time you'll need Entry4 in your table. And adding this field means, that with high probability you have to redesign most of your forms, reports, queries, procedures etc. And maybe after some time you have to repeat this process again and again,

Instead:
a) with only one Entry value per record you need 2 fields - e.g. EntryType, Entry - then you can always add new entry types. You have to remember to design e.g. reports using grouping by EntryType fieldi instead coding fixed type values in.
b) with several Entry values per record you need separate entries table with foreign key linked to mother table, and having a single Entry field in this entries table instead of mother table.

[moke123]

It also doesn't work well where fields & controls start with a number

I recall reading something about this years ago and have searched numerous times to try and find the article again to no avail.
As such I never use a leading number but wish I could find that article as you see people do this all the time in posts.

[davedinger]

Found a solution!! well at least a work around.
I create a locked field and had it default to no.
Then I Macro-ed the crap out of this thing.
I set the macro to run on dirty
its a simple if then macro.
if the field lock is "N" then
it setvalues for the timestamp, the Technician, and changes the
lock field to Y The next the field is changed time it takes the false loop,
which is if locked open a message box saying data is locked then back space to remove the entry that triggered the event and the escape key cleans it all up..

[isladogs]

Are you sure that doesn't alter all records in your continuous form?
See my previous answer.