Read Only records in Access

[EddiRae]

I have records that in certain circumstances, I don't want the user to have access to update them. I also want the background color to be changed for these records.

In Access, how can I accomplish this? I was thinking of the datagrid, would this work? If so, I was wanting a bit more information on how to use the datagrid in MS Access. Where would be a good place to look?

 

[Carl Foster]

Hello Eddi

You can change the background colour property and locked property of control programatically using VBA. Something like:

With Me!txtControl
           .BackColor = Gray
           .Locked = True

End With

Access does not have a datagrid control like VB but you can have continuous forms and DataSheets

 

[Tim L]

Welcome to the forums!

I have records that in certain circumstances, I don't want the user to have access to update them. I also want the background color to be changed for these records.

In Access, how can I accomplish this?

If you don't want the users to even see these records you should use a query to filter out the records that the user should not be able to see.

If you still want them to be able to see the records then the easiest method of preventing records from being edited is to set the Allow Edit tab in the Form\Data property to No. I'm guessing that your certain circumstances might also extend to deletion; setting Allow Edits to No won't prevent deletion of the record, for that you'd need to set the Allow Delete property to No too.

Both of the above work for all records in a Forms Record Source. As you only want to prevent the editing of records 'in certain circumstances' you will need to be able to identify which these records are; a field 'editable' of the Yes/No variety would suffice (added to the underlying table), if you want to control deletion seperately then you'd need an additional field for that.

To prevent the editing (to 'lock' the record) you would then put code in the On Current event of the form to test the value of your 'editable' field and, if it is 'No' then either:

1. Change the Allow Edit property to No, or
2. Set the Locked property of the controls on the form to Yes.

Don't forget that the code in the On Current event should reset the 'locks' above if the 'editable' field is set to Yes.

With regards to changing the backgrounds of the controls, changing the Locked property of a control to Yes will do that automatically. Unfortunately changing the Allow Edit property to No does not change the background colour of the controls, for this you would have to go through all of the controls that you need to change. Search the forum for 'For each ctl' for solutions on how to iterate through all of the controls on a form. Becareful with this method though, because if you use your own buttons to control navigation, or exiting from the form, then you may end up affecting them too, it may be better to go through each control manually (if you set up a sub routine to carry out the change, according to the status of the editable field, then you could use the same routine to both disable and enable the fields, or to change the background colour, as required).

I was thinking of the datagrid, would this work? If so, I was wanting a bit more information on how to use the datagrid in MS Access. Where would be a good place to look?

I'm afraid that I am not familiar with 'datagrid', and the Access help file doesn't return anything, so I cannot comment. A search in the forums shows that there are a quite a few results, mostly related to VB, VB.Net and ASP/ASP.Net (i.e: the programming forums rather than the Forms forum), perhaps you should have a look at some of them for this part of your question?

HTH

Tim

 

[EddiRae]

What you suggested, is that for ALL records in the detail section, or just the specific records that I want to change?

I had tried that, but it changed ALL the records

 

[missinglinq]

What is the criteria for these records that you want to be read-only?

 

[EddiRae]

I have the detail in continuos forms. Whenever the date is in the past, then only that record should be uneditable and highlighted.

 

[missinglinq]

So if the record's datefield is is prior to the current date, you want the record non-editable and with a changed color?

With a Continuous Form you're going to have to use a combination of Conditional Formatting (to change the background color of your textboxes) and code in your Form_Current sub to control whether a record can be edited.

In Design View, hold down the <Shift> and click on each textbox whose color you want changed. Goto Format - Conditional Format. You'll see a dropdown box marked Condition1. Use the arrow and select "Expression Is." In the box to the right enter [YourDate] < Date() where YourDate is the actual name of your date field. Now Use the buttons to the right of this to set the colors you want your textboxes to have if the date is prior to the current date. Click OK.

Now go into the code editor and enter this code:

Private Sub Form_Current()
If Me.YourDate < Date Then
 Me.AllowDeletions = False
 Me.AllowEdits = False
Else
 Me.AllowDeletions = True
 Me.AllowEdits = True
End If
End Sub

This will keep users from editing or deleting records whose dates are prior to the current date. If you want to allow these records to be deleted, simply omit the lines Me.AllowDeletions = False and Me.AllowDeletions = True.

Good Luck!

Linq ;0)>

 

[EddiRae]

Thanks!!! That helped!!

missinglinq,
I got it to work!! What I needed to do is not shade in specific fields, I needed to shade the entire background. What I did to accomplish this is to have a textbox which was behind ALL the fields on the continuous form. I then created the conditional formatting on the textbox so that if the date of the field was less than current date/time, then it would be yellow, else it would be normal.

I also had to create an event for when it got focus to change the focus to the first valid field of the form.

I did use the sub for form_current to make the field uneditable.

Thank you so much for your help!!

Eddi Rae

 

[missinglinq]

Glad you got it to work for you!

Linq