saving combo box default value

[Guest]

I’m trying to have the last selected item from a combo box be the default
value for the next new record.

I have the AfterUpdate event routine for the combo box assign the selected
combo item to the default value. The default value is updated, and new
records can be created using its updated value; but if the form is closed and
reopened, the old default value reappears. Consider the following simplified
test:

Create a table (e.g. tblRecords) with the following fields:
1. fld_Id autoNumber key
2. fld_MyColor text

Create a second table (e.g. tblColors) with the following field:
1. fld_Color text key

Populate the tblColors table with some records (e.g. “Redâ€, “Whiteâ€, and
“Blueâ€)

Create a form whose record source is tblRecords.
Set the forms ‘Allow Design Changes’ to ‘All Views’ so that you can view the
Properities window during the test.

Add a combo box (e.g. cboSelectColor) whose record source is tblColors.
For the field’s ‘After Update’ event add the following code:

Private Sub cboSelectColor_AfterUpdate()

MsgBox "Default value was '" & Me.cboSelectColor.DefaultValue & "'
before update."

Me.cboSelectColor.DefaultValue = """" & Me.cboSelectColor.Value & """"

MsgBox "Default value in '" & Me.cboSelectColor.DefaultValue & "' after
update."

End Sub

At this point the form can be saved and opened. Create some records. The
first time a color is selected the 1st msgbox shows ‘’ but afterwards the
last selected item is shown. Close the form, and reopen it. The Default
Value has been lost.

I have tried adding a ‘docmd.save acForm, me.name’ statement; but this
doesn’t cause the Default Value to be saved.

One final note, if a Default Value is manually entered, when the form is
closed, you are asked if you want to save the design. Answering ‘yes’ causes
the Default Value to be saved.

How to fix? Is there a better way to set the Default Value of a combo box?

 

[Allen Browne]

You are correct that the Default Value will not be persistently saved,
unless it is saved in design view.

The usual workaround is to create a table to hold the default value for the
control. Use the Unload event of the form to save the control's Default
Value to the table, and the Load event to retrieve and reassign it.

There is an example of the kind of table you might use to do this. The
example is actually for saving the primary key value and returning there
again later, but the process of saving the value and applying it later is
similar:
http://allenbrowne.com/ser-18.html