Jun 14 2022 08:53 AM
Jun 14 2022 08:53 AM
I will preface by acknowledging the amount of code written to accomplish a very simple task is likely sloppy and entirely unnecessary, but I'm very new and entirely self taught by forums such as these. With that said, here is my issue:
I have created a form that tracks when employees are observed under certain safety principles. To try and keep it as simple as possible for data entry I have two fields. A display field that shows the current record (left side) and a data entry field that manipulates the record (Right Side, all fields have "add" at the end).
If I want to add that they were observed on a principle, lets say its "Passing", then I select the drop down at "Pass Add" and select the "1" in the drop down menu. This then triggers a macro to update the record. The code for this is pretty simple and works great
To make this happen however, I had to populate all the fields in the corresponding table with a "0". I found then I had to have a way to add employees to the record set on the fly so I created a quick data entry form that will add a new name to the record set. What I realized was that the 0's on the new employees weren't entered and thus the macro would not work on them. I changed the default value to "0" on the fields but it still doesn't work. The only way it works is if an actual 0 is entered for all blank fields. Why is this?
Jun 14 2022 01:22 PM
This is probably not the answer you want, but from what I can see in the screen shot and your description, the problem is actually an inappropriate table design for a relational database application.
Your table appears to have multiple columns--like a spreadsheet would--for things like "Passing" and "Body Awareness" and so on. These are all attributes of one kind of thing, not separate kinds of things.
As a general guess, I'd say that thing is "Safety Principles", of which "Bend at Knees", "Move Feet" and so on are different aspects, or attributes.
You have a set of additional fields, paired with these, which accept "Yes" or "No" or perhaps "1" or nothing.
This approach is, unfortunately, all too common among people new to relational database applications, but it creates hurdle after hurdle when it comes to data entry, as you have found.
The overall guideline is that tables should be "tall and skinny" not "short and wide". Multiple columns like yours create short, wide tables.
Rather than repeat a lot of these principles here, I'm going to suggest you study the series of articles in this blog which explain the design problem in more detail and also guide you through the necessary redesign to make this work.
Among other things, you'll learn that you do not have to seed the record with all those 0 values along with all the hassle that creates. Instead, you simply add one new record each time an employee passes one of the criteria for a specific safety principle on which they are being observed.
Jun 15 2022 01:23 AM
If I understand your problem correctly then (apart from all that George wrote ;) your current obstacle is that you have or want to use 0 as default value because otherwise with your line of code the result is still empty. If that's the case, then you don't need a default value but can use:
Me!Passing.Value = Nz(Me!Passing.Value) + Me!PassAdd.Value