Allowing edits on only uncomplete records

[Angelique1982]

Hi

I was wandering if some one could help.

I have developed a database in MS Access that records testing information. The problem I have come across is that some staff are overwritting previous test information instead of adding a new record (no matter how many times it is explained to them they are still doing it.)

I want them to be able to still view all the tests sat but only edit those records that the results are still pending (so they can be updated when the results are received). Is there an easy way of doind this?

My VBA skills are practically non existant so have not found an easy way to do this, although I can edit some parametres in code to suit my setup.

The OS used is Windows XP and Access versions accessing the database range from xp, 2003 and 2007 (it was developed in 2007 if this helps)

Any help would be greatly appreciated

Thanks

[Lysander]

Hi

I was wandering if some one could help.

I have developed a database in MS Access that records testing information. The problem I have come across is that some staff are overwritting previous test information instead of adding a new record (no matter how many times it is explained to them they are still doing it.)

I want them to be able to still view all the tests sat but only edit those records that the results are still pending (so they can be updated when the results are received). Is there an easy way of doind this?

My VBA skills are practically non existant so have not found an easy way to do this, although I can edit some parametres in code to suit my setup.

The OS used is Windows XP and Access versions accessing the database range from xp, 2003 and 2007 (it was developed in 2007 if this helps)

Any help would be greatly appreciated

Thanks

I have the same situation in a suite of databases I am developing, where you want the user to be able to add new records, edit some records, and leave others alone. There are sophisticated ways to solve this, using lots of code and the Access security engine, but a simpler way would be to add a field to each of your relevant tables called RecordComplete. Make this a yes/no required field, with a default of No.

Lets assume we are talking about 1 table only. This would need repeating for each table.

Create a form to display the data, allow edits, deletions, add new record.
Put RecordComplete on this form as an invisible control

You would have to write some code in the Before Update Event of the form that would validate the data entered and set RecordComplete to Yes if it is complete.

Then, for this form, instead of using the table as the record source, create a query that only selects records that are NOT complete.

This is your edit form, say frmMyTableEdit

Save a copy of the form as frmMyTableView and change the properties to no edit, no additions, no deletions and create a new query that selects records that are complete.

You now have two forms, one to edit, add, delete records, and a second to view completed records.

This can all be done on one form, changing properties at each current record but requires a substantial amount of code.

Hope this helps

[missinglinq]

What kind of controls are we talking about here that need to be filled in for a record to be complete? Text boxes, check boxes, comboboxes?

Welcome to TheScripts!

Linq ;0)>

[Angelique1982]

What kind of controls are we talking about here that need to be filled in for a record to be complete? Text boxes, check boxes, comboboxes?

Welcome to TheScripts!

Linq ;0)>

There are a combination of text boxes and combo boxes. The result is in the form of a combo box that only has pass, fail and pending as the options. If the result is in as a pass or fail they should not be changing anything on the record.

Everything else that needs entered is a required field on the table so they get filled in anyway, so te only thing that would need to be edited is the result field.

Hope that made sense, been eating and sleeping this database for far too long now!

[missinglinq]

Still a little fuzzy, but let's call the control that holds the Fail/Pass/Pending status, well, Status! Then something like this should do:

Private Sub Form_Current()
 If Status = "pending" Then
   Me.AllowEdits = True
 Else
   Me.AllowEdits = False
 End If
End Sub 

Linq ;0)>