Combo to save last used value

  • Thread starter Thread starter OssieMac
  • Start date Start date
O

OssieMac

Is it possible to save the last used value of a combobox so that the next
time the database is opened, the last used value for the combo becomes the
default value?

I thought that I would be able to save it to the default value property but
it doesn't work.
 
Access won't remember the DefaultValue between sessions (unless it's
assigned in design view.)

So:
1. Use the AfterUpdate event of the combo to assign its DefaultValue.

2. In the Unload event of the form, save its DefaultValue into a table.

3. In the Load event of the form, read the value from the table, and assign
it to the combo's DefaultValue.
 
Thankyou Allen. Works perfect. Thought I might have to resort to something
like that but first I wanted to confirm that there is no way of having Access
remember the default value (both for this exercise and for my continued
learning). The user will be super pleased because she can return to the point
where she left off.

--
Regards,

OssieMac


Allen Browne said:
Access won't remember the DefaultValue between sessions (unless it's
assigned in design view.)

So:
1. Use the AfterUpdate event of the combo to assign its DefaultValue.

2. In the Unload event of the form, save its DefaultValue into a table.

3. In the Load event of the form, read the value from the table, and assign
it to the combo's DefaultValue.
Click to expand...
 
Hi Allen,

I'm a newb and was wondering if you could you provide a little more detail
regarding how to actually execute those 3 steps? i.e. How do I actually
assign a defaultvalue using the afterupdate event, how to save the
defaultvalue into a table using the unload event, read value from table and
assign it to the combo's default value.



Allen Browne said:
Access won't remember the DefaultValue between sessions (unless it's
assigned in design view.)

So:
1. Use the AfterUpdate event of the combo to assign its DefaultValue.

2. In the Unload event of the form, save its DefaultValue into a table.

3. In the Load event of the form, read the value from the table, and assign
it to the combo's DefaultValue.
Click to expand...
 
Hi jav,

I realize you directed this to Allen but as I developed the following as a
result of his help I thought I should share it with you. Allen might even be
able to show both of us a better way.

I created a table with three fields. One for each value to be saved. (The
table needs to be created first or the Open event will error.) I then created
the following subs for the Form Open event and the Form close event.

While in the VBA Editor you will need to Select Tools -> References and then
check the box against Microsoft DAO 3.6 Object Library. (Ensure you check the
box and not just select the line before clicking OK.)

Private Sub Form_Open(Cancel As Integer)

Dim rsCurrent As DAO.Recordset

'Copy the last ComboBox settings back to the ComboBoxes
Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT StartNumber,EndNumber,RecipContactFilter " & _
"FROM [View_Edit Receipt Default Filters]")

With rsCurrent
.MoveFirst
Me.StartNo = .Fields("StartNumber")
Me.EndNo = .Fields("EndNumber")
Me.RecipContactFind = .Fields("RecipContactFilter")
End With

rsCurrent.Close
Set rsCurrent = Nothing

Me.Requery

End Sub





Private Sub Form_Close()

'Save the ComboBox Settings to use as defaults at next form open.
Dim rsCurrent As DAO.Recordset

Set rsCurrent = CurrentDb.OpenRecordset _
("SELECT StartNumber,EndNumber,RecipContactFilter " & _
"FROM [View_Edit Receipt Default Filters]")

With rsCurrent
.MoveFirst
.Edit
.Fields("StartNumber") = Me.StartNo
.Fields("EndNumber") = Me.EndNo
.Fields("RecipContactFilter") = Me.RecipContactFind
.Update
End With
rsCurrent.Close
Set rsCurrent = Nothing

End Sub


Hope it helps. Feel free to get back to me if you have any problems with it.


--
Regards,

OssieMac


jav said:
Hi Allen,

I'm a newb and was wondering if you could you provide a little more detail
regarding how to actually execute those 3 steps? i.e. How do I actually
assign a defaultvalue using the afterupdate event, how to save the
defaultvalue into a table using the unload event, read value from table and
assign it to the combo's default value.
Click to expand...
 
In form design view, right-click the combo and choose Properties.

On the Event tab of the Properties sheet, set
After Update [Event Procedure]

Click the Build button (...) beside that.
Access opens the code window.

Between the "Private Sub..." and "End Sub" lines, enter this (substituting
your combo name for Combo1):

With Me.Combo1
If Not IsNull(.Value) Then
.DefaultValue = """" & .Value & """"
End If
End With

That's the first step. For an example of how to save a value to a table when
the form closes and assign it again when it opens see
Return to the same record next time form is opened
at
http://allenbrowne.com/ser-18.html
The example does something different (finding a record again), but the
process of saving and assigning the value is similar.
 
Allen Browne said:
In form design view, right-click the combo and choose Properties.

On the Event tab of the Properties sheet, set
After Update [Event Procedure]

Click the Build button (...) beside that.
Access opens the code window.

Between the "Private Sub..." and "End Sub" lines, enter this (substituting
your combo name for Combo1):

With Me.Combo1
If Not IsNull(.Value) Then
.DefaultValue = """" & .Value & """"
End If
End With

That's the first step. For an example of how to save a value to a table when
the form closes and assign it again when it opens see
Return to the same record next time form is opened
at
http://allenbrowne.com/ser-18.html
The example does something different (finding a record again), but the
process of saving and assigning the value is similar.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jav" <[email protected]> wrote in message
news:[email protected]...
> Hi Allen,
>
> I'm a newb and was wondering if you could you provide a little more detail
> regarding how to actually execute those 3 steps? i.e. How do I actually
> assign a defaultvalue using the afterupdate event, how to save the
> defaultvalue into a table using the unload event, read value from table
> and
> assign it to the combo's default value.
>
>
>
> "Allen Browne" wrote:
>
>> Access won't remember the DefaultValue between sessions (unless it's
>> assigned in design view.)
>>
>> So:
>> 1. Use the AfterUpdate event of the combo to assign its DefaultValue.
>>
>> 2. In the Unload event of the form, save its DefaultValue into a table.
>>
>> 3. In the Load event of the form, read the value from the table, and
>> assign
>> it to the combo's DefaultValue.
Click to expand...
A few years on and I have used your code very successfully - many thanks
 
Back
Top