Passing Arguments from VB form to query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Passing Arguments from VB form to query

Hybrid View

  1. #1
    Join Date
    Oct 2005
    Posts
    3

    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

  2. #2
    Join Date
    Oct 2005
    Location
    Rochester, NY
    Posts
    9
    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!

  3. #3
    Join Date
    Oct 2005
    Posts
    3
    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.

  4. #4
    Join Date
    Oct 2005
    Location
    Rochester, NY
    Posts
    9
    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!

  5. #5
    Join Date
    Oct 2005
    Posts
    3
    thank you very much, will check that..

    and thanks for the advice

  6. #6
    Join Date
    Oct 2005
    Location
    Rochester, NY
    Posts
    9
    your welcome! (See you over there lol)
    Play BF2? Looking for a Clan? Come Join us!

  7. #7
    Join Date
    Aug 2005
    Posts
    51
    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

  8. #8
    Join Date
    Oct 2005
    Location
    Rochester, NY
    Posts
    9
    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

  1. A form passing a parameter to another form
    By Bill Gaddam in forum VB Classic
    Replies: 10
    Last Post: 11-06-2007, 11:15 AM
  2. MMFAN Retires
    By MMFAN in forum .NET
    Replies: 20
    Last Post: 09-22-2002, 11:54 PM
  3. Replies: 0
    Last Post: 04-16-2002, 04:44 PM
  4. Scrolling a VB form,
    By bobby in forum VB Classic
    Replies: 6
    Last Post: 07-30-2001, 04:13 PM
  5. 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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center