Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Default value not recognized

Copper Contributor

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). 

Jbuff26_0-1655221318378.png

 

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

Jbuff26_1-1655221715577.png

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? 

 

3 Replies

@Jbuff26 

 

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.

 

 

Hi,

 

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

 

Servus
Karl
Access News
Access DevCon

Edit your table and on each field that you want 0 as default, put zero in
Default Value property.