-
Passing Arguments from VB form to query
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
-
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...
Play BF2? Looking for a Clan? Come Join us!

-
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
Last edited by e-girl; 10-14-2005 at 11:22 AM.
-
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)
Play BF2? Looking for a Clan? Come Join us!

-
thank you very much, will check that..
and thanks for the advice
-
your welcome! (See you over there lol)
Play BF2? Looking for a Clan? Come Join us!

-
Here's an example of using up to 4 ComboBoxes which allow user to define criteria for WHERE clause in your SQL statement:
HTML Code:
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
-
nice doofusboy! excellent job taking the code off the other forum and posting it as your own.. I appreaciate the credit you gave me.
Play BF2? Looking for a Clan? Come Join us!

Similar Threads
-
By Bill Gaddam in forum VB Classic
Replies: 10
Last Post: 11-06-2007, 11:15 AM
-
Replies: 20
Last Post: 09-22-2002, 11:54 PM
-
Replies: 0
Last Post: 04-16-2002, 04:44 PM
-
By bobby in forum VB Classic
Replies: 6
Last Post: 07-30-2001, 04:13 PM
-
By James in forum VB Classic
Replies: 0
Last Post: 07-01-2000, 03:44 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|