Conditional formatting for visual layout

[captike]

Ok, I'll give that a whirl.

[captike]

Ok, when I put that into the record source for the form and switch to Form View, I get an error that says Syntax Error in FROM clause. I attached what I typed in the record source. Just a note, I changed the name of the Description field since it was a reserved word. It's now called MaintenanceDescription.

Additionally, I see what the code is aiming to do, but how does it tie a given text box on the form to a unique EquipmentID? I'm lost on that part.

[CJ_London]

It was freetyped as I don't have your data and not clear what the syntax error actually is. A screenshot of the error would be more helpful - or provide a small db with some relevant data in it.

equipmentID? Ah I see you need it because you have two bits of equipment in the same unit and position, so add

tblEquipment.equipmentID

to your select query

You might also need to add property number as well

Edit - when I copy pasted the code I missed the ON part

SELECT Unit, Position, M.equipmentID is not null as Serviced
FROM tblEquipment LEFT JOIN (SELECT DISTINCT EquipmentID FROM tblMaintenance  WHERE year(MaintenanceDate)=year(date()) AND Description='Annual Service Complete') as M
ON tblEquipment.EquipmentID=m.EquipmentID

[captike]

I put the new code in the Record source and the error is now gone, so I definitely feel like I'm making progress. (I attached the code I used) I can also put "Serviced=True" in the conditional format box and it pops up in the Intellisense format so that has to be good, right? Now, I can't figure out where to go from here to get the text boxes on the form to relate to a specific pack. I attached the beginning stage of the form I want designed with the text boxes on it. So, for the first box under Engine 12 should be the LT pack assigned to that unit..in this case it would be EquipmentID=9, but if I put that in the control source, I get the #Name? error on the form. So, how do I assign a given text box (with the conditional format of Serviced=True) to a specific pack?

[CJ_London]

Doesn’t look like you have a normalised form

[captike]

I'm not familiar with that term with regards to forms...what does that mean and how should I normalize it?

[CJ_London]

you are using unbound controls. I would expect you to use a continuous form or a report per post #6. To get your design in post #1 each 'box' would be a continuous subform/report on a main form/report

[ssanfu]

Maybe post your dB? It would be easier than playing 20 questions......

I'm not familiar with that term with regards to forms...what does that mean and how should I normalize it?

Happy reading....

What is Normalization? (Parts 1 - 5)
=======================
Part I: Why Normalization?
Part II: Break it up.
Part III: Putting It Back Together
Part IV: More Relationships
Part V: Many-to-Many Relationships


The Normal Forms
=========================
Introduction
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
In a Nutshell
Try for at least 3NF...




The test is on Friday

[captike]

you are using unbound controls. I would expect you to use a continuous form or a report per post #6. To get your design in post #1 each 'box' would be a continuous subform/report on a main form/report

Ok, I think I'm tracking. So, for each box (or physical truck) what would the five positions fields be bound to? The position name field (LT, D/O, FF1, etc) or the equipmentID's. I don't need any information from the table in them other than the position names.

Maybe post your dB? It would be easier than playing 20 questions......




Happy reading....

What is Normalization? (Parts 1 - 5)
=======================
Part I: Why Normalization?
Part II: Break it up.
Part III: Putting It Back Together
Part IV: More Relationships
Part V: Many-to-Many Relationships


The Normal Forms
=========================
Introduction
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
In a Nutshell
Try for at least 3NF...




The test is on Friday

I tried to attach the db file, but it says it exceeds the file limit. Is a Dropbox or Google Drive link ok?
And I love the reading!! Thanks for that....I'll do my best to be prepared for Friday's test.

[CJ_London]

not for me, I will only download from the forum.

Remove tables/forms/reports not relevant to your question. then compact, finally zip and upload.

[captike]

Here you go. Thanks again for helping me out.

[CJ_London]

based on what is provided, in attached see a new qry and report called qryServiceStatus and rptServiceStatus. The latter is based on the former. It produces this result

[captike]

YES!! From here it sure looks like you nailed it! I'll unpack that tomorrow at work and see how you did it. Until then, I'm still studying my homework. Thank you, thank you, thank you! You're amazing!

[captike]

I've made the provided changes and after some testing it works with one problem. If there is another maintenance type record (say Repaired, for instance) the Unit doesn't show up on the report at all. The unit will only show up (in red) if the maintenance for that pack is completely empty. If there is a maintenance record for something other than PM, they're not listed. I'm guessing this is a change in the query, but I've tried several iterations and can't nail it down.

[CJ_London]

Always better to provide data which truly reflects the real life situation. You need a different type of query

SELECT tblEquipment.Unit, tblEquipment.Position, [c].[equipmentID] Is Not Null AS serviced
FROM tblEquipment LEFT JOIN (SELECT tblMaintenance.EquipmentID
FROM tblMaintenance
WHERE (((tblMaintenance.MaintenanceType)="PM") AND ((Year([MaintenanceDate]))=Year(Now()))))  AS C ON tblEquipment.EquipmentID = C.EquipmentID
WHERE (((tblEquipment.Unit) Is Not Null));