Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing variables (2003 (SP2))

    First of all I'll try to explain what I'd like to do, then how I wanted to make it happen.

    In the simplest of terms, I have a form (unbound) with a number of buttons. When the user clicks a button, it opens another form with one specific record available.

    I've only recently started with VBA, so if this is a basic error, please be gentle with me.

    Now I can have SQL behind each button, but I wanted to have this as a Module function & simply pass the variable to the function, to give the SQL the relevant criteria. But I'm stuck getting the function to see the variable. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

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

    Re: Passing variables (2003 (SP2))

    Can you provide some details? You haven't told us what variable and what SQL you are talking about, and how they fit into the On Click event of the command buttons.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing variables (2003 (SP2))

    OK, the variable name is strCrit, the value is specific to that particular button eg cmdOil has the variable value of "LowOil" & the value from cmdRev is "RevCounter"

    The SQL is a basic query that the 2nd form uses for data source. The function alters the query SQL accordingly.

    Is that what you meant?

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

    Re: Passing variables (2003 (SP2))

    How is the SQL used for the form? Do you pass it in the OpenArgs argument of DoCmd.OpenArgs, or ...? Please try to provide all relevant information.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing variables (2003 (SP2))

    Ah, right. Well the SQL is in this function (abbreviated);

    Function CreateSQL1()

    Dim SQL1Database As DAO.Database, SQL1QueryDef As DAO.QueryDef
    Dim SQL1QueryName As String
    Dim SQL1SQLString As String
    Dim SQL1dbODBC As DAO.Database
    Dim i As Integer
    Dim strCrit As String

    Set SQL1Database = CurrentDb()
    SQL1QueryName = "qryFilter1"

    Set SQL1QueryDef = SQL1Database.CreateQueryDef(SQL1QueryName)
    SQL1SQLString = "SELECT " & vbCrLf
    SQL1SQLString = SQL1SQLString & "Table1.Item, Table1.Description " & vbCrLf
    SQL1SQLString = SQL1SQLString & "FROM Table1 " & vbCrLf
    SQL1SQLString = SQL1SQLString & "WHERE(((Table1.Item)=" & Chr$(34) & StrCrit & Chr$(34) & "));"

    SQL1QueryDef.SQL = SQL1SQLString
    SQL1QueryDef.Close
    SQL1Database.QueryDefs.Refresh
    DoCmd.OpenQuery SQL1QueryName
    DoCmd.Close acQuery, SQL1QueryName

    End Function


    And this is the button OnClick code

    Sub CmdOil_Click()
    Dim strCrit As String
    strCrit = "LowOil"

    Call CreateSQL1

    stDocName = "form2"
    DoCmd.OpenForm stDocName, , "qryFilter1"

    End Sub


    PS i've left out the IfExists function

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

    Re: Passing variables (2003 (SP2))

    OK, that makes things clearer.

    1) Change the header of the function to
    <code>
    Function CreateSQL1(strCrit As String)
    </code>
    2) Remove the following line from the function:
    <code>
    Dim strCrit As String
    </code>
    3) Call the function like this in the On Click event of the command button:
    <code>
    Call CreateSQL1(strCrit)
    </code>
    <img src=/w3timages/blueline.gif width=33% height=2>

    However...

    I think it can be done in a much simpler way. You don't need to change the SQL of the query. The query can be fixed
    <code>
    SELECT Item, Description
    FROM Table1;
    </code>
    Change the On Click code to
    <code>
    Sub CmdOil_Click()
    DoCmd.OpenForm "form2", , , "Item = 'LowOil'"
    End Sub
    </code>
    Note the extra comma before the condition. Instead of a FilterName (a complete SQL string), we only pass a WhereCondition (a criteria string) to DoCmd.OpenForm

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing variables (2003 (SP2))

    That's great Hans. I had tried that way initially, but my syntax was way off.

    Many thanks

Posting Permissions

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