Jolien .A Jolien .A - 4 months ago 26
SQL Question

MS Access: Reset input field in form to default value 0 upon exit form

EDIT: The fields shown in pictures below all have "value list" as row source type. The input in these fields are used in queries that pull data from tables as well, but these fields themselves do not use any tables.

I have a form in Access that requires users to provide input via listboxes, and then queries use that data to perform calculations.
However,when the entire access file is closed, the same input values are still in the listbox after reopening.

Ideally I want the values in the input fields to default to 0 (not null) as soon as the access file is closed (or re-opened), so when the user opens the database, he will see the value "0" in those fields. The reason to why I want the value 0 as default is because the inputboxes reflect possible commodities a product can contain, and I want to allow users to define the % commodity a product does or does not contain. 0 means the product does not contain that commodity and the user does not have to change the value in the listbox (more user friendly in my opinion). Picture illustration:

enter image description here

Hence each time I re-open the access file, I keep seeing those input values above, while I want them to be 0.

I did some research but don't really understand how to use the expression builder, and I can't find a way to make it work via a macro. I read something about the listboxes having to be 'bound', but I'm not entirely sure how to deal with this.

This is the above listboxes in design view:

enter image description here

Answer

You are using listboxes but at a height of one selection which is not the optimal use for such a form control as no multiple listing is shown at once together.

Consider using free-form textboxes with a default value of zero. But if you need to control a user's input, use combo boxes which defaults to the zero selection item. Most likely right now, each listbox is defaulting to its first selection item.

Here is how the Property Sheet / Data Tab of the combox would look (in fact Default Value might be redundant as zero is the first item):

Row Source     : 0;20;40;60
Row Source Type: Value List

...


Default Value  : 0

If combo boxes use a table/query row source, you will need to append a value of zero with a UNION query:

Row Source     : SELECT TOP 1 0 AS ColName FROM TableName UNION SELECT Val FROM TableName;
Row Source Type: Table/Query

...


Default Value  : 0