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 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 =" _
                  & 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 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 =" _
                 & 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now