Many here cannot or will not use any sort of drop box. With so few forms and tables, you probably didn't compact the db first, then zip it. Hard to believe it would be that size if you had (not that 5Mb is large, just that yours shouldn't be).
I did download it using my internet computer (computer easily re imaged if something bad happens - all ok this time).
The download size was ~ 9mb. I did a C&R and the size dropped to ~6.5mb. Zipped it is 5.35mb.
I deleted the picture on the main form - not much change in file size.
Only 436 records total.
Don't know why so large.
No reserved words, special characters, spaces, punctuation, Look up fields, attachments, calculated fields, or MVF. PK/FK fields identified with suffix.....
Only 1 query, 1 form and 1 report with spaces in name.
I table "BuildingCurrent", there is a field (SqFt2010Survey) with a year embedded in the name. What happens if another survey is taken in 2019? Are you going to add another field?
Instead, I would have 2 fields: "SqFtSurvey" and "SurveyYear".
Over all, pretty good. I don't use/like the picture as the form background because it increased file size.....but I have seen way worse.. one (or more) were mine...
And as Micron stated, you need to have a better method of selecting the buildings to preview/print.
So a cursory look. Seems the form pic adds 2.3 Mb.
- By using a DISTINCT predicate on the combo row sources, you cannot get records where the building name is duplicated. Why have duplicate property names and then exclude them from the report? (see Ironwood & Cottonwood).
- you don't get Cottonwood in the combo list. Is that because there are no available spaces? Or because it has no spaces in the building at all? This is regardless of using DISTINCT.
- Not sure of what some of the fields mean, but I'm thinking there should be a table for building data and one for all the spaces for each contained therein. While a checkbox or similar field could be used to flag those that are available, sometimes date fields are more useful.
- suggest no special characters in data (e.g. Café)
- usually you would not repeat data as a page header as well as the first record in the details in a report
- don't show data that is not useful to the user (e.g. PK field). It is OK when developing of course, but not for the finished product. These fields should be hidden.
I'm thinking for your case that a multi select list box might be your best bet for now. When the button is clicked, a code loop iterates over the selected items and builds a sql statement for the report. There are possible issues (noted above re the list items); otherwise I would have gone ahead with that.
Last edited by Micron; 06-01-2018 at 07:50 PM. Reason: clarification
If you need to clear all Comboboxes on the Form, using a Command Button, in the button's OnClick event you can use this code, without actually having to name each Combobox:
Code:Dim ctl As Control For Each ctl In Me.Controls If ctl.ControlType = acComboBox Then ctl = Null End If Next ctl
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
Thanks everyone, I think a weekend off was what I needed. I now have a button (btnRefreshAll) that now clears all the selections from the ComboBoxes. I'll continue to review the options for the ComboBoxes. I want to be able to quickly assign one or two buildings to student workers, hence the combo boxes. But, I will also need to pull the entire report some times. I will look into the suggestions you all made regarding the list for my building names.
I wanted something that looks nice for the boss and students that will be pulling reports (hence the picture - I'll probably take it out but it did go over well during a meeting - so maybe not).
I am NOT a db person. I'm a Sustainability Coordinator that happens to LOVE Excel. However, I felt the scope of the project warranted the functionality of a database. I used a teeny, tiny bit of SQL back in the early 2000's but never built a db myself. I do need a LOT of VBA tutorials.
However, at least I am 75% where I need to be with this project. Tables are created, Relationships are established, and most Reports are ready. Now, I just need to complete the inventory and I'm well on my way. Ya'll are the best!