Results 1 to 11 of 11
  1. #1
    pinebush is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3

    use VBA to set Option Group default value

    I have an Option Group on which I need to allow the user to reset the default value. This should be done by selecting one of the 5 options, then an On_Click event to reset the default value to the currently selected one. Through VBA I am able to determine the currently selected one, and also determine the current default. However, none of my attempts are able to change the default value in the Form properties window. Therefore, every time I close, then open the form, it loses the 'new' default.
    I have attached a sample database for your review.
    Thank you in advance for your help. I have spend too many hours trying to get this to work.
    Attached Files Attached Files

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,676
    You would have to do that in design view, which is awkward, or impossible if your users are using a runtime version.
    Much better would be to have a (local?) table of user options and store the(their?) default value in that.

    On form open set it accordingly by looking it up.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,557
    In order to change a form or control property and have it stick, you have to put the form into design view. If this setting needs to be user specific, you will have to do something else. On option would be to have a user options table, or if this is likely to be the only option, a field in the user table. Saving the form in code behind the open form doesn't save design changes.

    Dang! too slow again.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,557
    A few minutes to kill while I wait for some wood glue to set so I wanted to play with this a bit. Probably not the way I'd do it because in a split db, the value gets lost every time a new fe is uploaded. If one is sharing the fe (not a good idea) then this could be very wonky. However, if you put the first block of code in a standard module and edit the click event to the 2nd code block, it works. In fact, it's so fast that it appears noting changes. Test that by trying the same value again and see what happens. Note: you can also write the function call as ChangeDefault Me.Name, frameOptGroup
    Code:
    Public Function ChangeDefault(frmName As String, lngDefValue As Long)
    
    DoCmd.Close acForm, frmName
    DoCmd.OpenForm frmName, acDesign, , , , acHidden
    Forms(frmName).frameOptGroup.DefaultValue = lngDefValue
    With DoCmd
       .Save acForm, frmName
       .Close acForm, frmName
       .OpenForm frmName
    End With
    
    End Function
    
    Private Sub imgSetDefault_Click()
    Dim intResult As Integer
    Dim strMsg As String
    
    If Me.frameOptGroup.DefaultValue = Me.frameOptGroup Then
       MsgBox "Selected value is already the default!"
       Exit Sub
    End If
    
    strMsg = "Switch default from " & Me.frameOptGroup.DefaultValue & " to " & Me.frameOptGroup & "?"
    intResult = MsgBox(strMsg, vbYesNo)
    If intResult = 6 Then
       Call ChangeDefault(Me.Name, frameOptGroup)
    End If
    
    End Sub
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Give a try to this ―also, very fast― way:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Function DefaultOption() As Long
        DefaultOption = GetSetting(CurrentProject.Name, Me.Name, Me.frameOptGroup.Name & ".DefaultValue", 0)
    End Function
    
    Private Sub Form_Load()
        'Get the default value
        Me.frameOptGroup.Value = DefaultOption
    End Sub
    
    Private Sub imgSetDefault_Click()
        With Me.frameOptGroup
            MsgBox "The current Default is " & DefaultOption & vbCr & _
                   "Currently selected = " & .Value
            'Save the default value
            SaveSetting CurrentProject.Name, Me.Name, .Name & ".DefaultValue", .Value
    
            MsgBox "You have changed the default option to the " & .Value + 1 _
                   & "st radio button", vbInformation + vbOKOnly, "Default Option..."
        End With
    End Sub

  6. #6
    pinebush is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3
    Micron,
    I like it a lot. I'm always amazed when multiple hours of my time can be summarized in about 20 lines of code.
    My actual database is much larger than the sample, and the option group is in a subform, but I was able to tweak the code, and it works wonderfully.

  7. #7
    pinebush is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    3
    Accesstos,
    Immediately after replying to Micron, I noticed your post. I tested it, and in my case, it is actually a smoother method of setting the default. It does not require closing and reopening the form (subform), which avoids the need to rerun a query. As a result, it is indeed very fast. Thanks for your contribution - if it continues to perform throughout my testing, I am likely to go with your suggestion.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,557
    You realize that you are altering the registry with that? Perhaps I'm mistaken and will welcome any correction (I've never used that method).
    In some IT environments, registry hacks are not allowed so it may not work everywhere.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    I don't have experience of strict IT environments, but, SaveSetting() and GetSetting(), are embedded methods of VBA, recomended of MS, that handle the registry on a secured level. So, I would't call it registry hack.

    Anyway, glad you have it resolved,
    John

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,557
    debatable, but I do believe that just about anyone who works in corporate IT would consider a non-standard, unauthorized modification to a registry file to be a "hack". Google 'registry hack' - you'll get millions of results such as "best n registry hacks" to make this or that run better. Seems to be a common characterization of even useful registry modifications. We'll agree to disagree on the term, I guess.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Micron,

    I think that the "registry hack" is just an exaggerated description which ―to this matter― is able to hurt more than the innocuous process that describes is able to do.

    I know that the registry manipulation sounds scary for most of us, but, fortunately, we canít hack (or harm) the registry via the relevant methods of VBA. Maybe they are able to produce to a novice VBA programmer the delusion that he/she manipulates the registry, but nothing worse.

    Those methods, reads and writes only under the key Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings. We can step to any level forwards but nowhere backward. Thatís why I call it "secured level", and, under those conditions, I believe that, they are much safer than any other methods for read/write to disc and they are very useful in several cases. So, I think that is better to face them as permanent TempVars instead of registry hacking.

    If IT administrators does not fall in line with my opinion, this is another issue.

    Cheers,
    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 06-25-2020, 08:42 AM
  2. Replies: 3
    Last Post: 09-27-2018, 11:54 AM
  3. Replies: 6
    Last Post: 07-20-2011, 11:54 AM
  4. option group default values
    By wlumpkin in forum Access
    Replies: 3
    Last Post: 02-15-2011, 03:30 PM
  5. option group default values
    By wlumpkin in forum Access
    Replies: 6
    Last Post: 02-07-2011, 06:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums