Custom Control Builder?

[Banana]

I'm wondering if anyone is aware of existence of anything, be it add-in, a module, or whatever that provides two things:

1) Build a group of controls that are in use several times, are similar in functionality. E.g. Cascading Comboboxes or Searchbox- They all are functionally similar, but it's necessary to handcraft each one (though one could just copy the controls and the code behind it, that's still extra work)

2) Automagically update all similar controls in very similar manner to class inheritance- Suppose I made a cascading combobox and later down the road, I discover a bug or realized that my design had changed. So I need to change the code/properties/etc. But suppose I had a bazillion of them all over the place? I'd imagine DLL Hell would be a walk in park after that!

My google wasn't very successful because it gets snowballed with "Access Control" as in security mechanism, rather than a control belonging to a program named Access. Ideally, this functionality would be free, but we'll have to see about that.

 

[georgedwilkinson]

Nifty question. I have only worked with object modules a little. Would something like that fit the bill?

 

[Banana]

Elaborate, please. Not sure what you mean by "object modules"...

The idea would be to have a drag'n'drop functionality for any custom control that I build. We all are already familiar with that toolbar for dropping in a combobox, listbox. I would like to be able to add a Cascading Combobox, and just specify the SQL for each of combobox, and be done with it.

 

[georgedwilkinson]

Sorry, I meant class module. Too wrapped up in my game to give you the attention you deserve.

From 2007 Help:

You can use a class module to create a definition for a new custom object. When you create a new instance of a class, you create a new object and return a reference to it.
Any public procedures defined within the class module become methods of the new object. The Sub statement defines a method that doesn't return a value; the Function statement defines a method that may return a value of a specified type.

