Conditional formatting on a whole form 1

[domino3]

I have a form which users choose the required record by selecting from a drop down combo box. I am trying to change the background colour on the whole form depending on the membership type for the record selected in the combo box. The code I have at the moment is:

Private Sub Combo_member_AfterUpdate()
On Error GoTo Combo_member_AfterUpdate_Err
DoCmd.GoToControl "ID_No"
DoCmd.FindRecord Combo_member, acEntire, False, , False, , True
Combo_member_AfterUpdate_Exit:
Exit Sub
Combo_member_AfterUpdate_Err:
MsgBox Error$
Resume Combo_member_AfterUpdate_Exit
Me!Combo_member_AfterUpdate.Requery
End Sub

and what I tried to do was add an if statement above the afterupdate_exit, but couldn't work out how to refer to the membership type box in the if statement. For example, if the membership type is donor the background colour of the form would be green.

I'd be very grateful if anyone could either give me an idea of how to put the code together, or point me in the direction or where to look for info. I've tried looking at conditional formatting on this site, but only seem to find formatting for particular control boxes, not the whole form. But I might be looking in the wrong place.

Any help gratefully received. Thank you.

 

[domino3]

Thought I ought to show what I have already tried.

If Me.Membership_Type = "Donor" Then
Me.membership_form.BackColor = vbRed
Else
Me.membership_form.BackColor = vbgrey
End If

This fits just above Combo_member_AfterUpdate_Exit: but gives me an error message on Me.membership_form.BackColor = vbRed so I am obviously doing something wrong.

 

[domino3]

I realised that I could perform the colour background changes with a macro on the on current event. This works fine. However, when I tried to change the macro to vba it stopped working. Although I could leave it as a macro, I would rather have it as vba if possible. The code it creates is:

Private Sub Form_Current()
On Error GoTo Form_Current_Err

Forms!requirements_colour_form2.Section ( 0 ) [].BackColor

= IIf ( Forms!requirements_colour_form2!From = "home" , 16777134 , 12615935 )

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

I don't understand why it would work as a macro and not as vba. I thought that letting the system convert it would ensure there were no problems, but obviously not. Can anyone explain to me what the problem with the above code is, and how to solve it? Thank you.

 

[PHV]

And what about this ?
Me.Section(acDetail).BackColor = IIf(Me!From = "home", 16777134 , 12615935)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[domino3]

The me.section works fine. Thank you. One more question, though. I tried having three options by having

Me.Section(acDetail).BackColor = IIf(Me!From = "home", 16777134 ,IIf(Me!From = "work", 65789, 12615935))

to give me three options and I got an error message. Is it not possible to do this type of nested if with lots of options, or do I need to change the way I show the options.

 

[domino3]

It must have been a glitch on my machine, because I've just tried the same code again and it works fine changing between the three colours. So thank you everyone for all your help.

 

[domino3]

I've taken this colour changing form at little bit further by setting the form to requery when the "from" field is changed. I've done this by having a requery event in the on change property for the "from" field. However, the form then clears the screen as it changes the background colour, rather than staying in the record it was in to begin with. I've tried changing requery to refresh, but that doesn't make any colour change at all until I go into another record. Can anyone tell me if how to get to stay in the record I am in and change the screen colour as well? Thank you.

 

[PHV]

Repaint ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[domino3]

That still doesn't stay in the record already selected. What I would like to be able to do is refresh the infomration in the form, without switching to a different record. The code I am usig at the moment is:

Private Sub Form_Current()
On Error GoTo Form_Current_Err
Me.Section(acDetail).BackColor = IIf(Me!Membership_Type = "full", 16777134, IIf(Me!Membership_Type = "donor", 8453888, IIf(Me!Membership_Type = "bereaved", 3677902, 12632256)))
Refresh
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub

I've now tried refresh, requery and repaint and they all seem to do a very similar action, namely they make the changes but don't stay in the record already selected. Any suggestions on how to solve this would be gratefully received. Thank you.

 

[domino3]

I've now tried adding a refresh button to the form, to see if that would refresh the data while staying in the same record, with no success. However, could anyone let me know how to write code to refresh the data, then reselect the record I was already in? It strikes me that it would give the result I want, but I can't work out how to get the system to remember the record in the first place.

 

