Hi Everyone

I have a list of clients in a continous form ordered alphabetically on Surname. It occured to me to use conditional formatting to highlight the first surname starting with a new letter just to make the list easier to look thro'.

So the conditional formatting expression would be

Left(Surname)<>Left(Surname of the Last Record)

So how do I refer to the last record?

TIA

Hi Ian,

I like your idea!

See this MSDN link on how to reference the previous record.

I would recommend the 'Code' method (2nd suggestion) over DLookup() (1st suggestion) as you will be using this on a whole table of data.

In fact, I would recommend wrapping the code in to a boolean function that you can use as your format condition.


hth,

d

Thanks d

You're right the DLookup isn't the way to go but I can't get the code route working either.

My list has, of course, unique ID's but it isn't arranged by that ID & for good reasons can't be.

It's arranged by surname & of course surnames may duplicate - we have many many 'Smiths'!

I'll keep playing but if you've any other suggestions I'd be grateful.

Thanks again for your interest.

Ian

Hi Ian,

With the code method desribed in the article it shouldn't matter what order the ID's are in because it uses .MovePrevious to see what the previous record is.

If you just want to use the function for this particular purpose you can probaly hardcode a lot of the values too.

Assume your form's RecordSource is:
Select conID, conFirst, conSurname, [other fields as required]
From tblContact
[Inner Join ...]
[Where ...]
Order By conSurname, conFirst, [etc];

and assume conID is a Long Integer and is contained in a textbox called 'txtID',
and conSurname is contained in a textbox called txtSurname,
and your form is called 'frmContacts'



Then, for your format condition use:

fNewStartLetter([Forms]![frmContacts], [txtID], [txtSurname]) = True

(Usual caveats about air-code apply! )

hth,

d

Hi d

Thanks for your input again.

There's a problem with your line

fNewStartLetter = Left(.Fields("ConSurname", 1) <> Left(strSurname, 1)

& as I don't understand how your function works - oneday perhaps - I can't resolve it.

Help please

Ian

Scratch that last post just noticed missing )

Hi d

Hurray it works - that is your fNewStartLetter function works. I still don't understand how/why - but what the [censored] - it works!

Thank you very much!

Ian

Dang! I kept saying to myself: "Don't forget the bracket"!, and of course I forgot!

I'm glad you got it to work Ian.

So, what happens?

Each record in the form passes the value it has for ID (lConID) and Surname (strSurname) and the form (frm) itself to the function.

You pass the form object to the function so that the function can access it's RecordsetClone (which is a DAO recordset).

Then the function finds the record with the same contact ID using: '.FindFirst "ConID = " & lConID'

It then moves back to the previous record: '.MovePrevious'

It checks that you haven't moved back before the first record (ie you were on the first record): 'If Not .BOF Then'

Then comes the interesting part! Compare the first letter of the surname in the previous record with the first letter of the passed surname:

Left(.Fields("ConSurname", 1)) <> Left(strSurname, 1)

This evaluates to either True or False which is assigned as the result of the function:

fNewStartLetter = Left(.Fields("ConSurname", 1)) <> Left(strSurname, 1)

You can encase it in brackets if it makes it clearer:

fNewStartLetter = (Left(.Fields("ConSurname", 1)) <> Left(strSurname, 1))

If the result of the 'Not .BOF' check returned False (ie there is no previous record to compare) then the above comparison is skipped and the function returns False (which is what a boolean variable initialises to.)

I'm not sure if that is any clearer - but the main thing is that is does the job!




d

Thanks again!