Highlighting a current row in a form

[WCStarks]

I found a solution online for highlighting the current row of a continuous sub-form. I have implemented highlighting a row in a sub-form of a "Tab Control" parent form as follows:

1) I created a hidden unbound text control in the continuous sub-form named, "txtSelectedRecord".

2) Then in the sub-form On Current property, I added this code:

Private Sub Form_Current()
Me.txtSelectedRecord = Me.[ID]
End Sub

3) Then, for all the fields in the row of the continuous sub-form, I did the "Conditional Formatting", and entered the rule, "[ID] = [txtSelectedRecord]" for the property, "Expression Is"

This method works fine in the sub-form of a "Tab Control" parent form, but it does not work for a sub-form of a "Navigation Control" parent form. All the rows get the same highlighted background color. I suppose the "Me." does not work properly within the context of a "Navigation Control" parent. How should I modify this to make it work?

[June7]

I tested this and syntax you show all works for me in a Navigation Form.

If you want to provide db for analysis, follow instructions at bottom of my post.

[WCStarks]

1) Here is the sanitized db. Under the default Navigation Control, "Members", go to "SCATeam" in the Combo Box Control, and then select the "Ledger" Tab Control below. You will see the highlighting working correctly. The problem is under the "Events" Navigation Control. The current color of the rows in the continuous form, as shown on my computer, is the highlighted color, rather than the normal color. I can see the normal color in the last row, to add a new row.

2) While you have the db, perhaps you could also look at another issue for me. Under the Navigation Control, "Committees", if I attempt to add another person to the child continuous form, the system errors out. Access does not otherwise crash. See Error attachments. After the error, the name gets into the new record, but the associated Member record is not linked to the Committees parent record. This used to work, but even though I had not modified this module since it was working, one day, some months ago, it stopped working. If it also does not work on your system, as you will see, the errors occur before the related procedure is even executed, so I cannot debug it. I suspect, the problem may be related to a software update months ago (Windows or Access). I had discussed this issue with MS Access/Windows Support, but they did not know how to resolve it. I don't know how to close and restart the Ole server.
SCATeam.zip

[mike60smart]

Hi

I looked at the problem with Committee's and traced the problem to your Relationships between tables.

I tried to set Referential Integrity between Members and Committe_Members and it produced the standard Microsoft error message indicating that you had created Child Records with no Matching Parent.

There was 1 record in Committe_Members which had a Foreign Key of 0
Seeing as there is no Primary Key of 0 - hence the error.

I deleted this record in the BE and then enforced Referential integrity.

The ON Click errors you attached screen shots for usually indicate corruption of some sort.

To solve this I created a New Blank Front End named SCATeam V1 and then Imported all of the Objects from your original FE.
I did NOT Import the list of tables you had in the FE as these should ONLY be located in the BE.
I then relinked your BE to the New FE.

Tried to add a Committee Member and still the same problem.

To solve this I recreated the Subform for Committee members and called it frmCommitteeMembersSubform

You can now add Committe Members.

There are other issues with the database with the Main ones being:-

1. Unable to set Referential Integrity between certain tables.
2. Need to remove the Default of 0 from ALL Foreign Key Fields
3. Remove ALL Lookup fields from numerous Tables. (Lookup the "Evils of Lookup Fields in Tables" on Google)

[WCStarks]

Mike60Mark,
Thanks for your help. However, my original Combo box control is missing in the sub-form, which looks up the members to link to. I will need to restore or recreate that control before I can actually add members. The problem may be in that control. I take it you didn't actually test using that combo box control.

NOTE: After I changed the 0 foreign key to 5 in the table, which it should have been, I tried the original form and it works now. I guess the invalid reference was causing the OLE issue. I am now able to keep the original form. Thanks again for your clue to the resolution. I will see what I need to do to clean up issues you reported.

How can I remove the lookup fields. I need to keep the fields. Do I just change their properties?

I discovered that the combo box was storing the member.ID in the Com_Mem_ID field, instead of in the Member_ID field. This is what was causing the problem. That is now fixed.

[mike60smart]

Hi

You do not need a Combobox in the Committee's Subform to select an Employee.

The EmployeeID is currently the FK and this links to the PKJ of the Employee.

All you need to do is select a Committee that the current Employee is becoming a Member of.

[WCStarks]

Hi

You do not need a Combobox in the Committee's Subform to select an Employee.

The EmployeeID is currently the FK and this links to the PKJ of the Employee.

All you need to do is select a Committee that the current Employee is becoming a Member of.

Mike,

You must be thinking from the perspective of the Members Navigation Control module. Many Tab Controls under the "Members" Navigation Control are essentially "reports", showing the member's participation in those various activities. These relationships are established and managed in the related Navigation Control modules.

For example:
Members' "Activities" links are established and managed in the "Events" Navigation Control
Members' "Assets" in the "Assets" NC
Members' "Leadership" in "Organization" NC
Members' "Committees" in "Committees" NC
Members' "Liaison" in the "Agency" NC
These Members' modules are not editable. The remaining "Members" modules are specific to the member and are managed under the Members NC module.

It would be terribly awkward to not work from the perspective of those main NC modules. If you will look under the "Events" NC, you will see what I mean. When there is a meeting, or a call out, project, or any other event, they are created and managed here. For call outs, for example, we need to show who is filling what shift during the call out. This cannot be done effectively from the perspective of the member. We need to be able to make assignments for, and to see at a glance, who is participating in which shifts, and which shifts are not yet filled for that call out. This can only be done from the Events NC module. So, yes, the Members Combo Box is needed in each of those NC modules to manage those links.

Anyhow, with your clue provided earlier, I was able to understand the issue and to fix it, as indicated in my earlier response.

[WCStarks]

I tested this and syntax you show all works for me in a Navigation Form.

If you want to provide db for analysis, follow instructions at bottom of my post.

Hi June 7,
Did you get a chance to evaluate the uploaded db to see my issue 1) under the "Events" Navigation Control module? Issue 2) is resolved.