Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


design question on forms

Posted on 2001-06-21
Medium Priority
Last Modified: 2013-12-03
I need some opinions about form design.  What i want to do is have a single form that replaces about 30 of them.  what the form does is show data from a table in a db based on user input.  Basically it is a search form.
you have table a
forma searches table a and returns data.  you might have 2 txt boxes and a sheridan data combo.

table b
this form, formb, has 5 txt boxes, a combobox, and a data combo

each form has to generate different sql depending on the input.  Basically i want to have a form in a dll with a class wrapper or similar and have that one form show what i need it to.  I have though about using meta data.  I would rather have the programmers see stuff visually though.  It is hard for someone to do a form nonvisually when they didn't write the code behind it.  I'll give more points to a intuitive solution.  I'm sure someone out there has done this before.
Question by:jrspano
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +3

Author Comment

ID: 6215047
as a further note I am asking this because someone wrote a dll here that has a different form for about 30 tables.  it works great when you open 1 instance of it.  if you have more than one instance of it it messes up because he put a lot of variables in a mod in the dll.  they get overwritten with the new instance of the forms and the old one doesn't work.  I would have to change a Lot of code to fix this, so i'm thinking of rewriting it totally.

Expert Comment

ID: 6215094
Here is how I would do it...

Have two search forms in an ActiveX DLL.  Two class modules to control their behavior.

Here is an old controling class I have used.

Option Explicit

'Private member variables
Dim objForm As frmCustSearchDLG

Public Function ShowCustSearch() As clsCustomer
  Set objForm = frmCustSearchDLG
  objForm.Show vbModal
  If objForm.blnOK Then
    Set ShowCustSearch = objForm.objCust
    Set ShowCustSearch = Nothing
  End If

End Function

Private Sub Class_Initialize()
  Set objForm = frmCustSearchDLG
End Sub

Private Sub Class_Terminate()
  If Not objForm Is Nothing Then
    Set objForm.objCust = Nothing
    Unload objForm
    Set objForm = Nothing
  End If

End Sub

And here is the form code used with it.

Option Explicit

Public objCust As New clsCustomer

Public blnOK As Boolean

Private Sub cmdCancel_Click()
  blnOK = False
End Sub

Private Sub cmdAccept_Click()
  blnOK = True
  Set objCust = ctlCustSearch.CheckedCustomer
End Sub

The only difference here is that on the form I had a UserControl which would perform a customer lookup...

Hope this helps

Author Comment

ID: 6215225
wouldn't that still require a form for each table?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 6215297
You could do it with one form and the two separate views as UserControls...  Then you could load whichever control when a particular class was called...

Just some thoughts...

Author Comment

ID: 6215320
that's a good idea.  I'd like to stay away from user controls though.
some more explanation
I would like to keep each search looking the exact same way also.  same colors, same size controls, same position controls etc.  The ideal way is to let programmers set a few properties of a class and have it generate the search form.

Expert Comment

ID: 6215432
How about you build the form with all possible search options(possibly drive by frames) then show or hide the search fields by either frames or control name(s).

I think if you tried to make it wholly dynamic it will cause more problems than it will solve. (with regards to the entry fields)
LVL 28

Expert Comment

ID: 6215448
something like a dynamic form?  i did something similar once, where one form was responsible for showing different types of data.  i created a "design" table which held the info responible for telling me things like, the caption name to display for a particular field, what sort of data entry it required, etc.  this way my form was essentially blank at design time, and i could build it dynamically based on the results of my initial query.  not sure if thats what you are after though.

Author Comment

ID: 6215504
we're getting closer now.  i would like to do azrasound's idea, but I have a lot of problems with it.  for example i have 1 search that has to change the mousepointer when over a specific txt box.  that would be hard to code.  I have thought of Dave's also but I think it would be messy and programmers wouldn't like to update it.  I have an article i found and am about to read(28 pages though). I think it will help also.  I'll get back with what ever i find in it.  any extensions of these ideas are welcome.  I realize that no solution is perfect, but I would like to get the best one.
LVL 28

Expert Comment

ID: 6215567
well, for example, that could be another attribute you specify in your "Design" table.  it could have a field named "MousePointer" which could hold an integer value of the constant representing the pointer to use.  if it is custom, store 99 and add an additional field with the image path of the icon to use, or, more appropriately, the ID if an image in a resource file.

Author Comment

ID: 6215625
I'll leave this open for a few days as i try different stuff.  Thanks for the help Dave and Azra.  I you think of anything else please let me know!!
LVL 16

Expert Comment

ID: 6215637
LVL 28

Accepted Solution

