Combo to save last used value

Discussion in 'Microsoft Access VBA Modules' started by OssieMac, Sep 7, 2008.

  1. OssieMac

    OssieMac Guest

    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
     
    OssieMac, Sep 7, 2008
    #1
    1. Advertisements

  2. OssieMac

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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
     
    Allen Browne, Sep 7, 2008
    #2
    1. Advertisements

  3. OssieMac

    OssieMac Guest

    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" <> wrote in message
    > news:...
    > > 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

    >
    >
     
    OssieMac, Sep 7, 2008
    #3
  4. OssieMac

    jav Guest

    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" <> wrote in message
    > news:...
    > > 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

    >
    >
     
    jav, Oct 20, 2008
    #4
  5. OssieMac

    OssieMac Guest

    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" <> wrote in message
    > > news:...
    > > > 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

    > >
    > >
     
    OssieMac, Oct 20, 2008
    #5
  6. OssieMac

    Allen Browne Guest

    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" <> wrote in message
    news:...
    > 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, Oct 21, 2008
    #6
  7. OssieMac

    kary

    Joined:
    Nov 8, 2011
    Messages:
    1
    Likes Received:
    0
    A few years on and I have used your code very successfully - many thanks
     
    kary, Nov 8, 2011
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    4
    Views:
    540
    Guest
    Apr 1, 2005
  2. Hermann

    Combo Last value as default / not saved

    Hermann, Jun 30, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    196
    Guest
    Jun 30, 2005
  3. Hermann

    Combo Last value as default / not saved

    Hermann, Jun 30, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    167
    Hermann
    Jun 30, 2005
  4. lgray

    Increment Current Record Value on Basis of Last Records Value

    lgray, Jan 15, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    363
    lgray
    Jan 16, 2009
  5. Kim M.

    string variable value as default value for combo box

    Kim M., Jun 1, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    2,905
    Jack Leach
    Jun 2, 2009
Loading...

Share This Page