Passing Forms and Controls to a Module

[MykelDL]

After searching various forums, I haven't been able to resolve my challenge.

I have a module where I store all of my navigation buttons, so I don't have to use Access' default form navigations (next, previous, delete, undo, etc.)

For my current New button, the sub is:

Sub NewButton()
DoCmd.GoToRecord, , acNewRec
End Sub

Then, wherever I insert an "Add New" button, the OnClick event code is

NewButton

It's simple. It works. I want more.

What I want to be able to do is push some of the other things that I may do into this module, to keep things consistent across my database. For instance, when the user presses the [ADD] button, I want to:

1. Unlock and enable the pertinent control (I usually don't want them editing. I'm capturing new instances)
2. Change the caption of the "Add" button to "Undo" in case they really don't want to Add
3. Go to a new record (as I had before.)
4. Set the focus on the pertinent control.
5. Reverse everything if the hit the same command to "Undo" or Cancel the Add. (put back the "Add" caption, relock, re-disable.)

What I thought I could do was pass the form and controls to the Sub in the module (which I can), but I can't seem to make use of them.

This is my sub:

Sub AddNewButton (frm as Form, ctl as Control)
If frm.ctl.Locked = True then
    frm.ctl.Locked = False    frm.ctl.Enabled = True
    frm.cmdAddNew.Caption = "Undo"
    DoCmd.GoToRecord , , acNewRec
    frm.ctl.SetFocus
Else
    frm.Undo
    frm.ctl.Locked = True
    frm.ctl.Enabled = False
    frm.cmdAddNew.Caption = "+ADD"
End If

End Sub

In my forms' modules, I was entering

Private Sub cmdAddNew_Click()
    AddNewButton Me, cboVendor
End Sub

I know I can the pass frm and ctl, because when I

debug.print frm.name ctl.name

in my database module, I see that what I was expecting to pass to the module Sub is there.

I appreciate your help in this. I'm either doing something incorrectly, trying to do something I shouldn't, or both.

[CJ_London]

Not clear to me what you are trying to do, you can only add controls in design view, not form view

Your onclick event doesn't look right
1. your newbutton sub needs to be a function
2. you call it from an event with =newButton()

if you want to pass the form as a parameter then it would be =newbutton([Form])

function newbutton(frm as form)

with frm
    DoCmd.GoToRecord, , acNewRec
end with
end function

[MykelDL]

So after struggling with this for hours and posting here, I have a solution. It's not quite as tidy as I wanted, but it works. All that I need to change is the name of the locked control in quotes on the Call.

Here's my Sub:

Sub AddNewButton(frm As String, ctl As String)

With Forms(frm).Controls(ctl)
    If .Locked = True Then
        .Locked = False
        .Enabled = True
        Forms(frm).cmdAddNew.Caption = "Undo"
        DoCmd.GoToRecord , , acNewRec
        .SetFocus
    Else
        Forms(frm).Undo
        .Locked = True
        .Enabled = False
        Forms(frm).cmdAddNew.Caption = "+ADD"
    End If
End With


End Sub

And here's my form's call:

Private Sub cmdAddNew_Click()
    Call AddNewButton(Cstr(Me.Name), "cboVendor")
End Sub

UPDATE: I have changed the Call to:

 Call AddNewButton(CStr(Me.Name), CStr(Me.cboVendor.Name))

so that intellisense helps me to not fat-finger the control's name.

I'll give people a little bit of time to offer some alternatives before I mark this as SOLVED.

[davegri]

This worked for me:

Sub AddNewButton(frm As Form, ctl As Control)


    If frm.Controls(ctl.Name).Locked = True Then
        frm.Controls(ctl.Name).Locked = False
        frm.Controls(ctl.Name).Enabled = True
        frm.Controls("cmdAddNew").Caption = "Undo"
        DoCmd.GoToRecord , , acNewRec
        frm.Controls(ctl.Name).SetFocus
    Else
        frm.Undo
        frm.Controls(ctl.Name).Locked = True
        frm.Controls(ctl.Name).Enabled = False
        frm.Controls("cmdAddNew").Caption = "+ADD"
    End If
End Sub

[Welshgasman]

You do not need the Cstr(), they are already strings?

[moke123]

Another option when looking for reusable code is custom classes.

here's an example of a navbar class.

[MykelDL]

@davegri: Nice. This is slightly tidier on implementing the call. I now have:

With frm.Controls(ctl.Name)
    If .Locked = True Then
        .Locked = False
        .Enabled = True
        frm.cmdAddNew.Caption = "Undo"
        DoCmd.GoToRecord , , acNewRec
        .SetFocus
    Else
        frm.Undo
        .Locked = True
        .Enabled = False
        frm.cmdAddNew.Caption = "+ADD"
    End If
End With

And the call is now:

AddNewButton Me, cboVendor

So, less typing on the call, but I lose the intellisense on the control name.

[MykelDL]

Another option when looking for reusable code is custom classes.

