• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

design question on forms

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.
  • 6
  • 3
  • 3
  • +3
1 Solution
jrspanoAuthor Commented:
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.
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
jrspanoAuthor Commented:
wouldn't that still require a form for each table?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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...
jrspanoAuthor Commented:
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.
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)
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.
jrspanoAuthor Commented:
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.
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.
jrspanoAuthor Commented:
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!!
Richie_SimonettiIT OperationsCommented:
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

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.
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

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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now