Can't be this difficult, is it? - Cascading combo box to autopopulate subform difficulties.

[sabrinalynn23]

Much appreciation in advance to anyone willing to try to help me with this!

The overall purpose is to log client utility bills and eventually set up reporting.

I have multiple projects... all having multiple facilities... with multiple utility accounts... with sometimes multiple meters.
Hence, I believe all of these would have a one to many relationship. No?

I would like the user experience to be like so:
They select the project for that utility bill they would like to enter data for, via a drop down box.
They would then select the facility (or maybe utility account next would be better). I would like only the facilities associated with that project to be displayed.
They would then be able to enter in that bills information into a datasheet type view with a drop down for the particular meter and the corresponding billing information.

What I have now is:
Project on top with a drop down box that properly populates the Facility drop down box with the right ones.
I then have a tabbed subform with the accounts "Gas' and "Electric" and will eventually have water.
Inside the first tab I have the Gas subform (datasheet)

What I can't get right is:
When selecting the facility from the drop down box, it does not change any of the values in the gas subform to only those for that facility. Instead I see all values ever entered.

Frustrated!
I have created relationships and deleted relationships.
Created and re-created forms.
Watched hours of online tutorials.
I just can't seem to figure this out!
I doesn't seem like it should be that difficult.
I've tried the form wizards, codebuilder, Main form, subform, and sub sub form, queries... alksdjalkdjslfkjasdklj arghhhhhhhhhhhhh


This is currently my relationships. I had all of them linked at one time, but unlinked most to try to uncomplicated things. I didn't initially have Gas bills linked back to projects, but thought maybe it was necessary. I saw no change either way.




Here is what I have on the form right now.

The facility is autopopulating correctly even though it currently displays "2". That is the facility ID, and I'm pretty sure I know how to display as the name instead.
My data is just a sample to see if it's working, and as you can see, it's clearly not. It's still showing data for Poland.


Please any help

[sabrinalynn23]

at least tell me if I'm a total dummy or if it's too much to explain please

with that being said...
update:
I'm trying out Queries with what I've read online.
I seem to be able to get everything filtering properly (without the combo box) but now have my gas bills' meter# still not linked somehow. When I select the facility at first.. the gas bills' meter updates as it should... but this value isn't constrained ( enforce referential integrity I thought took care of this??) Well, what I mean is... I can still change the meter# to one that doesn't exist for that particular facility. Blah! ... I don't even want to see those that don't apply!

this is the mess I have

[June7]

Think I see circular references in table relationships, which might be an issue. Review
http://www.codeproject.com/Articles/...atabase-Design
http://stackoverflow.com/questions/1...le-in-database

If the Facility combobox is used to input filter criteria, it must be UNBOUND.

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

[sabrinalynn23]

I've attached the database. I've got a crap load of forms that I don't need - as I just keep trying different methods. The one to look at would be "this one rocks" ... labeled that when I "thought" it worked.


Thanks

[June7]

Who are you in relation to 'clients'? Are these your meters? What is a 'project'?

I've only ever seen utility billing for my home. I always thought each meter would be on separate billing.

Do you need history of meters associated with facility (a broken meter is replaced and the meter ID then changes)?

The 3rd and 4th level forms don't make sense to me. Could be one form using Continuous View and arranged to look like Datasheet.

What exactly is that unbound combobox supposed to accomplish? If you want its RowSource limited to GasID records associated with the FacilityID then need filter criteria in the RowSource SQL.

When I open [this one rocks] I get error message: "Can't find the field 'Gas Bills Subform4' referred to in your expression - in what expression?

[sabrinalynn23]

A project/client are those we monitor utility usage for. Example - Flb High School. I receive utility bills for all of these projects and currently log them in each of their own (project) excel spreadsheet. This works.. but I think it'd ultimately work better in a database.

What I would like the database to be able to do:
Run weekly reports that can quickly tell me information such as if I haven't received utility bills in the last month (maybe even autogenerate an email message to the client)
Determine which projects may be going over their guaranteed energy usage by so much money
perform the math calculations that excel is doing for us now.
House every project and it's data in one central location instead of separate spreadsheets with an overwhelming amount of tabs
Search for specific information quickly and effortlessly.

Each project/client have more than one utility bill (gas, water, sewer) with various facilities on each bill (high school, middle school, bus garage, etc). Each facility may have more than one meter.

The history I'm looking to gather is more on their costs and usage on each bill. But it is a good point to consider - changing a meter number. I'm sure at times that happens so it should be flexible.

The unbound combobox initially autopopulated the project name in its subform until I realized it would only populate that one field and not all fields in that subform. Somewhere along the line it became broken.

The error message occurred after deleting one of the subforms and I'm not sure where to find that expression to remove it entirely.

At this point I'm not real concerned with the bits and forms that aren't functioning because I realize I've created a disaster here. I just kept trying various things to see what would work and what would not and have started a new clean database. I was able to get my facilities as a subform in my projects and gasbills as a datasheet subform to facilities. All data populates as it should and seems to be linked correctly but when I attempt to then add in the meters field (with an autolookup field) I fail. It doesn't stay constrained to that project and facility.

I'm going to research "continuous view" right now to see what this is all about as I haven't heard of it. I know I probably just have this all set up and wrong and am making it harder than it should be. I took the advice of planning this out before I began to construct.. but have found myself back at that square 1 over and over again and not sure how else to do it. Perhaps return to excel?

If you have any other advice on how this would be better set up I'd appreciate.

[June7]

I suggest that Meters table not link to Projects table. Also suggest that GasBills table only needs the Meter#, not FacilityID or ProjectID as those can be retrieved in queries thru link to MeterID.

Continuous View is an option for form Default View property.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be MeterNum or Meter_Num. Also, no reserved words as names. Name and Type are reserved words.

[sabrinalynn23]

Thanks for the suggestions! I will use them moving forward as I re-create a cleaner (hopefully better) database. Just one clarification please ... If I can get the facility and project IDs from a query of meters wouldn't I have to have at least one or the other in a relationship with meters?

Thanks

[June7]

GasBills table would have MeterID. Meters table would have FacilityID. Facility table would have ProjectID.