Aren't class modules in VB so we can do just this kind of thing? Create custom objects that we can re-use throughout our stuff? Shouldn't you be able to overload an existing VB class, like a form module, or a control module (maybe that's expecting a bit much)?

As usual, the documentation on this stuff leaves a lot to be desired.

Somebody had also asked me once why I didn't create a wizard for something I was asking about. At that time, I was on a tight deadline and didn't have time to follow up. If you can make custom wizards in Office, that might also accomplish your objective.

 

[Banana]

The problem with class module is that they don't actually provide controls- you have to manually tie a control to the class module, then use the class module's interface to provide the desired behavior, which just hurts the performance. Not to mention that you'd still have to make sure that every events raised in a control would call the class module's methods for an expected output. They're dandy for background processing but when it comes to presentation, form design, or that kind of stuff, they're next to useless, unfortunately.

And, no, VBA doesn't support overloading. Furthermore to make matters even weird, form modules aren't true class module- I mean, they may act like one, but one MVP mentioned that it's more like a "object module" in the sense that we're dealing with an object, not a class.

The bitch is that VBA isn't fully OOP- it's object-based, yes, but not truly object-oriented, stopping short of true inheritance and polymorphism.

Custom wizard... I vaguely recall a blurb about that somewhere... lemme see if I can find something. Thanks for the lead.

 

[Banana]

Would anyone else be interested in having this functionality?

 

[Mike375]

Would this tool be of any use under the Personal Object Library

http://www.skrol29.com/dev/en_vtools.htm

 

[Banana]

I actually have that already as well as the MZ-Tool. Nifty tools, but doesn't address the problem of inheritance and doesn't exactly provide a black box (unless I happened to just want a specific subform, then it address the problem just perfectly. MZ-Tools also provide a tool to (copy/paste controls with the code), which is also great, but still doesn't address the inheritance, and I really don't want to "copy" a control, I want to "create" a control, throw in some SQLs for the necessary sources then forget about it. Copying still has that labor of picking through the code and making sure that you have all variables changed for this particular instance.

Not to ding the authors of those two great tools- they sure did everyone a favor, and I appreciate that.

 

[Mike375]

I would like a version of the V Tool that would find and replace field names in macros

What is the MZ tool?

 

[Banana]

This. (You want 3.0 for VBA, and it's free)

 

[Mike375]

Many thanks

 

[Pat Hartman]

I have an app that collects data for a bunch of "transactions" that are sent to another system for processing. Each "transaction" form has the same set of buttons along the bottom of the form. The individual buttons were identically named across all the forms. I ended up creating a common procedure for each button which was called from the click event of the button on the form. As input, the calling procedure passed Me which identified the form and allowed the called procedure to refer to form fields and a couple of other variable pieces of data.

Call CopyTran(Me, IBC123, InstanceID)

Some of the buttons have significant amounts of code behind them. For example, one of the buttons allows the user to close the current instance of a transaction which contains an error so that it was rejected by the down-stream application, and create a clone of the tran so it can be corrected and re-submitted. to accomplish this, 6 tables need to be updated or have rows appended to them so it wasn't code that you would want to have 18 times in your app with only 3 variables being different.

 

[Banana]

Pat,

This is quite an elegant solution- I definitely would want just one instance of code in that situation!

I may be over-engineering here, but when I initially posted here, I was thinking about cascading comboboxes, transferring listboxes (for lack of better name- you know how there are two listboxes, with buttons where you can click on to move one item from left listbox to right listbox?), and then some more which usually are wrapped up in a given form's model. Could that functionality be had using common public procedures? It definitely would work for buttons, no doubt about that, but are there ever instances where code would need to have more context by being embedded in a form module?

My past experiments with using a class module to simplify creating the transferring listbox was perceived as more work than productivity because I still had to manually tie each event to call the given functionality. Using a standard module would do away with the instantiating an object which really isn't that necessary, but the manual labor is still there.

But seeing how MZ-Tools provide a "Copy/Paste Controls with Code", it would mean that I only need to create one instance of control with all events calling a common public procedure in a separate module, that would be quite adequate solution in quickly creating similar controls that will be all over the application.

At least until I run into the problem of discovering a bug or needing extra functionality.

It's too bad that controls' properties collection isn't appendable like other object (I believe one can append new custom properties to AccessObject objects or DAO objects), as that would be perfect place to provide custom functionality. But the idea I've been kicking around is to use the Tag property to identify a control as belonging to a certain "custom controls", and create a procedure where if I need to change the codes, use the Tag property to tell which codes it should have changed.

 

[Banana]

Scratch the comments about using MZ-Tools for copying controls with code:

When I use the "Copy Control(s) With Code" feature with Access I get the error "There is no active designer". Why?

That feature works with forms of Visual Basic, or VBA userforms of Word or Excel ("Insert", "UserForm" menu). On the one hand Access lacks VBA UserForms and on the other hand an add-in for the VBA editor of Access can not access the forms or reports of Access, because they belong to Access itself and MZ-Tools is not an add-in for Access but for its VBA editor.

So... would anyone else be interested in having functionality of automagically creating custom controls?

 

[Pat Hartman]

One of the things I find most aggrevating when copying a control from one form to another is the fact that none of the event procedures are copied. Is that what you mean? You want to create a tool to do that. I would be your first customer The copy would need to start by checking that the control isn't going to duplicate the name of an existing control. If it does, you could prompt for a new name and fix up the event procedure names also.

 

[Banana]

Pat, that'd be the worst offender, yes, and yes, I want to create a tool to fix that problem.

Actually I didn't really think about prompting for name of controls, because I was thinking in terms of a collection of controls.

For an example, cascading comboboxes involves two comboboxes, with an AfterUpdate event. Therefore, I'd create a template that would create two new comboboxes in a given form, copy the events and properties. Each control would have a suffix appended to the end to allow more than one instances on a form, if necessary. The naming would be automated mainly because I want them to act something like objects. If I found a bug in that template, all I should have to do is edit the template, then click a button to update all the copies in the database with the edits.

In short, I want it to act as if it was a class.

 

[Pat Hartman]

Just being able to copy a control with all its event code would be enough for me. There's only a couple of lines of code at most to a cascading combo so I'm not sure how useful a tool that just extended the cascade would be. Especially since each level would refer to a different table and have a different RowSource so there is really limited commonality.

 

[Banana]

Interesting.

My perspective is this:

The only thing that each instance of cascading comboboxes differ is the SQL driving the rowsources and controlsource. Beyond that, they're fundamentally the same in my eyes.

Therefore, the only properties I should ever have to set is the rowsources for the pair and control source for the child combobox (maybe parent combobox, too? Not likely). Beyond that, I don't give a holler what it did between before and after, as long it works.

But it's not just cascading comboboxes. There's search boxes, which, again, are essentially identical except for the data sources.

Finally, the real inspiration was the "transfer listboxes" and multi-select listboxes (I don't know the proper term? Essentially a pair of listbox, one showing all options, and another displaying selected value, with buttons to move values between the pair). My users love those controls, but holy hell, they're a PITA to recreate every time, again the difference being only the data sources.

Not to mention that I've been asked by my users to provide them with this and that extra functionality, which would entail me manually checking every certain groups of controls to extend the functionality. Had this been a class, I would just go to one place and make modification and it'd just work™.

It's just possible that I'm designing forms abnormally and trying too hard to a point where I'm just butting heads with Access, but why deny all Access developers what is generally available to other programmers in other languages?

 

[Banana]

Here's a very simple proof of concept.

I'm afraid I have no idea how to copy a toolbar for sharing- if anyone can show me how, I'll be more than happy to share the toolbar as well. All we really need is just two button on a custom toolbar, one to toggle CopyControl() and another for PasteControl(), a standard module to insert the code.

This code is very very fragile and there's no error handling, but it works given the following conditions:

1) both forms where you copy and paste the control are open in design view; we would have to figure how to identify whether a given form is open in design view or not

2) the control does not depend on any form events, module-level variables or other variables outside of the controls' procedures

3) all procedures associated with control are not Property Get/Let/Set

I may clean it up and fix the shortcomings as described above, and others are more than welcome to contribute.

'// This snippet was written by Banana, according to the thread at
'// http://www.access-programmers.co.uk/forums/showthread.php?t=151534
'// The code may be freely shared, modified, and used.

Private mControl As Control
Private mForm As Form
Private mFormName As String

Property Get srcControl() As Control

Set srcControl = mControl

End Property

Property Set srcControl(ctl As Control)

Set mControl = ctl
Set mForm = ctl.Parent
mFormName = mForm.Name

End Property

Public Function CopyControl()

Set srcControl = Screen.ActiveControl

End Function

Public Function PasteControl()

Dim frm As Form
Dim pasControl As Control
Dim prp As Property
Dim strName As String
Dim strProc As String
Dim strCopy As String
Dim objModule As module
Dim i As Long
Dim var As Variant

Set frm = Screen.ActiveForm

If Not CurrentProject.AllForms(mFormName).IsLoaded Then
    DoCmd.OpenForm mFormName, acDesign, , , , acWindowNormal
End If

strName = InputBox("Name of the pasted control")
Set pasControl = CreateControl(frm.Name, srcControl.ControlType, acDetail, , , 1, 1)

pasControl.Name = strName

'Copy all the properties, with exception for certain properties that shouldn't be copied
For Each prp In pasControl.Properties
    Select Case prp.Name
        Case "Top", "Left", "Name", "EventProcPrefix", "ControlType", "TabIndex", "Section"
            'Skip
        Case Else
            var = srcControl.Properties(prp.Name).value
            If Not IsNull(var) Then
                prp.value = var
            End If
    End Select
Next prp

'Copy all code associated with that control's events
If frm.HasModule = False Then
    frm.HasModule = True
End If

Set objModule = mForm.module

i = 1

Do Until i = objModule.CountOfLines
    If InStr(objModule.ProcOfLine(i, vbext_pk_Proc), srcControl.Name) Then
        strProc = objModule.ProcOfLine(i, vbext_pk_Proc)
        strCopy = objModule.Lines(i, objModule.ProcCountLines(strProc, vbext_pk_Proc))
        strCopy = Replace(strCopy, srcControl.Name, strName)
        frm.module.InsertText strCopy
        i = i + objModule.ProcCountLines(strProc, vbext_pk_Proc)
    Else
        i = i + 1
    End If
Loop

End Function

 

[NigelShaw]

Hi,

i have MZ Tools & Codewiz. MZ tools has a "copy to private clipboard" and a "paste to private clipboard" built in. there are 10 spaces to copy to and a pop up to select the code you need to paste. i find this quite useful.

NS