Passing Arguments from VB form to query

[e-girl]

Hi all,

I'm designing a form in VB that is supposed to run a query in MS Access that filters the database according to certain criteria, the problem is that there are many criteria that the user may want to filter the database according to, so I had to make a different query for each criterion and a specific command button in VB that runs that query. But this means a very large number of queries which is just not professional, so I was just wondering if there is a way to pass the arguments from the command buttons in the VB form to only one query that would filter the database according to these arguments.

Thank you very much

[[A51g]Static]

best way to do this:

Create an array of control (Textboxes? Combos?) however you want to allow the user to enter criteria:

put the Table Field Name in each of the controls TAG property...
loop through the Objects and build a "WHERE" string using the obj.Tag & "=" & obj.text

get it?? ive done it on many occasions its very handy..
you may need to add a flag to the tag wether the field is TEXT or NUM
like TAG = "T|FieldName"
then Split by "|" .. if T then TEXT etc...

[e-girl]

thank you very much, I'm not sure i got your point perfectly though, so could you please explain more, cuz I'm a beginner at VB

your help is very much appreciated

[[A51g]Static]

ok.. actually i explained a bit wrong.. you dont want an array of control.. but

1) Create a control for each field in the table that you want available in your where clause
(If you use comboboxes.. you could even pre-fill all the available choices)
2) loop through checking each control for a value.. if there.. add to the where clause

here is an example I did for someone else:

http://www.vbforums.com/showthread.p...=Complex+Query

read the whole post.. since there was a lot of back and forth question/answers

(And Btw... I'll probably get kicked/banned from here for saying this.. but vbforums is THE best place for help.. Very quick.. tons of members.. etc go Join them!! its free)

[e-girl]

thank you very much, will check that..

and thanks for the advice

[[A51g]Static]

your welcome! (See you over there lol)

[doofusboy]

Here's an example of using up to 4 ComboBoxes which allow user to define criteria for WHERE clause in your SQL statement:

Option Explicit
Private Sub Command1_Click()
Dim strSQL As String
Dim Where As String
Dim ctl As Control

strSQL = "SELECT * FROM tblTest"
For Each ctl In Me.Controls
    If TypeOf ctl Is ComboBox Then
        If ctl.List(ctl.ListIndex) <> "" Then
            If IsNumeric(ctl.Text) Then
                Where = Where & ctl.Name & "=" & ctl.Text & " AND "
            Else
                Where = Where & ctl.Name & "='" & ctl.Text & "' AND "
            End If
        End If
    End If
Next

If Len(Where) <> 0 Then
    strSQL = strSQL & " WHERE " & Left(Where, Len(Where) - 5) & ";"
Else
    strSQL = strSQL & ";"
End If
Text1.Text = strSQL

End Sub

Private Sub Form_Load()
Field1.AddItem "A"
Field1.AddItem "B"
Field1.AddItem "C"
Field1.AddItem "D"
Field2.AddItem "1"
Field2.AddItem "2"
Field2.AddItem "3"
Field2.AddItem "4"
Field3.AddItem "A"
Field3.AddItem "B"
Field3.AddItem "C"
Field3.AddItem "D"
Field4.AddItem "1"
Field4.AddItem "2"
Field4.AddItem "3"
Field4.AddItem "4"

End Sub

[[A51g]Static]

nice doofusboy! excellent job taking the code off the other forum and posting it as your own.. I appreaciate the credit you gave me.