none
Use one VBA sub to edit another RRS feed

  • Question

  • I currently have a split database set up to track devices while they are being repaired, and the status of their loaners.  This is being used by multiple users, at multiple locations.  I have set a general default subform, for each site (i.e. some sites prefer to view as a form, while others prefer to view as a datasheet).  Of course, each user has their own preference of how they work with the data, so instead of saying building KR has to work with data in a form view (as default), and building KL has to view all of their defaults as a datasheet (everyone is able to change the view once they reach their default subform, but there's nothing wrong with providing a little bit of options (maybe)). 

    So, my question is:  Is it possible (and if so, how), to have one sub call another (both on the same form), and edit its data.

    For example:

    If (Building Like "KR") Then
            If (NavigateTo Like "Swap Form") Then
                DoCmd.BrowseTo acForm, "Swap KR", "Swap.NavigationSubForm>Swap KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-In Repair") Then
                DoCmd.BrowseTo acForm, "In_Repair_KR", "Swap.NavigationSubForm>In_Repair_KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-In Repair/Repaired") Then
                DoCmd.BrowseTo acForm, "Repair(ed) KR Datasheet", "Swap.NavigationSubForm>Repair(ed) KR Datasheet.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-Repaired") Then
                DoCmd.BrowseTo acForm, "Repaired_KR", "Swap.NavigationSubForm>Repaired_KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-Returned") Then
                DoCmd.OpenForm "Returned KR Datasheet", acFormDS, "", "", , acDialog
            ElseIf (NavigateTo Like "Withdrawn") Then
                DoCmd.BrowseTo acForm, "Withdraw KR", "Swap.NavigationSubForm>Withdraw KR.sfrmChild", "", "", 1
            End If

    If I'm using the information above and, let's say, User A would prefer to work with "Withdraw KR" as a datasheet, by default, is there a way to write it up so that a button (let's just say the sub is Default_Click()), so that:

    ElseIf (NavigateTo Like "Withdrawn") Then
                DoCmd.BrowseTo acForm, "Withdraw KR", "Swap.NavigationSubForm>Withdraw KR.sfrmChild", "", "", 1

    Can become 

    ElseIf (NavigateTo Like "Withdrawn") Then
                DoCmd.BrowseTo acForm, "Withdraw KR Datasheet", "Swap.NavigationSubForm>Withdraw KR Datasheet.sfrmChild", "", "", 1

    But only make that change if [Building] Like "KR" And [NavigateTo] Like "Withdrawn" And [ViewAs] Like "Datasheet"

    Wednesday, December 6, 2017 6:17 PM

Answers

  • Hi,

    User preferences can be stored in a table to persist them from one user to the next and even from one app version to another. Using a preference table, you can then simply code to check the user preference to open whichever form they preferred.

    But to answer the question of modifying a sub using code, it is possible only in a non-compiled ACCDB file. In other words, the user must be able to go to Design mode because the code will basically be changing the design of the module.

    Otherwise, you can just simply modify the execution (not really modifying the code itself) but using decision branch logics in your code - especially if you use a preference table.

    Hope it makes sense...

    • Marked as answer by bensim123 Wednesday, December 6, 2017 7:49 PM
    Wednesday, December 6, 2017 7:40 PM

All replies

  • Hi,

    When you changed "Withdraw KR" to "Withdraw KR Datasheet," you're not talking about creating two separate forms, were you? You can use code to view the same form in either form or datasheet view.

    Wednesday, December 6, 2017 6:24 PM
  • Unfortunately, yes.  It's how it was originally designed, and I've only recently realized that it's not necessary to have two separate forms, and am in the process of making that change.

    The way it is set up, now, is the user opens the frontend, the main navigational form opens, they select their building from a combo box, and the view that was originally decided, for their site, is opened in the subform.  There are additional combo boxes, used to navigate to the various forms for their site, and loads in the subform.

    If one user prefers to work in a form view, while another as a datasheet, one of the users must change the view, and do this each time they navigate to another form (cleanup to eliminate some of the extra forms is in process)

    I guess that modifies my question, a bit slightly.  Is it possible to allow a user to change what their default view is, and is it possible to allow one sub to edit another?

    They understand that, as the frontend is updated, they may need to change their settings again, but this interruption is foreseen to be minimal in the long run.

    Wednesday, December 6, 2017 7:25 PM
  • Hi,

    User preferences can be stored in a table to persist them from one user to the next and even from one app version to another. Using a preference table, you can then simply code to check the user preference to open whichever form they preferred.

    But to answer the question of modifying a sub using code, it is possible only in a non-compiled ACCDB file. In other words, the user must be able to go to Design mode because the code will basically be changing the design of the module.

    Otherwise, you can just simply modify the execution (not really modifying the code itself) but using decision branch logics in your code - especially if you use a preference table.

    Hope it makes sense...

    • Marked as answer by bensim123 Wednesday, December 6, 2017 7:49 PM
    Wednesday, December 6, 2017 7:40 PM
  • That makes a lot of sense.  Thank you!
    Wednesday, December 6, 2017 7:49 PM
  • Hi,

    You're welcome. Glad we could assist. Good luck with your project.

    Let us know how it goes...

    Cheers!

    Wednesday, December 6, 2017 7:53 PM
  • If (Building Like "KR") Then
            If (NavigateTo Like "Swap Form") Then
                DoCmd.BrowseTo acForm, "Swap KR", "Swap.NavigationSubForm>Swap KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-In Repair") Then
                DoCmd.BrowseTo acForm, "In_Repair_KR", "Swap.NavigationSubForm>In_Repair_KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-In Repair/Repaired") Then
                DoCmd.BrowseTo acForm, "Repair(ed) KR Datasheet", "Swap.NavigationSubForm>Repair(ed) KR Datasheet.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-Repaired") Then
                DoCmd.BrowseTo acForm, "Repaired_KR", "Swap.NavigationSubForm>Repaired_KR.sfrmChild", "", "", 1
            ElseIf (NavigateTo Like "-Returned") Then
                DoCmd.OpenForm "Returned KR Datasheet", acFormDS, "", "", , acDialog
            ElseIf (NavigateTo Like "Withdrawn") Then
                DoCmd.BrowseTo acForm, "Withdraw KR", "Swap.NavigationSubForm>Withdraw KR.sfrmChild", "", "", 1
            End If

    Hi bensim,

    Instead of hard-coding all possibilities, you could also write these in a table. Depending on the choices the right subform-name is selected for use in the BrowseTo command. Changing a default is just changing the subform-name in the table.

    Imb.

    Wednesday, December 6, 2017 7:56 PM