PC Review


Reply
Thread Tools Rate Thread

Combo to save last used value

 
 
OssieMac
Guest
Posts: n/a
 
      7th Sep 2008
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.

--
Regards,

OssieMac
 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      7th Sep 2008
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.

--
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.

"OssieMac" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Regards,
>
> OssieMac


 
Reply With Quote
 
 
 
 
OssieMac
Guest
Posts: n/a
 
      7th Sep 2008
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" 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.
>
> --
> 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.
>
> "OssieMac" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > --
> > Regards,
> >
> > OssieMac

>
>

 
Reply With Quote
 
jav
Guest
Posts: n/a
 
      20th Oct 2008
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.
>
> --
> 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.
>
> "OssieMac" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > --
> > Regards,
> >
> > OssieMac

>
>

 
Reply With Quote
 
OssieMac
Guest
Posts: n/a
 
      20th Oct 2008
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" wrote:

> 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.
> >
> > --
> > 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.
> >
> > "OssieMac" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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.
> > >
> > > --
> > > Regards,
> > >
> > > OssieMac

> >
> >

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      21st Oct 2008
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
New Member
Join Date: Nov 2011
Posts: 1
 
      8th Nov 2011
Quote:
Originally Posted by Allen Browne View Post
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


A few years on and I have used your code very successfully - many thanks
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Combo box value determined by the value of another combo box fredg Microsoft Access 10 27th Apr 2011 12:03 AM
VBA IIf(myCell.Value = Array(myCell2(1).Value, myCell2(2).Value,myCell2(3).Value,myCell2(4).Value) ,"OK","No Match) EagleOne@discussions.microsoft.com Microsoft Excel Programming 2 24th Apr 2009 11:25 PM
Why is the last used cell being used? Lostguy Microsoft Excel Discussion 1 9th Feb 2009 02:54 AM
Making the last value entered the default value in a combo box Degger Microsoft Access Form Coding 1 2nd Oct 2004 07:43 PM
Combo Value Depending On Other Combo Value Daniel Microsoft Access Forms 2 28th Jan 2004 11:47 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:48 PM.