By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

refering to a field in a 'multiple items' form

reginaldmerritt
100+
P: 201
i wish to change the backcolor of a field on a current record being dispalyed in a 'multiple items' form. However when using me.fieldname01.backcolor = makes refrence to all fields called fieldname01 on the 'multiple items' form and therefore all fields change colour not just the current record.

How do i refer to the current record with field named fieldname01?

Thanks :)
Dec 14 '07 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, Reginald.

I guess you mean Datasheet or Continuous forms view.
You may use conditional formatting to achieve this.
  • a special conditional format type is "control has focus"
  • you may use additional bound checkbox control to trigger conditional formatting (sure that means you need to add this field to the form's bound table)
    in format condition
    Expand|Select|Wrap|Line Numbers
    1. Forms!frmFormName!chkMark=True
    2.  
    in form's module
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_BeforeUpdate(Cancel As Integer)
    2.     'to clear previous record mark
    3.     Me.chkMark = False
    4. End Sub
    5.  
    6. Private Sub Form_Current()
    7.     'to check current record mark if it is not new one
    8.     If Not Me.NewRecord Then Me.f3 = True
    9. End Sub
    10.  

Regards,
Fish
Dec 14 '07 #2

reginaldmerritt
100+
P: 201
thanks for your reply Fish

your quite right i have the form in continues form view. I have a check box on the form which is bound. When this check box is clicked i what all the fields in that perticular record to change color, a way of highlightin a chosen record. Lets just say it is called chkMark.

I'm a bit thrown by the code you showed, i was knida hoping there was a currentrecord. rahter than a me. that i could use.

I don't understand how the code you mentioned achives this. and i'm very confused what me.f3 means.
I tried putting
Expand|Select|Wrap|Line Numbers
  1.  if chkMark=True then me.fieldname01.backcolor = 255
in form_current but that doesn't do anything.

Sorry if i'm gettin the wrong end of the stick, or not explaning myself well.
Dec 14 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Hi, Reginald.

Ok. Below is a fixed code with some explanations.

Expand|Select|Wrap|Line Numbers
  1. 'BeforeUpdate event fires when a current record looses focus
  2. 'the code clears chkMark so conditional format makes the control looks like 
  3. 'inactive record
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5.     'to clear previous record mark
  6.     Me.chkMark = False
  7. End Sub
  8.  
  9. 'Current event fires when form controls are populated with current record values
  10. Private Sub Form_Current()
  11.     'to check current record mark if it is not new one
  12.     If Not Me.NewRecord Then Me.chkMark = True
  13. End Sub
  14.  
The following expression should be placed in a control needed to change its appearance conditional format condition. As well as comparisson type should be set to "Expression is" and background color to what you like.
Expand|Select|Wrap|Line Numbers
  1. Forms!frmFormName!chkMark=True
  2.  
To understand how the code works toggle breakpoints on each sub and see when they are invoked.
Dec 14 '07 #4

NeoPa
Expert Mod 15k+
P: 31,491
Reginald, if you think about what you're asking it to do you will see that it doesn't actually make sense (this is possibly only comprehensible if you understand what's going on under the hood of course).
The control that you're accessing is actually a single object that is reused as separate instances for each record displayed on the form. When you change the design of the control, you do just that. Change the design of the control which is used for each instance. Instances are not even perpetuated when they are no longer visible on the form, so the practicalities of changing the settings at an instance (record) level are further complicated.

Hence the recommended way to handle a situation like this is to design conditional formatting into the control itself. This way the data determines the formatting and consistency is maintained after scrolling or rebuilding the query.
Dec 14 '07 #5

reginaldmerritt
100+
P: 201
Ah i see, not a smiple as i hoped, but i understand why now. thanks NeoPa.

Thanks for the clearer explination Fish, i appreciate your time.

I see how it will show which record is current and would highlight the current record. Which is simular to using "When Field Has Focus" under Condintonal Formating for that control. Or atleast that is what the code does for me.

What i'm actually after is a way to have a check box for the user to choose to turn on or off a backcolor to the control, which will then stay that colour for that record even after the database has been shutdown and reopened. This is so the user can look at a number of records and pick out the ones they have marked easily.

Which isn't really possible, but where there's a will there's a way.
Dec 14 '07 #6

NeoPa
Expert Mod 15k+
P: 31,491
What about an underlay control with the same ControlSource as the CheckBox.
With conditional formatting you could set this control to the colour of your choice. After that, all you have to do is set the other control on top of it with a transparent background. Could that do the trick for you?
Dec 14 '07 #7

reginaldmerritt
100+
P: 201
What about an underlay control with the same ControlSource as the CheckBox.
With conditional formatting you could set this control to the colour of your choice. After that, all you have to do is set the other control on top of it with a transparent background. Could that do the trick for you?
Yeah good idea NeoPa i did think of that. I put a text box under the cotrols i wished to highlight, i set the texcolour to white and the background to white. The checkbox gave the text box a value which i used in contional formating for the text box as' field value is' which turned the backgorund and text colour to yellow.

Unfortunatly the contional formating only seems to apply when directly entering the value and pressing enter. After closing the form and reopening the contional formating did not react and the text box with the correct field value to change colour stayed white unless i gave the control focus and pressed enter again.

Is the a way of running a condional value on a control, like a requery code or something? Or giving focus to a control and make access think you've pressed enter?
Dec 18 '07 #8

NeoPa
Expert Mod 15k+
P: 31,491
I think your problem is more likely related to setting the "Back Color" to White (16777215) instead of setting the "Back Style" to Transparent.
Dec 18 '07 #9

reginaldmerritt
100+
P: 201
I don't know why but it seems to work now.

I added a new bound text control which is set to be behind all other controls and have the same color background as the forms background so you can't see it.

The bound option check box changes the new hiden text control to have the value "a".

The new hiden text control has condtional formating that turns it yellow when equal to "a".

Many Thanks to Fish and NeoPa for helping me work though this.
Dec 18 '07 #10

NeoPa
Expert Mod 15k+
P: 31,491
No problems Reginald.
Pleased to see you got it working in the end :)
Dec 18 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.