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

Running WHERE clause from two combo boxes

Posted on 2002-06-28
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 100 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

860 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