here's an example of a navbar class.

Thanks @Moke123. As you probably figured, I'm purely self-taught, so there's a lot of programming in there that I need to spend time with to understand and how to implement. (e.g., I've never seen "WithEvents" before.)

[davegri]

So, less typing on the call, but I lose the intellisense on the control name.

I included the excess syntax to make it more clear. As far as intellisense, you really only have one variable name to deal with, 'cmdAddNew'. And if the calling form always has that control name, no worries, nothing to change in the module code at all.

[davegri]

[QUOTE]

here's an example of a navbar class.

/QUOTE]

Moke, nice. I'm taking a look at fix to avoid clicking arrows twice at boj or eoj to get the message box.
I like custom navigation for my forms and this could be a very useful time saver for me.

[CJ_London]

seems my post has been ignored

[moke123]

Thanks @Moke123. As you probably figured, I'm purely self-taught, so there's a lot of programming in there that I need to spend time with to understand and how to implement. (e.g., I've never seen "WithEvents" before.)

Actually there is barely any coding needed to impliment. Thats the beauty of classes.
Just drop the sfrmNavBar as a subform in your form. You'll notice there is no code at all in sfrmNavBar. Its all handled by the class.
Only code needed in your form is

Dim clsP As New clsNavBar


Private Sub Form_Load()
clsP.InitNavClass Me
End Sub

Thanks Dave for the absolute position fix. I'll take a look when I get home.

[accesstos]

Actually there is barely any coding needed to impliment. Thats the beauty of classes.
Just drop the sfrmNavBar as a subform in your form. You'll notice there is no code at all in sfrmNavBar. Its all handled by the class.
Only code needed in your form is

Dim clsP As New clsNavBar


Private Sub Form_Load()
clsP.InitNavClass Me
End Sub

Thanks Dave for the absolute position fix. I'll take a look when I get home.

It's not necessary a separate class module for the functionality of the sfrmNavBar.
Put the code below in sfrmNavBar's code module and put the sfrmNavBar in any form and play. No code needed in parent form.

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Me.Caption = "Navigation Bar"
End Sub

Private Sub BFirst_Click()
    On Error GoTo ErrHandler
    Me.Parent.Recordset.MoveFirst
ExitHere:
    Exit Sub
ErrHandler:
    HandleError "Go To First"
    Resume ExitHere
End Sub

Private Sub BLast_Click()
    On Error GoTo ErrHandler
    Me.Parent.Recordset.MoveLast
ExitHere:
    Exit Sub
ErrHandler:
    HandleError "Go To Last"
    Resume ExitHere
End Sub

Private Sub BNewRec_Click()
    On Error GoTo ErrHandler
    Me.Parent.Recordset.AddNew
ExitHere:
    Exit Sub
ErrHandler:
    HandleError "Go To New"
    Resume ExitHere
End Sub

Private Sub BNext_Click()
    On Error GoTo ErrHandler
    With Me.Parent.Recordset
        If (.PercentPosition + (100 / .RecordCount)) < (100 - (100 / .RecordCount)) Then
            .MoveNext
        Else
            If Me.Parent.Cycle = 0 Then
                .MoveFirst
            Else
                MsgBox "Your at the last record"
            End If
        End If
    End With
ExitHere:
    Exit Sub
ErrHandler:
    HandleError "Go To Next"
    Resume ExitHere
End Sub

Private Sub BPrevious_Click()
    On Error GoTo ErrHandler
    With Me.Parent.Recordset
        If (.PercentPosition > 0) Then
            .MovePrevious
        Else
            If Me.Parent.Cycle = 0 Then
                .MoveLast
            Else
                MsgBox "Your at the first record"
            End If
        End If
    End With
ExitHere:
    Exit Sub
ErrHandler:
    HandleError "Go To Previous"
    Resume ExitHere
End Sub

Private Sub HandleError(Optional strSource As String)
    Select Case Err
        Case 2452
            MsgBox "This form is for use only as subform within a form!", vbCritical, Me.Caption
        Case 3021
            Beep
        Case Else
            MsgBox strSource & " Error: " & Err & vbCrLf & vbCrLf & Err.Description, vbCritical, Me.Caption
    End Select
End Sub

Cheers,
John

[davegri]

seems my post has been ignored

You're not alone. Happens to me a lot. ��

[MykelDL]

Not clear to me what you are trying to do, you can only add controls in design view, not form view

Your onclick event doesn't look right
1. your newbutton sub needs to be a function
2. you call it from an event with =newButton()

if you want to pass the form as a parameter then it would be =newbutton([Form])

function newbutton(frm as form)

with frm
    DoCmd.GoToRecord, , acNewRec
end with
end function

So as you don't feel ignored: I saw your response and it didn't seem to address my issue, as I didn't see how your you provided a solution to my need to pass the name of any control from any form to a module. Also, stating that I needed a function instead of a Sub was shown to be incorrect, as I am able to do what I wanted without creating a function.