Default Values

[lovelornloser]

I have a form built from a two table query
The first table is an auto update from a linked excel sheet, the second is brings in the additional information.
The problem I'm having is having this additional information have set default values. It appears to work fine if I create a whole new record after the imported, but that makes it semi-redundant as I need this working on the records I import daily. The purpose of having the default value is to save time - 75% of the records will need the default value, whilst the remainder will need to be amended

I figure I might only be able to do this within the form, rather than the query, but it doesn't seem to work with whatever I try. What I have found semi-working, is if I amended one part of the record on the form, the fields I wish to have a default value changes from blank to a 0, though this isn't the default value nor a part of the list (I've tried limiting to list too - no dice)

Please save me from the headache of my staff winging another day

Thanks for your help in advance

 

[Dairy Farmer]

So why not set your default values via VBA.

Form:

AllowAdditions = False

2 buttons on the form:

Button1 imports data
AllowAdditions = True
Import Data
Set blank fields to something
AllowAdditions = False

Button2 adds a new record
AllowAdditions = True
Add new record
Set all fields to something
AllowAdditions = False

 

[lovelornloser]

I have no idea how to work with VBA
Any starting points? I hear it's going to become redundant?

 

[vbaInet]

I think you mean an alternate value because default value (as you have figured out) is for new records.

So what is the criteria? The alternate value should show when nothing is in the field?

Why not "fix" the Excel sheet before importing?

 

[lovelornloser]

The excel doc is a wonderful creation from out information department - a database dump from one of our servers. Limited and locked - they hate me messing about. I have to import it in, unedited - hence the two tables. I also have staff sometimes performing this task, but they get a little confused with copy and paste, I don't want to explain to my manager why there are brain parts all over the walls. So I have to also work with their skills too, especially as doing things like this isn't part of their remit.

The default values are a simple "Yes" or "No"... but not a check box, this is a drop box - this aids in understanding for the staff involved.

 

[vbaInet]

So what determines whether it will be a Yes or a No? Do you have 0s and 1s?

 

[lovelornloser]

I honestly have no idea where the 0s come from, though have a feeling this might be down to choosing the first on the list (Yes) but defining it as a 0. This list only has Yes and No as options, limited to values, set with a default for No

75% of the new records will have the "No" value, hence why I wanted it placed in automatically to save on workflow.

The report is of proposed duplicates in our database; we need to investigate each one and rectify if necessary. From this we can show how much we look into, how many we rectify, and, with the other information provided from our information team, display who the worse offenders are for various divisional meetings which then gets cascaded with, hopefully, appropriate action. (deep breath!).

 

[vbaInet]

So of the newly imported records, ~25% the Yes/No field that require a No "default value" will be blank?

Have you thought about creating an UPDATE query to replace the blanks with No? Or using the Format property of the field?

 

[lovelornloser]

This is a good idea. Rather than working with the idea that access will set a default value, work around the limitations with an update query after the import query. set it to a 1 click macro and the staff won't know any difference other than a little bit of time saved

Many thanks. I'll let you know how I get on

 

[vbaInet]

Correct! Import, then Update.

Keep us informed.

 

[lovelornloser]

Due to having two tables, I had to combine these and create a whole new "super" table.
As a side note, if anyone is trying an update query with two linked tables, even if you try the update within a query joining the two, unless the record is created (in which case, it doesn't create in the second table until information is entered), the update will not work.

So I created a joined table, and reset the layout of my database, merging the information over. A little macro added to run the import and then update query with one click, and it all appears to be working well.

Thanks again for your help, guys

 

[vbaInet]

Good to hear!