AzraSound earned 800 total points
ID: 6215664
one last comment, if youre using VB6, and a lot of things that need to be set are particular properties of controls, such as the MousePointer, you can use the CallByName function to automate a lot of the work, e.g.,

if you needed to add a textbox and you had these fields stored in a table:

you could loop through the fields to set all the properties at once:
For Each fld In rs.Fields
   CallByName txtBox, fld.Name, VBLet, fld.Value

LVL 22

Expert Comment

ID: 6216458
This all sounds similar to a project I'm currently working on:

There are several fields (with up to 2 labels) that must be made available depending on which option the user chooses.  Since some of the fields are enumerated, I started adding dropdown lists or textboxes; some fields needed lookups to I added [...] buttons; some were only visible at certain times.

I ended up writing the app as a single input form that was database driven.  The database had all of the parameters that were dynamically read.  If the user changed a dropdown list, it had to re-read because sometimes this opened up new fields or hid other fields.

It got to be a mess until I created my own "multi-input" control that consisted of properties that let me specify whether to show a textbox or dropdown, add a lookup button, and display the two labels.  I also added validation to indicate whether this was a numeric or string text box.

It all came together nicely because the database had everything I needed; the form became a dynamically changed container for the database info.  And for every input required, I merely added one object: MutliInput box.

This may be overkill in your case, but it made it a lot easier to deal with all of the extra components: 2 labels (and potentially different fonts), textbox/dropdown, lookup, height.
LVL 15

Expert Comment

ID: 6217745
Duwamish sample has SearchControl usercontrol (SrchItem.Ctl)

If you have 6 fields, you add 6 SearchControls (3 rows * 2 cols).

- e.g. for Publisher field it will show a textbox and two checkboxes (in a frame)

x Publisher  [                              ]
                       x Match case

            PublisherCtrl.SetCheckTitle ("Publis&her")
            PublisherCtrl.SetCtrlType (0)
            PublisherCtrl.ShowMatchCase True

' other control types (you can add combo as 4th controltype)
            YearCtrl.SetCheckTitle ("&Year")
            YearCtrl.SetCtrlType (2)
            YearCtrl.SetOption1Text ("&Before")
            YearCtrl.SetOption2Text ("E&quals")
            YearCtrl.SetOption3Text ("A&fter")

            PriceCtrl.SetCheckTitle ("P&rice")
            PriceCtrl.SetCtrlType (1)
            PriceCtrl.SetOption1Text ("&Less than")
            PriceCtrl.SetOption2Text ("&Greater than")

ExecSearch() function will fill some objects, then Filter and show records:

    With oForm
        If mbPublisher Then
            setFilterCriteria FILTER_CRITERIA.icPUBLISHER, mbPublisher, .PublisherCtrl.GetText, _
        End If
        If .YearCtrl.GetCheck Then
            If .YearCtrl.GetText <> "" Then
                If Year(.YearCtrl.GetText) Then
                    setFilterCriteria FILTER_CRITERIA.icYEAR, .YearCtrl.GetCheck, _
                            .YearCtrl.GetText, , .YearCtrl.GetOption
                    MsgBox "Type in a valid year in the form yyyy (1990)."
                End If
            End If
        End If

        Set rsTemp = New ADODB.Recordset
        Set rsTemp = FilterRecords(moWorkFlow.CatalogItems, GetFilterCriteria)
        iRecords = 0
        If rsTemp.RecordCount Then
            rsTemp.Sort = "PKId"
            While Not rsTemp.EOF
                If moItemFilter.TitleCheck And moItemFilter.TitleMatchCase Then
                    bHit = False
                    If checkCase(moItemFilter.Title, rsTemp.Fields("Title")) Then
                        bHit = True
                    End If
                    bHit = True
                End If

Function setFilterCriteria() fills moItemFilter class
            moItemFilter.TitleCheck = checked
            moItemFilter.Title = searchText
            moItemFilter.TitleMatchCase = matchCase
            moItemFilter.YearCheck = checked
            moItemFilter.Year = searchText
            moItemFilter.YearMatch = iOption

Function GetFilterCriteria() returns filter part of the SQL statement
    sAnd = " AND "
    GetFilterCriteria = ""
    If moItemFilter.PublisherCheck Then
        If GetFilterCriteria <> "" Then
            GetFilterCriteria = GetFilterCriteria & sAnd
        End If
        If moItemFilter.Publisher <> "" Then
            GetFilterCriteria = GetFilterCriteria & " (Publisher Like '*" & moItemFilter.Publisher & "*')"
        End If
    End If


Author Comment

ID: 6228652
i'm going to try the db example. thanks for everybody's input.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question