Default value for lookup in Main Form

[jessicaford]

Hello,



Hoping someone can help me as I am seriously struggling.

I have a form which has a number of lookup fields which MUST be completed before moving on to the next record. I would like the lookups which require completion to be set as 'Not applicable' (which is an option in the lookup table) as the default value when a new record is started. HOW DO I DO THIS?!?!!?

Any help would be greatly received.

Many thanks

Jess

[Jim Connell]

In order for 'Not applicable' to show up in your drop down it must be an element in your list. When the user goes to save you would then check (via code) to make sure that no element has that value.

You need to set the default for each combo box = 'Not applicable'

To get the element into your lookup list you have two options;
1. Explicitly add it to the list or
2. Create a union query that adds 'Not applicable' to your drop down.

If you need help to create the Union query check out the following Access Wizard Articles

http://custom-software.biz/AccessWizard/Mar06.htm
http://custom-software.biz/AccessWizard/July05.htm

Hope this Helps

Jim Connell
http://custom-software.biz

[jessicaford]

Hello Jim,

Thanks for replying. Not applicable is already in the lists however whenever I try to set the default value to ='Not applicable' it says there is an error. I have tried putting an expression together but it says there is an error in the syntax.

Jess

[rpeare]

if your list of values has a PK (primary key) and a description i.e.:

Opt_ID  Opt_Desc
1       N/A
2       Description 1
3       Description 2

If you are using a BOUND form you must populate the option field with the Opt_ID field, not the description.

Similarly, if you are using an UNBOUND form you must look up the value for N/A and populate it before appending the record.

Just be aware, if you are using a BOUND form (a form tied directly to a table or query) if you populate the value as soon as you get to a new record a new record is created that will essentially be blank with the exception of the fields you've forced to have a default value. In other words, I would not populate the 'blank' fields with N/A until you navigate away from the record rather than as soon as you open the record.