[domino3]

I've now searched further in tek-tips and have found that I may need to declare a variable and then set the id_no as a bookmark. I've now changed the code as follows:

Private Sub Form_Current()
Dim intID As Variant
intID = Me.ID_No
On Error GoTo Form_Current_Err
Me.Section(acDetail).BackColor = IIf(Me!Membership_Type = "full", 16777134, IIf(Me!Membership_Type = "donor", 8453888, IIf(Me!Membership_Type = "bereaved", 3677902, 12632256)))
Me.Refresh
Me.Recordset.FindFirst "ID_No =" & intID
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub

but now get a syntax error with a missing operator in the expression. What I want the code to do is bookmark the id_no for the record I am in, allow me to make the change to membership_type I neeed, then refresh the data whilst applying the ID number bookmarked to keep the form in the record I was in originally. I'm hoping that is what the code above is trying to do, too. If anyone can see what it is I have missed or got wrong, I'd be very garetful for a response. Thank you.

 

[domino3]

I've realised that as my id no is a number the beginning of the code should read (I think)

Dim intID As Integer

intID = Me!ID_No.Value

If that's npot right, or if there are problems in the code in the posting above, please let me know. I'm starting to feel swamped by this, and don't know how to proceed.

 

[MajP]

First I would recommend getting rid of the nested "iif". These are slow and problematic, but worse they are really hard to read and debug. (Litwin and Getz in A2K Developers handbook have a good discussion on why not to use nested iif) Use an "if then else" or a "select case"
Untested:

Select Case Me.Membership_Type
Case "Full"
Me.Section(acDetail).BackColor = 16777134
Case "Donor"
Me.Section(acDetail).BackColor = 8453888
Case "Bereaved"
Me.Section(acDetail).BackColor = 3677902
Case Else
Me.Section(acDetail).BackColor = 12632256
end Select

Where does the code breaks?. (When replying to Tek-tips always say where the code breaks, it is very helpful)

I use a bookmark to do this. Something like

Dim rs as dao.recordset
set rs = me.recordsetclone
'do your refresh and other code
me.bookmark = rs.bookmark

 

[domino3]

Thanks for your reply. I've now changed the code to your select case suggestion, and it is much easier to read. I'm going to try your bookmark suggestion next. I had just found a rough and ready solution, by getting the system to go to the next record, then the previous record, when the membership type was changed. It does work, but seems an unreliable way to do it. So I'm hoping yours will work.

Thanks again.

 

[domino3]

I'm finally having to try the bookmark suggestion, as the cheat I used going back a record then forward only works for existing records. I got away with it for a few months, but all good things come to end. The moment someone tried to create a new record the system fell apart.

What I don't understand is where the bookmark gets applied to. Is it the on_current on the form itself where I have the different backgound options, or the after_update on the field which is responsible for which colour should be showing?

Thank you.

 

[domino3]

The code I have in the after_update of the membership type field is

Private Sub Combo7633_AfterUpdate()
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone
Me.Requery
Me.Bookmark = rs.Bookmark

End Sub

This brings up a debug of "no current record". I've also tried recalc and refresh, and these change the membership type but revert to the first record and don't stay on the record selected.

I'm sure it should be simple to work out, but I am now stuck.

Any help would be much appreciated. Thank you

 

[domino3]

I've kept on trying different options on the code above, but keep getting the no current record debug message on the line

Me.Bookmark = rs.Bookmark

Can anyone help by telling me if there is some other code I need to add to get the bookmark to work? Any advice would be myuch appreciated.

Thank you.

 

[MajP]

Requerying a form invalidates any bookmarks set on records in the form. However, clicking Refresh on the Records menu doesn't affect bookmarks.

Why not reorder the code?
Dim rs As dao.Recordset
Me.Requery
Set rs = Me.RecordsetClone
Me.Bookmark = rs.Bookmar

 

[domino3]

Thanks for your reply. I've now tried reordering, but that still brings up a debug on the line

Me.Bookmark = rs.Bookmark

 

[domino3]

I've just noticed that with the new order I do now get the record I want to stay on the screen. However, the debug error still happens and the background colour doesn't change.