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

Hi Khalid,

Firstly apologies to Bob, I didn't know there were no spaces between the controls, so they needed their backgrounds highlighted.

I now see the problem about the highlighting jumping to the blank row as soon as you start a new record. I could not find any event of the form that I could use to stop it, but I could use the OnChange event of any individual control. If only one particular control is the always the first one to be filled, e.g. CartonNo, only that box needs the OnChange code added. But if the user can start in any box, the code would have to be added to all of them.

Although your controls are now transparent, the one being used will not be transparent. This looks nice, but if you prefer them not to change color, you can make the back color the same as your highlight color, then make the back style transparent again.

My code is now as follows, assuming the CartonNo is always entered first. I don't seem to need the Null clauses after all.

Private Sub CartonNo_Change()
Me.IDs = Me.ID
End Sub

Private Sub Form_Current()
Me.IDs = Me.ID
End Sub

The conditional format expression is unchanged.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@yahoo.com [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Thursday, October 29, 2015 8:02 PM

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

Hi Dave,

Thanks to Bob, his suggestion was correct. I changed Back Style from 'Normal' to 'Transparent' of all controls on the front and now able to see row highlighted.

But, now one issue is left. My requirement was that the row on which data entry is being made that should highlight.

On the form focus is set to control 'CartonNo' (which is not highlighted). All other controls are highlighted. As i start entering data in 'CartonNo' next row is highlighted (By next row i mean on Record Selector which is marked with *). I thought may be it is because of Update event of two controls like:

Private Sub ProductQty_AfterUpdate()

If Me.NewRecord Then

ProductQty.DefaultValue = Chr(34) & ProductQty & Chr(34)

End If

End Sub

So for test purpose i stopped these two events, but it was not the case.

Now what do you say?

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :

Hi Khalid,

I thought you had already got it working at your last email. Now that you have done something unknown and can see highlighting, it should still work no matter whether the Highlight box is in front of or behind the other controls. The highlight box should cover the whole Detail area, and show through the gaps between the other controls. It should also be Disabled and Locked so that it can't be selected by the user.

I assume the other controls do not fill the entire space, leaving no room for the highlighting to show through the gaps? If the other controls do fill the entire space, you could delete the highlight box, and apply the conditional format to all the other controls instead.

Or you could put the highlight box in front, but much smaller and of suitable shape to act as an indicator.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Thursday, October 29, 2015 5:04 AM

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

Hi Dave,

Finally I saw a ray of hope. After read again & again your last two replies and also checked the other group you mentioned, doing some experiments at my own like putting IDs on the Form Footer and removing ID1 box and so on...

What I did that just reverted to your first original reply and made all settings according to that.

~~~~~~~~~~~~~~~~~~~~~~~~~

Your original reply as below:

Add "ID" AutoNumber field as Primary Key to CollectionVoucher table.

On CollectionVoucherssubform add:

IDs as a hidden unbound textbox.

ID1 as a hidden textbox with Control Source ID.

A Textbox filling the subform record and behind all other controls.

Conditional Format: (option under "Format" on Menu bar)

Condition 1 Expression is ([IDs]=[ID]) Or (IsNull([IDs] And IsNull([ID])))

I used light yellow fill for highlighting.

Enabled: No

Locked: Yes

Name: Highlight

Form Event Procedure On Current:

Private Sub Form_Current()

If IsNull(Me.ID1) Then

Me.IDs = Null

Else

Me.IDs = Me.ID1

End If

~~~~~~~~~~~~~~~~~~~~~~~~

When i opened the Form i saw one box highlighted (with light yellow color), that struck to me actually by mistake that box was marked "Send to back" so i rest it to "Bring to front". Then i pulled down box "Highlight" below all controls on Detail Section. Re-opened the Form and its working clicking Record Selector of any record it Highlights on that record.

But now only we have to resolve when this box "Highlight" is behind all other controls we cannot see it highlighted?

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :

Hi Khalid,

That's strange. I tried changing ID to ID1 in my conditional format, and it still worked just the same. My original conditional highlight expression referred to ID, the name of the autonumber field. You have now changed the condition to the name of the ID1 box, which has ID as its control source, and should give the same effect.

In your example below, when IDs was 3026 you must have had the second row selected, and when it was 3025 you had the first row selected as you said, and I hope highlighted at last.

There is another Access user group I look at - comp.databases.ms-access. By amazing chance someone gave an answer three days ago how to highlight rows like you. I see from this that my IDs box could be put in the footer (if there is one) to avoid getting in the way of the record boxes in the Detail section, and also there's no need for my ID1 box, provided you put the name of the ID table field (if it's not 'ID') into the conditional format expression, instead of the box name ID1. Maybe you called your autonumber field ID1 as well as the box.

