single record manipulation

Hsibbs

Board Regular
Joined
May 6, 2005
Messages
85
I have a table with over 2000 records, the user enters and updates on this table via a form. At some stage, when the record is fully paid or no longer current, I would like a button on the form to 'disable' this record, ie grey it out so the user know it is 'done'. they should also be ab;e to 'turn it back on', just on case..............

Any ideas on how to do this?????
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Here is one solution.

Say you want the rows in the range A3:E100 greyed out IF the corresponding cell in column F of that row has some symbol, say Paid.

Then, goto the first cell of the range, A3, and highlight the range A3:E100. Next, click on Format, Conditional Formatting..., and select Formula Is:, and enter, =F3="Paid" (make SURE there are no $ signs left!), then select Format, Patterns, and choose the light-grey, or any other, pattern, click OK, OK. Done!

Let us know if this does it for you.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
RalphA... that might be how you do it in Excel... but sounds like user wants to know how to apply this to an Access form.

In Access: In the On Open Event for the form, you need to add code to check a specific field... or more than one field... to identify if the record currently being accessed is in the condition you want it to be ... to lock it. (Fully paid or no longer current... I don't know how you identify that.)

If this condition is met, you would loop through all the fields on the form and set the Enabled and/or Locked properties appropriately. Setting Locked to Yes will gray out the field... as you requested. Setting Enabled to No will prevent the user from updating that particular field but won't gray out the field. You could also set the Allow Edits property to NO as this should prevent edits at the form level. If you do this, you may have to set Allow Deletes to NO also.

My preference would be to set the form property - Allow Edits to NO ... if my conditions were met... less code to write. I would then maybe set the Back Color of the form so that the user would immediately know that this record does not allow Edits.

To allow the user to make changes, you would add another button... call it Edit... when the user clicks on this button, it would 1) change the form's Allow Edits property to YES and change the Back Color to the previous color. I might even hide the Edit button, displaying it ONLY when the Allow Edits property is set to YES.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
MyBoo:

I'm sorry, but, since this is an Excel forum, I assumed the problem was in Excel. Perhaps the original poster should post in the Access forum?
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

RalphA,
Something has gone arye in the universe. For MyBoo and me, this thread is in the Access Forum. I have double checked, and it is still the Access forum.
:oops:

MyBoo:
The Enable property, when set to No is what will grey out a field, and the Locked property when set to Yes will not allow the field to be updated. When Locked = Yes, the field is available for selecting, but can not be changed. Enable = No and Locked = Yes leaves the field looking like it can be edited (not greyed out), but it can not be selected or changed.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Hsibbs:

Vic has the settings correct for Enabled and Locked. You sometimes have to play with the two settings to identify the settings you want to use. They work with each other providing multiple settings.

Sorry for the confusion.
 

Hsibbs

Board Regular
Joined
May 6, 2005
Messages
85

ADVERTISEMENT

Thanks guys for the replies, and apologies for my late try out of your responses. The greying out / enabling / locking option doesn't work cos it locks ALL the fields on the form, not just the one field on the one applicable record.

So, I tried
On open

If me![Outstanding]=0
then me![Outstanding] = locked
and me![Outstanding] backcolor = grey

I know that is not the correct syntax but you get my drift - ;0)

like I say this locks ALL the [Outstanding] fields on the form - 2000 records, not just the one i'm working on?!?!?!

what am I doing wrong??

Thnx 4 any replies
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
You need to put this locking code into the OnCurrent event for your form. That way, the Outstanding field will be locked, or unlocked according to the record you are working on, rather than just some record controlling the entire form.
 

Hsibbs

Board Regular
Joined
May 6, 2005
Messages
85
this code, an event procedure on 'On Current'

Private Sub Form_Current()
If Me![OS] = 0 Then
Me![OS].Locked = True
End If
End Sub

locks every OS field, not just the ones that are zero.
I only want the OS field IN THIS RECORD if it is zero to be locked. If there is any other amount in this field I want it unlocked.

Is it possible
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Hsibbs
Change your code to add these two lines.
Code:
ELSE
Me![OS].Locked = False
 

Forum statistics

Threads
1,136,263
Messages
5,674,710
Members
419,521
Latest member
Jasonnie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top