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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…

785 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