I'm so glad all is now OK.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Wednesday, October 28, 2015 5:24 PM

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

Hi Dave,

Yes, your method of investigating gave us the error.

I checked it opening the Form "Collection Voucher". My way of data entry is that first on main form user selects a "ConsignmentNo" from a combo box "ConsignmentNo" drop down list. Then from the sub form, Form Header he selects from "cmbClient" ClientCIN, Row Source of cmbClient is:

SELECT Clients.ClientCIN, Clients.ClientName, Consignee.ConsigneeName FROM Consignee INNER JOIN Clients ON Consignee.ConsigneeID = Clients.ConsigneeID;

and there are 5 more unbound controls on Form Header. Then on the Detail Section for the specific ConsignmentNo and ClientCIN Consignment is entered goods are received in the shape of Cartons.

I checked for ConsignmentNo 2015-B-07 for ClientCIN 3, which is displaying as follows i am not showing other fields on the Detail Section:

CartonNo ID1 IDs

1 3025 3026

2 3026 3026

3 3027 3026

4 3028 3026

5 3029 3026

6 3030 3026

7 3031 3026

8 3032 3026

9 3033 3026

10 3034 3026

11 3035 3026

12 4644 3026

13 4645 3026

14 4646 3026

15 4647 3026

16 4648 3026

17 4649 3026

18 4650 3026

You can see that IDs is not matching ID1 and for all 18 Cartons is the same 3026.

In Condition 1 Expression is ([IDs]=[ID]) Or (IsNull([IDs] And IsNull([ID])))

I thought may be ID should be replaced ID1, after changing the above to:

Condition 1 Expression is ([IDs]=[ID1]) Or (IsNull([IDs] And IsNull([ID1])))

Then on the first record/Carton i got

1 3025 3025

ID1 in the same above order but IDs for all 18 Cartons 3025

I hope my clarifications might give you some clue the error at my end.

Thanks once again for being with me.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :

Khalid,

Sorry, here is my second posting that I sent to myself by mistake!

To investigate further, make all three textboxes Visible. On the continuous form view, the ID1 boxes should show a different ID for each record. The IDs boxes should all show the same number, put there by my OnCurrent event code to be ID for the record you happen to have currently selected. It will change when you select a different record. Only on the selected record will the numbers in the two boxes be equal, switching on the conditional formatting of the Highlight box.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Wednesday, October 28, 2015 10:46 AM

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

Hi Dave,

After reading your reply i did not made any changes to the end of my subform SELECT statement. Look it is as you have said in your reply.

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, CollectionVoucher.ID

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;

Still I am on the same situation, not highlighting any row.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <davewillgmale@...> wrote :

Hi Khalid,

I should also have said that you have to add ", CollectionVoucher.ID" (without the quotes) to the end of your subform SELECT statement, before the FROM part. Hope this works.

Regards,

Dave

----- Original Message -----

From: khalidtanweerburrah@... [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Tuesday, October 27, 2015 7:34 PM

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

Hi Dave,

Sorry to say i could not resolve the mystery yet...

Regards,

Khalid