Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL parameters VBA (2000/2002)

    I am trying to set the row source of a series of combo boxs in vba, where the value chosen in the 'parent' of a particular combo become the value used in the WHERE clause for the row source sql statement.

    I've done this before and had it working the way I wanted.

    The difficulty I'm having this time is that if the 'parent' combo is blank I want to return ALL records for my row source, not none.
    I've had a look at some queries with parameters and can achieve this, what I can't do is recreate this in vba. I'm sure I'm getting confused with my strings and having a zero length string when I really want a NULL.

    Additionally, the value picked from cboE is a string, whereas the value from cboM is a NUMBER

    Here's myCode, where I have 3 combo boxes cboE, cboM and cboC.

    for cboE the row source is:
    strESQL = "SELECT DISTINCT tblDataTest.FieldE FROM tblTest"

    for cboM the row source is:
    strMSQL = "SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest"
    strMSQL = strMSQL & " WHERE (tblTest.fieldE = '" & strE & "' Or ('" & strE & "' Is Null))"
    strMSQL = strMSQL & " ORDER BY tblTest.fieldM;"

    for cboC the row source is:
    strCSQL = "SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest"
    strCSQL = strCSQL & " WHERE (tblDataTest.fieldE = '" & strELR & "' Or ('" & strE & "' Is Null))"
    strCSQL = strCSQL & " AND ((tblDataTest.fieldM = " & intMile & " Or (" & intMile & " Is Null))"
    strCSQL = strCSQL & " ORDER BY tblDataTest.fieldC;"

    so before anything is picked from cboE the row source is
    SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest WHERE (tblDataTest.ELR = '' Or ('' Is Null)) ORDER BY tblDataTest.fieldM;
    which obviously is not going to return all my rows as I know '' is not the same as null, but how can I get it to be null

    similarly (for cboC)
    SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest WHERE (tblDataTest.fieldE = '' Or ('' Is Null)) And (tblDataTest.Mileage = 0 Or (0 Is Null)) ORDER BY tblDataTest.fieldC;
    as 0 isn't null its not gonna work

    Any ideas?, its the OR parts I'm stuck on. The examples use variables but I also had no luck with referencing the cbo values directly.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL parameters VBA (2000/2002)

    I noticed that you are using both tblTest and tblDataTest in the same SQL string strESQL and also in strMSQL. As far as I can see, you should use either one or the other. Since I don't know which, I have left it as posted by you in the code below. Try

    strMSQL = "SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest" & _
    " WHERE tblTest.fieldE = Forms!FormName!cboE Or Forms!FormName!cboE Is Null" & _
    " ORDER BY tblTest.fieldM"

    where FormName is the name of the form; if it contains spaces, enclose it in square brackets. I'm less sure about the next one, since I can't visualize how the dependencies work.

    strCSQL = "SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest" & _
    " WHERE (tblDataTest.fieldE = Forms!FormName!cboE Or Forms!FormName!cboE Is Null)" & _
    " AND (tblDataTest.fieldM = Forms!FormName!cboC Or Forms!FormName!cboC Is Null)" & _
    " ORDER BY tblDataTest.fieldC"

    (In fact, you don't need to set this in code)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameters VBA (2000/2002)

    Thanks Hans, the different table names were a typing error (the real table is something different altogether - I tried to shorten for clarity in the example but appear to have failed miserably <img src=/S/frown.gif border=0 alt=frown width=15 height=15> )

    I'm sure I have tried what you've suggested originally (as I built the query in the query grid and then copied the sql)

    I'll give it another go and let you know how it goes

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameters VBA (2000/2002)

    Ok, its working, I'm not sure why it didn't seem to be yesterday.

    Out of curiosity, is there a way to make the parameter thing (with the nulls) work using variables like my first (posted attempt) or does it need to reference the controls so the values (if none are picked) are seen as NULL?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL parameters VBA (2000/2002)

    The alternative would be to create a different SQL string depending on the value of the combo box(es), for example

    strMSQL = "SELECT DISTINCT tblDataTest.fieldM FROM tblDataTest"
    If Not IsNull(Me.cboE) Then
    strMSQL = strMSQL & " WHERE tblTest.fieldE = " & Chr(34) & Me.cboE & Chr(34)
    End If
    strMSQL = strMSQL & " ORDER BY tblTest.fieldM"

    The WHERE part is only added if cboE is not empty.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameters VBA (2000/2002)

    Thanks for clearing that up.

    I've just used something similar to get the sql string for a record source to be used for a report.

    The purpose of the combos was to allow the user to choose the filter criteria they wanted and to ensure they can't choose conflicting criteria, getting no records returned.

    The form now has 6 combos, each potentially dependent on those 'above' so by the 6th, the WHERE part of the statement could comprise six elements of none, and of course everything needs refreshing when something above it changes. Whats your opinion on the most appropriate method to set the row source for the combos, this 'alternative' or the original solution?

    Everything now seems to be working fine by the way, your help was much appreciated (as always)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL parameters VBA (2000/2002)

    In theory, the "alternative" is slightly more efficient, since the SQL is more specific, but in practice, I think the difference will not be noticeable. I would just use whatever works for you, or you feel most comfortable with.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameters VBA (2000/2002)

    (Edited by HansV to indent code <img src=/S/grin.gif border=0 alt=grin width=15 height=15> - see next reply)

    I thought I try it but then thought it looked a bit complicated with the requirement for a number of nested 'ifs'

    The example below would be for cboC where there could be a WHERE and an AND from the 2 higher combos (E and M) - I've renamed the sql variable for clarity

    strSQL = "SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest"
    If Not IsNull(Me.cboE) Then
    strSQL = strSQL & " WHERE tblDataTest.fieldE = " & Chr(34) & Me.cboE & Chr(34)
    If Not IsNull(Me.cboM) Then
    strSQL = strSQL & " AND tblDataTest.fieldM = " & Chr(34) & Me.cboM & Chr(34)
    End If
    Else
    If Not IsNull(Me.cboM) Then
    strSQL = strSQL & " WHERE tblDataTest.fieldM = " & Chr(34) & Me.cboE & Chr(34)
    End If
    End If
    strSQL = strSQL & " ORDER BY tblDataTest.fieldC"

    Couldn't this get horribly complicated looking where 6 combos are involved?
    ps how do you indent code in the forum?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL parameters VBA (2000/2002)

    1. There are two ways to indent code:
    <UL><LI>Put <!t>[pre]<!/t> before and <!t>[/pre]<!/t> after the block of code. The code will be displayed "as is" in a fixed-width font (probably Courier New.)
    <LI>Use <!t>[tab]<!/t> tags to indent.[/list]Both types of tag can be typed directly, or obtained from the 1-Click TagPanel link above the edit area.

    2. To avoid endless nesting of If Then statements, I would do it like this:

    Dim strSQL As String
    Dim strWhere As String
    strWhere = ""
    If Not IsNull(Me.cboE) Then
    strWhere = strWhere & " AND tblDataTest.fieldE = " & Chr(34) & Me.cboE & Chr(34)
    End If
    If Not IsNull(Me.cboM) Then
    strWhere = strWhere & " AND tblDataTest.fieldM = " & Chr(34) & Me.cboM & Chr(34)
    End If
    ' Repeat for other combo boxes if necessary
    If strWhere <> "" Then
    ' Replace initial " AND" by " WHERE"
    strWhere = " WHERE" & Mid$(strWhere, 5)
    End If
    strSQL = "SELECT DISTINCT tblDataTest.fieldC FROM tblDataTest" & _
    strWhere & " ORDER BY tblDataTest.fieldC"

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL parameters VBA (2000/2002)

    So you're using mid without specifying an end and just sticking WHERE at the start - pretty obvious really. Funny how I often seem to go the complicated route - cheers <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •