Running WHERE clause from two combo boxes

Posted on 2002-06-28
Medium Priority
Last Modified: 2012-05-04
I'm using VB6 against SQL Server 7.
I need help in the sequencing of events for my form.
I have two combo boxes, a textbox, and a DataGrid that gets populated based on the values chosen from the two combo boxes.
One combo box is an agency description, the textbox is an agency code, and the other combobox is a negotiation code.
So, in order to build my sql statement that populates the grid, I have to check two values; the values of each combo box.
Then, I pass that value to the main sql string, (DataInquire function) which populates the grid.
I want to build the full sql string BEFORE I populate the grid; so I want to check the values of each combo box and then run my DataInquire function.
Right now, my DataInquire function is run each time the value of either combo box is changed.
Here's my main procedure which populates the grid:
Public Sub DataInquire()
Dim ctr As Integer
    Select Case CurrentForm
        Case "ModifyCuts"
               SQLData = "Select cuts.id as ID,appropid,agencycode,fundtypecode,programcode,purposecode,objectcode," _
                  & "description,recamt,cashamt,workforce,ftecutcode,subcommcode,negcode," _
                  & "Cuts.RecordStatus as Status, cuts.lastactiondate as lastactiondate" _
                  & " from cuts join Appropriations" _
                  & " on cuts.appropid = Appropriations.id" _
                  & strWHERE _
                  & " order by appropid" & SortBy
        End Select
    If ActiveRs Then RsData.Close
        RsData.Open SQLData, Cn, adOpenForwardOnly, adLockReadOnly
        ActiveRs = True
        If Not (RsData.EOF And RsData.BOF) Then
'GetRows() function automatically ReDims the array
           DataArray = RsData.GetRows()
           For ctr = 0 To UBound(DataArray, 2)
             ucDataGrid1.LoadCutsData DataArray(0, ctr), DataArray(1, ctr), DataArray(2, ctr), _
             DataArray(3, ctr), DataArray(4, ctr), _
             DataArray(5, ctr), DataArray(6, ctr), _
             DataArray(7, ctr), DataArray(8, ctr), DataArray(9, ctr), DataArray(10, ctr), _
             DataArray(11, ctr), DataArray(12, ctr), DataArray(13, ctr), _
             DataArray(14, ctr), DataArray(15, ctr)
           txtDateTime = Format(Now(), "mmmm d,yyyy @ hh:mm AMPM")
           MsgBox "There Are No " & tblMaint & " Records"
           ActiveRs = False
        End If

End Sub
Here's my agency description function which passes a WHERE clause to my main sql string:
Private Sub cmbAgencydesc_Click()
    If cmbAgencyDesc = Space Then
        txtAgencyCode.Text = ""
        ChangeFlag = True
        txtAgencyCode.Text = GetCode("Agencies", cmbAgencyDesc.Text)
        strWHERE = "  WHERE AgencyCode = '" & txtAgencyCode _
            & "' and NegCode = '" & cboNegCode & "'"

    End If
End Sub
And here's my other combo box click event which passes a WHERE clause to my main sql string:
Private Sub cboNegCode_Click()
    strWHERE = "  WHERE AgencyCode = '" & txtAgencyCode _
        & "' and NegCode = '" & cboNegCode & "'"

End Sub
Thanks in Advance.
Question by:jtrapat1
  • 2

Accepted Solution

rpai earned 400 total points
ID: 7116394
What you could do is declare the strWhere variable as Private Module level variable.
Change the SQL statement to something to:-
SQLData = "Select cuts.id as ID,appropid,agencycode,fundtypecode,programcode,purposecode,objectcode," _            & "description,recamt,cashamt,workforce,ftecutcode,subcommcode,negcode," _
                 & "Cuts.RecordStatus as Status, cuts.lastactiondate as lastactiondate" _
                 & " from cuts join Appropriations" _
                 & " on cuts.appropid = Appropriations.id" _
                 & strWHEREClause _
                 & " order by appropid" & SortBy

And then use a Replace function to replace strWhereClause with strWhere created from the click events of one of the combos. Like this:-

SQLData = Replace$(SQLData, strWHEREClause, strWHere)

What is SortBy in the SQLData statement??

Rest of the code looks fine. Do you have any other issues?
LVL 43

Expert Comment

ID: 7116418
Use the .Tag property of each control:


In Form_Load set .Tag property of both combo's and the textbox equal to a default value.

In the change event of each control, check whether the value of each control is the same as the .tag property. If it is then you do not need to run datainquire, if it is different in all cases and not equal to your default value then you must run DataInquire as all values have changed since it was last run. In the DataInquire procedure reset the .Tag property to the new values.

Repeat as necessary.

Here is a simple example:

Private Sub Combo1_Click()
    If Combo1.Text <> Combo1.Tag And Combo2.Text <> Combo2.Tag And Text1.Text <> Text1.Tag Then DataInquire
End Sub

Private Sub Combo2_Click()
    If Combo1.Text <> Combo1.Tag And Combo2.Text <> Combo2.Tag And Text1.Text <> Text1.Tag Then DataInquire
End Sub

Private Sub Form_Load()
    For i = 1 To 10
        Combo1.AddItem "Agency " & i
        Combo2.AddItem "Negotiation" & i
    Combo1.Tag = Combo1.Text
    Combo2.Tag = Combo2.Text
    Text1.Tag = Text1.Text
End Sub

Private Sub Text1_Change()
    If Combo1.Text <> Combo1.Tag And Combo2.Text <> Combo2.Tag And Text1.Text <> Text1.Tag Then DataInquire
End Sub

Private Sub DataInquire()
    Text1.Tag = Text1.Text
    Combo1.Tag = Combo1.Text
    Combo2.Tag = Combo2.Text
    MsgBox "Inquiring on " & vbLf & Combo1.Text & vbLf & Combo2.Text & vbLf & Text1.Text
End Sub

This will only call datainquire function when all three values have changed from the previous call.

Expert Comment

ID: 7116419
Also, while you are here, please attend to your open questions:-
Questions Asked 54
Last 10 Grades Given A A A A A A A A A A  
Question Grading Record 50 Answers Graded / 50 Answers Received


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

597 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