highlight a record...in a continous form? 1

[brightstar]

Access 2000:
i have a form bound to a query. the form shows records in continous view. once the user has selected a record i want that record to be highlighted somehow... anyhow?
perfectly i would like it to change the back colour of the text boxes relating to that record, but im not sure thats possible becuase i cant find any way of discerning between controls in one record and another, changing one changes them all. Failing anything like this, is there any work around someone knows of?

 

[DanJR]

hi there,

Access 2000 and above as 'conditional formatting'. In the design view of a form, you can select a textbox and then goto the menubar (Format | Coditional Formatting...). This is where you can change the textbox's look without affecting the other records. Eg, you could change the back colour of a textbox when it get the focus (without affecting the other records).

If this doesn't have all the options you want, then there are other ways to 'conditionally format' a record (see links)

Links to download examples:

http://www.lebans.com/DownloadFiles/FormatByCriteriaVer2.zip

http://www.mvps.org/access/downloads/colorcon.zip

Let us know if you want further help...

I hope this helps,
Cheers,
Dan

 

[brightstar]

the conditional formatting was good on got focus, for one text box, but i want to highlight all the text boxes in a record, and they all cant have the focus at the same time.
one of your examples showed exactly what i want. changing the back colour of a record that is highlighted. but, to be honest, the coding is WAY above my head. i mean i thought i knew a little about VBA, but i just couldnt take in all the variables and calls etc. couldnt wrap my head round it.if you could do it quickly is there any chance of you taking out the code im after? i'd do it myself but i really dont know where to start.

 

[DanJR]

Hi there,

Here is some code that I wrote...

In Access 2000 and above, you will need to add a reference to DAO (this reference is set by default in Acc 97, but in Acc 2000 and above, ADO is the default):

STEP 1: Set DAO Reference:
------------------
1. Within Access, press Alt+F11, to get to the 'code' environment
2. goto the menubar and select Tools|References...
3. here you will see a long list of 'available references'
4. If not already ticked, you will need to look through this list and find an item called "Microsoft DAO 3.6 Object Library". Tick this reference and press OK. (NOTE: the number "3.6" could be "3.51" or similar)
5. Done

STEP 2: Paste this Code into new Module:
----------------------------------------

1. Goto the menubar again and select Insert|Module
2. paste the following code into the module:


Public Function IsCurrentRecord(RowID As String, RowValue As Variant, f As Form) As Boolean
Dim rs As DAO.Recordset
Dim strCriteria As String
Dim lngCurrentRow As Long

On Error GoTo errHandler

IsCurrentRecord = False 'default

'create search criteria
If IsNumeric(RowValue) Then
strCriteria = "[" & RowID & "] = " & RowValue
Else
strCriteria = "[" & RowID & "] = '" & RowValue & "'"
End If


'find position of RowValue in recordset
Set rs = f.RecordsetClone
lngCurrentRow = f.CurrentRecord

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
rs.FindFirst strCriteria

IsCurrentRecord = (rs.AbsolutePosition + 1 = lngCurrentRow)

End If

CleanUp:
exit function

errHandler:
Debug.print err.number & ": " & err.description
resume cleanup

End Function


STEP 3: Make Continuous Form
----------------------------

1. Create an invisible textbox within your continuous form
2. Name the textbox txtIsCurrentRecord
3. Set its control source to: =IsCurrentRecord("UniqueID",[UniqueID],[Form])

4. Replace the word "UniqueID" with the name of the field that can uniquely identify a record

e.g. If your form is based on a table like this:

myID LastName FirstName
1 Smith John
2 Jo Blogg
3 Jo Smith

then, the control source of the invisible textbox (txtIsCurrentRecord) would be:

=IsCurrentRecord("myID",[myID],[Form])


STEP 4: Add Conditional Formatting
----------------------------------

1. For each textbox that you wish to change the background colour, do the following

2. Goto the 'conditional formatting' form, and make 'condition 1' equal to:

Select "Expression Is"
In the expression box type: [txtIsCurrentRecord]=True


All done.

I hope this helps. Let me know if my instructions are hard to follow or i've made a mistake somewhere..

Cheers,
Dan

 

[DanJR]

Opps, I forgot Step 5!

STEP 5: Add Code to 'Current' Event of form
-------------------------------------------

1. In the design view of your continuous form, goto the menu bar and select View|Code

2. Paste the following code into the forms module:

Private Sub Form_Current()
Me.txtIsCurrentRecord.Requery
End Sub

That should be all now...

Cheers,
Dan

 

[brightstar]

dammit man, your a genius.
thats exactly what i was after!

thanks a lot.