Results 1 to 15 of 15
  1. #1
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110

    Changing private sub dim to public sub dim


    I'm trying to move a piece of code I've tested successfully as a private sub into a public sub and am having troubles with the declaration for form.

    Here is the code that worked for the private sub

    Code:
    Private Sub cboRockVendorID_DblClick(Cancel As Integer)    
        'Dim strDocName As String
        'strDocName = "Receipts"
        'DoCmd.OpenForm strDocName
        'If Not IsNull(Me.tboMaterial) Then
        'With Forms(strDocName)
       ' .pgMaterial.SetFocus
       ' End With
        'End If
    End Sub
    Here is what I have in my public module that is not working. There is an error on my form declaration.

    Code:
    Public Sub SubDbl2(form)    
        Dim strDocName As String
        Public strDocName As String
        strDocName = Forms.["Receipts"]
        DoCmd.OpenForm "strDocName", , , "[ID] = " & form!ID
        If Not IsNull(form.tboRockProduct) Then
            With Forms(strDocName)
                .pgRock.SetFocus
            End With
            End If
        If Not IsNull(form.tboMaterial) Then
            With Forms(strDocName)
                .pgMaterial.SetFocus
            End With
            End If
        If Not IsNull(form.tboOutsideService) Then
            With Forms(strDocName)
                .pgOutsideService.SetFocus
            End With
            End If
        If Not IsNull(form.Permit) Then
            With Forms(strDocName)
                .pgPermit.SetFocus
            End With
            End If
    End Sub
    How can this be changed to get it to work? I'm sure I'm missing something simple.

    Thank you for looking.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Is this code in a general module?

    Can't declare the same variable name twice within a procedure. I think if you want a variable to be public so it can be referenced by multiple procedures, it must be declared in module header. If it is declared in general module header, the variable can be referenced from any procedure in the database (global variable). Do you really need strDocName to be public or global? Since your code attempts to declare and set variable in the same procedure, why would it need to be public/global? Is the same variable referenced in another procedure? If variable is declared in header it does not need to be declared again. Form and Forms are reserved words. You should use something besides form as a variable name, such as strForm. Variables do not go within quote marks.
    Code:
    Option Compare Database
    Option Explicit
    Public strDocName As String
    ____________________________
    
    Public Sub SubDbl2(strForm As String)    
        strDocName = "Receipts"
        DoCmd.OpenForm strDocName, , , "[ID] = " & Forms(strForm)!ID
        If Not IsNull(Forms(strForm).tboRockProduct) Then
            Forms(strDocName).pgRock.SetFocus
        End If
        If Not IsNull(Forms(strForm).tboMaterial) Then
            Forms(strDocName).pgMaterial.SetFocus
        End If
        If Not IsNull(Forms(strForm).tboOutsideService) Then
            Forms(strDocName).pgOutsideService.SetFocus
            End If
        If Not IsNull(Forms(strForm).Permit) Then
            Forms(strDocName).pgPermit.SetFocus
        End If
    End Sub
    Show code that is calling the procedure.

    Why do you have multiple forms with the same fields/controls?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    June7,

    Thank you for the response. I'm using this procedure for a double click event in a subform to make sure when a record is double clicked it brings up the correct page in a forms tab control. I would like to put this in a general module to make future editing easier. This procedure will be on all the fields doubleclick event in the subform. This variable (I'm still new to them) will only be referenced in the procedures for this one form.

    I am not sure of the difference between public and global? Still learning all these fun things. Here is some of the code that calls the public sub. They are all similar to these examples.

    Code:
    Private Sub tboLoadsExport_DblClick(Cancel As Integer)    
    End Sub
    
    
    Private Sub tboLoadsImport_DblClick(Cancel As Integer)
        Call SubDbl2(Me)
    End Sub
    
    
    Private Sub tboLoadsOnsite_DblClick(Cancel As Integer)
        Call SubDbl2(Me)
    End Sub
    
    
    Private Sub tboMaterial_DblClick(Cancel As Integer)
        Call SubDbl2(Me)
    End Sub

    As for why I have multiple forms with the same fields/controls? I wish I had a good answer. Based on the question though I assume I need to amend this. I have been trying to make them different, but have not yet made good practice on my naming.

    I used your fixes for my code and have gotten the error, Type mismatched.

    Once again, thank you for your help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Type mismatch on what line?

    By referencing Me I guess you are trying to pass form object, not form name. I've never done that. Then in the public sub would have to declare and set a form object variable. Again, I've never done that.

    If you want to pass form name (a string), use:

    Me.Name
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The following code is extracted from one of my DB's. This is an example of a function in a Standard Module that creates a form object. It uses the name of an open form to create a form object within the Standard Module.

    Some of the code from the function:
    Code:
    Public Function RequiredFields(FormName As String) As Boolean
    
    Dim frm As Form
    Set frm = Application.Forms(FormName)
    And then the call from the form:
    Code:
    If RequiredFields(Me.Name) Then
    
    'Things are OK
    ...
    
    End If

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I am not sure you need a form object.

    More simplified code:
    Code:
    Public Sub SubDbl2(strForm As String)    
        strDocName = "Receipts"
        DoCmd.OpenForm strDocName, , , "[ID] = " & Forms(strForm)!ID
        If Not IsNull(Forms(strForm).tboRockProduct) Then
            Forms(strDocName).pgRock.SetFocus
        ElseIf Not IsNull(Forms(strForm).tboMaterial) Then
            Forms(strDocName).pgMaterial.SetFocus
        ElseIf Not IsNull(Forms(strForm).tboOutsideService) Then
            Forms(strDocName).pgOutsideService.SetFocus
        ElseIf Not IsNull(Forms(strForm).Permit) Then
            Forms(strDocName).pgPermit.SetFocus
        End If
    End Sub
    Still don't understand data structure. If you want to provide db for analysis, follow instructions at bottom of my post.

    Inconsistent naming convention - Permit does not have tbo prefix.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    Sorry I haven't been back on. It's been a busy Sunday.

    June7 I'm attaching a copy of my database. Please know this is my first crack at building a database. I'm happy to have any suggestions you may have. Be careful its a bit of a mess.

    Thank you again for all the help.
    Attached Files Attached Files

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You are trying to set focus on controls in a subform based on values in a main form. Cannot reference a subform as you are attempting. Subform reference path must be through the parent form and its subform container control.

    Why do you need this procedure to be public?

    The main form and subform have the same data source. This seldom works well. This is what the Split form was designed to accommodate.

    I have to say what you are attempting is not practical.

    What is this db for - tracking employee time for jobs? What are MasterTable and MasterTableLog for?

    What is 'Perminiter drain'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    I do not need this to be a public sub. I assumed that was the better place to put it for future editing. I had it working as a private sub, but later moved it to a public. I guess I should move it back.

    What about having a main form and a subform does not work?

    Yes this db is for tracking employee time and jobs for billing as well as keeping budgets on track. I hope to expand its uses in the future. First I have to get this to work. I'm trying to replace an Excel workbook I created that does something similar that we use currently. MasterTable and MasterTableLog are part of the future plans.

    Perminiter is an example of my poor spelling skills. It's intended to be Perimeter Drain. Thank you for catching that.

    Thank you for taking a look. I appreciate the help.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Code in the public sub cannot find the subform because it does not reference through the main form and its subform container.

    Did you originally have the code behind the subform?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    Yes. I had it in each line for the double click event. I assumed there was a smarter way than repeating it for my multiple events. That's what lead me to putting it into a public sub.

  12. #12
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    I've put the following code into a module and everything seems to be working.

    Code:
    Option Compare DatabaseOption Explicit
    Public frmName As String
    
    
    Public Sub SubDbl2(Form)
         
        frmName = "Receipts"
         DoCmd.OpenForm frmName
        If Not IsNull(Form.tboRockProduct) Then
            With Forms(frmName)
                .pgRock.SetFocus
            End With
            End If
        If Not IsNull(Form.tboMaterial) Then
            With Forms(frmName)
                .pgMaterial.SetFocus
            End With
            End If
        If Not IsNull(Form.tboOutsideService) Then
            With Forms(frmName)
                .pgOutsideService.SetFocus
            End With
            End If
        If Not IsNull(Form.tboPermit) Then
            With Forms(frmName)
                .pgPermit.SetFocus
            End With
            End If
    End Sub


    I call it with this

    Code:
    Option Compare DatabaseOption Explicit
    Dim frmName As String
    Dim strForm As String
    
    
    Private Sub cboRockVendorID_DblClick(Cancel As Integer)
        Call SubDbl2(Me)
    End Sub
    
    
    Private Sub Extra_DblClick(Cancel As Integer)
         Call SubDbl2(Me)
    End Sub

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    In what module?

    Well, glad you have a solution but after looking at your code again, I am really confused. The code has a line to open a form (Receipts) that is already open. Why (and your revision removes the filter)?

    I would have expected referencing the subform controls to fail. And also the Me reference to fail but I guess it is successfully passing form object. May have learned something new here.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Stephenson is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    North West
    Posts
    110
    Hey June7,

    I'm sure you're less confused than me, ha.

    I have a new module, mdDoubleClick that I've put this in. My above code post is missing
    Code:
    DoCmd.OpenForm "Receipts", , , "[ID] = " & Form!ID
    I have this so that it will bring the record into the parent form for editing.

    Thank you again for your help.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I've never tried OpenForm against a form that is already open. I did not expect it to work, especially the filter argument. But it does. I am still baffled that referencing controls in a subform in this way is working.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Combining Private Sub
    By Thompyt in forum Programming
    Replies: 2
    Last Post: 11-11-2014, 02:25 PM
  2. Turning Private Function Into Public
    By g4tv4life in forum Programming
    Replies: 1
    Last Post: 02-04-2014, 05:31 PM
  3. Calling fields into VBA Private Sub
    By fullshape in forum Programming
    Replies: 3
    Last Post: 02-18-2011, 09:22 AM
  4. Replies: 9
    Last Post: 12-20-2010, 08:05 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