Multiple partial records combine to complete a task for each month. Status display of that task.

[Redjinator]

To the curious reader... As a courtesy I feel I should say this post is...involved! Only the brave without better things to do with their time should press on!



My main point of this thread is to ask for a better solution:

Ok so I've tried finding a better solution to this but I think wording it correctly for a search might be my largest enemy. If I've missed some great info somewhere, a point in the right direction is greatly appreciated.
Bare with me I'm new to Access, but not to Office/VBA. (me and VBA are like people that can understand their mother language when they hear it, but are not amazing at speaking and writing it due to rarely using it).
I'll try to be as detailed as I can, ideally I'd like to approach a solution with the least amount of VBA involvement mainly to increase my MS Access abilities. But if VBA is where I sensibly have to go then that is fine.
Oh and I apologize for my terrible naming of objects, I've educated myself on better naming practices since starting this project and I promise I'll do better in the future!

On with the details...

Situation: Those with high blood pressure or risk of aneurysm, exit now!

At my compound we have multiple people working from a main office, and 6 un-manned buildings.
Monthly inspections of fire extinguishers take place and when 100% complete a report is sent up the chain of command.
Extinguishers are inspected in an opportunistic fashion when other work must be done, then the inspection is recorded into a table via a form.

This form(bound) also has a sub form to show recorded inspections for convenience. The user enters their name, the date, and checks the checkbox for the building(s) they inspected as well as a check if the Fire Warden Report was sent up the chain of command. (Screenshot below of form and table with randomized data for testing purposes). So far, no problems.
<--table (name: "02 Fire Warden Records")

<--bound form(name: "02 Fire Warden" | Subform name: "Fire Warden Records subform")

I'm attempting to have an unbound form(named: "UnboundForm") in my DB which will serve as an overall status screen for multiple monthly/weekly/daily duties of our office.
For the context of these Extinguisher inspections, I plan to have a 'status light' (rectangle) that will change backcolor between Red and Green depending on status for the month selected in a combobox (name: "MonthCombo")

So to isolate records by Month, I created a query(named: "q_FW_Subform") with all the building fields of the "02 Fire Warden Records" Table minus "Signed Complete" , and added a field (name: "Month") to the query to convert the existing dates into the name of the month by using "MonthName(Month([Date of Insp]))" so I could pull record information by Month using my combobox (name: "MonthCombo") on my "UnboundForm", and loaded the resulting information (for prototyping/testing purposes) into a subform(named: "sub_FWstatus1") with the idea that I would teach myself now how to parse the information to get what I needed from it to make my status light respond appropriately.

Are you still with me? We're almost there I promise.

So now when I choose a month from my combobox (lets say "June") my subform re-queries and shows all entries from June, no problem. But all I want is to know that an individual building has been checked off at least once in that month. I don't need to know it's checked 3 times. Anyway I move on and create a little status light for each individual building and create 8 textboxes in the header of subform(named: "sub_FWstatus1"), named as "cnt259, cnt260... etc" to
count the number of times a building is "checked" in my results from the monthly query by using " =Abs(Sum([Fieldname])) ".

I created those textboxes in the subform's header because I was having no luck with putting them in the mainform area. Oddly enough I put 8 more textboxes (Text21, 23, 25, 27....35) on the mainform and I was able to retrieve the resulting checks counted from the txtboxes in the subforms header without any issues. ... So I move forward and pump out some really bad code in VBA to change the individual building lights to green if it's related count was greater than or = too 1. (VBA code is in the event After Update of the combobox "MonthCombo".



Code:
Private Sub MonthCombo_AfterUpdate()
Me.sub_FWstatus1.Requery

Dim inspB259 As Integer
inspB259 = Me.Text21.Value

Dim inspB260 As Integer
inspB260 = Me.Text23.Value

Dim inspB261 As Integer
inspB261 = Me.Text25.Value

Dim inspB262 As Integer
inspB262 = Me.Text27.Value

Dim insp85E As Integer
insp85E = Me.Text29.Value

Dim insp85F As Integer
insp85F = Me.Text31.Value

Dim insp25M As Integer
insp25M = Me.Text33.Value

Dim inspFWR As Integer
inspFWR = Me.Text35.Value

CheckInspections inspB259, box1
CheckInspections inspB260, box2
CheckInspections inspB261, box3
CheckInspections inspB262, box4
CheckInspections insp85E, box5
CheckInspections insp85F, box6
CheckInspections insp25M, box7
CheckInspections inspFWR, box8
End Sub
Public Sub CheckInspections(insp As Integer, box As Rectangle)
If insp > 0 Then
box.BackColor = RGB(0, 250, 0)
End If
If insp <= 0 Then
box.BackColor = RGB(250, 0, 0)
End If
End Sub



















And it works.

Now I could just write some VBA to check and see if all of the new textboxes are >= 1 and turn green if they are but in the end, all I want is the "Overall Status Light" for my UnboundForm because I will be adding the status from many other tasks eventually to this form and I just want this form to be extremely simple and quick to look at.
So I went and turned the subform(named: sub_FWstatus1) visibility property to "no". And everything stopped working. (update: "just tried this again, and now it's work... :S ")

Conclusion: I've spent a week with Access, I really like it! But... yeah, I know... it's bad.
I'm sure there is some really simple solutions to what I want to do here and I don't regret my approach as I've learned a great deal from building my Frankenstein form. But I would prefer to not to have to continue so...wildly.

Advice, Criticism, Threats... don't go easy on me I can take it!
(kidding, keep it professional please)

And thank you.

Redjinator

[ssanfu]

My observations/thoughts....

and added a field (name: "Month") to the query

"Month" is a reserved word in Access and shouldn't be used as an object name.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

Without having your dB to analyze and with what you have posted, I would have to say the design of the table "02 Fire Warden Records" is, well, wrong. It appears that the table is designed like an Excel spreadsheet.

Field names like "B259", "B260". "B261", etc are actually data... I would have fields:
InspectionID_PK (autonumber)
BldgName (Text)
InspectionDate (Date/time)


And speaking of "02 Fire Warden Records", should not use spaces, punctuation or special characters (exception is the underscore) in object names.

[Redjinator]

[QUOTE=ssanfu;323258]My observations/thoughts....

I would have to say the design of the table "02 Fire Warden Records" is, well, wrong. It appears that the table is designed like an Excel spreadsheet.

Field names like "B259", "B260". "B261", etc are actually data... I would have fields:
InspectionID_PK (autonumber)
BldgName (Text)
InspectionDate (Date/time)
QUOTE]

Thank you for your response!

This has actually switched on a light bulb for me. I will update soon with my revised version of everything.
I've done some reading on normalization (was very interesting), commonly accepted best practices for naming fields/objects, relationship models, and a really involved blog about how lookups in tables are EVIL!!!!! lol.
Which.... I don't agree with because if I'm interpreting it correctly the problems associated with lookups are mainly to do with sorting and users not understanding that sorts will be based off the hidden PK instead of the data selected in the lookup's combo box? Seems like something easily overcome if you remain conscious of what's really happening. But anyway, I'm really new at this so maybe there is more to it than I am aware.

I'll update in the next day or two with a full upload of my database file.

Thanks again

Regg