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]

I've now tried merging the two procedures that had both partially worked, by trying to go back to the first record and then back to the record in my bookmark as an after_update on my memmbership_type field, instead of having to refresh or requery. I hope that makes sense. The code I used was

Private Sub Combo7633_AfterUpdate()
Dim rs As dao.Recordset

Set rs = Me.RecordsetClone
DoCmd.GoToRecord , , acFirst
Me.Bookmark = rs.Bookmark
End Sub

However, the record on the screen goes to the first record, and doesn't then go back to the bookmarked code. Do I need to add a docmd instruction in front to the me.bookmark line. I've looked down the options on docmd and nothing struck me as being correct.

 

[MajP]

I gave you bad info. Working with bookmarks can get a little confusing. I would recommend doing something like this (untested)

'where varID is a primary key of the record
dim varID as variant
dim rs as dao.recordset
'if this is a new record might need to save record first
varID = me.fieldname
me.requery
set rs=me.recordset
rs.findfirst "fieldName = " & varID
'if text then "fieldName = '" & varID & "'

 

[MajP]

I think until a record is saved the bookmark is not created which may have been causing an error. To save a record in your after update

DoCmd.RunCommand acCmdSaveRecord

 

[domino3]

That seems to have solved the problem. I had to move various fields which were required input to be filled in before the membership_type field, but now it works exactly as I wanted. Thank you very much.