ACCESS FEED: Re: [MS_AccessPros] Current record or row colour be highlighted

Jumat, 09 Oktober 2015

Re: [MS_AccessPros] Current record or row colour be highlighted

Hi Khalid,

the row can be highlighted using conditional formatting and a control to keep track of the primary key (PK), which is set on the form current event.

TO HIGHLIGHT THE CURRENT RECORD IN A CONTINUOUS FORM

put the following textbox control on your form (I usually put it in the footer since there is often more unused space)

name --> RecordID_current
visible --> no

make the following textbox control in the detail section of your form:

name --> HighlightBox
left --> 0
top --> 0
width --> width of detail section
height --> height of detail section, like 0.2
enabled --> false
locked --> true
tabstop --> false

send this control to the back so it is behind all the other controls in the section

in the design view of the form, select HighlightBox

since HighlightBox is behind everything, you may need to select it using the object drop-down (combo box in the Properties window at the top)

conditional formatting
condition 1 --> Expression Is
--> [RecordID] = [RecordID_current]

example to also highlight a new record:
    [RecordID_current] = nz([RecordID],RecordID_current)

change fill/back color to LIGHT YELLOW or light gray
or whatever color you want for your highlight

If my detail section background is White, I like to use light yellow for a highlight

WHERE
RecordID is the name of your primary key field

then, in the form OnCurrent event, assign a value to the unbound RecordID_current
being unbound, it will have the same value on every record

'~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
   Me.RecordID_current = IIf([NewRecord], 0, [RecordID])
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~~~~~~~~~~~~~~~~~~~~~~~
'needed since Access 2007
Private Sub Form_BeforeInsert(Cancel As Integer)
   Me.RecordID_current = me.RecordID
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~

where [RecordID] is the controlname (or fieldname) corresponding to your primary key.

~~~
Me.NewRecord is true if the form is on a new record. Throwing this in means that a new record will also be highlighted ... otherwise, since 2007, it will not. I had to create the BeforeInsert event to make it work as well ... maybe this was a temporary bug and the extra steps are no longer necessary

I used to use
nz(RecordID,0)

but I changed to
IIf([NewRecord], 0, [RecordID])
since 2007

~~~
I also put a click event on the highlight box to SetFocus to another control -- because, even though it is not enabled, it will come to the front and cover everything up if it is clicked on!

... however, you want the Click event to ALSO open another form (first setFocus, then open the form)

since HighlightBox spans your entire detail section, you can put a click event on that control.

be sure to save the record (if it needs it) before setting focus somewhere else.

~~~~~~~~~

If you are using a datasheet, I believe you will need to set up conditional formatting for each control as you can't use a control that spans the whole line as you can in a continuous form

warm regards,
crystal

Have a suggestion to improve Access?
http://access.uservoice.com/

~ have an awesome day ~

On 10/9/2015 7:24 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Khalid-

Choose some field in CollectionVoucher that is Required but that does not have a default value. Write Conditional Formatting for all the controls based on the value of that field. If the field is empty (Null or blank), then change the background color. As soon as the user fills in the value, the row should change back to its normal color.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 9, 2015, at 12:48 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,

I have a form "Collection Voucher" on its sub form "CollectionVouchersubform" when i am doing data entry on a new record I need that current row should be highlighted or to a custom colour defined.

Sub forms SQL is as under:

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductNameEnglish, Products.ProductNameRussian, Products.HSCode, CollectionVoucher.BrandName, CollectionVoucher.ProductQty, CollectionVoucher.UnitOfQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.UnitOfGrossWeight, CollectionVoucher.[Net Weight], CollectionVoucher.UnitOfNetWeight, CollectionVoucher.UnitOfValue, CollectionVoucher.Rate, CollectionVoucher.Amount

FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductNameEnglish = CollectionVoucher.ProductNameEnglish;

Help required please.

Regards,

Khalid