Solved

Running WHERE clause from two combo boxes

Posted on 2002-06-28
3
168 Views
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
    ucDataGrid1.Clear
    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
           RsData.MoveFirst
'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)
           Next
           txtDateTime = Format(Now(), "mmmm d,yyyy @ hh:mm AMPM")
           ucDataGrid1.SelectFirst
        Else
           MsgBox "There Are No " & tblMaint & " Records"
           RsData.Close
           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 = ""
    Else
        ChangeFlag = True
        txtAgencyCode.Text = GetCode("Agencies", cmbAgencyDesc.Text)
        strWHERE = "  WHERE AgencyCode = '" & txtAgencyCode _
            & "' and NegCode = '" & cboNegCode & "'"

        DataInquire
    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 & "'"

    DataInquire
End Sub
------------------------------------------------------
Thanks in Advance.
John  
0
Comment
Question by:jtrapat1
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
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?
0
 
LVL 43

Expert Comment

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

e.g.,

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
    Next
    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.
0
 
LVL 5

Expert Comment

by:rpai
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

Thanks!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

679 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