Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: SQL Query don't work in Cmd Button Event

    Hi All
    Problem
    I have a SQL that I constructed with the aid of the Query builder. It works in the query builder but I can not get it to work in the Cmd Button event. Here is the code that comes from the Query Builder.

    Code
    --------------------------------------------------------------------------------------
    DELETE tblRentalPayments.InvoiceNumber, tblRentalPayments.Type, tblRentalPayments.Trans_ID, *
    FROM tblRentalPayments
    WHERE (((tblRentalPayments.InvoiceNumber) In (14,15,16)) AND ((tblRentalPayments.Type)="Closed") AND ((tblRentalPayments.Trans_ID)=18));

    --------------------------------------------------------------------------------------

    The In (14,15,16) takes its values from a multi listbox routine to capture selected items. I substituted the numbers manually to try to get it to work.

    The (tblRentalPayments.Trans_ID)=18 is the Foreign Key which I have entered manually but would like get the value from a variable that captures it at the beginning of the event.

    Thanks in Advance Bob

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The easiest way would probably consist in building the query dynamically:
    Code:
        CONST c_SQL As String = "DELETE FROM tblRentalPayments " & _
                                "WHERE tblRentalPayments.InvoiceNumber In ( @L ) AND " & _
                                      "tblRentalPayments.Type = 'Closed' AND " & _
                                      "tblRentalPayments.Trans_ID = @V;"
    
        Dim var As Variant
        Dim strList as String
        Dim strSQL As String
        Dim lngTransID As Long
    
        lngTransID = ... ' assign the value here.
    
        With Me.ListBoxName '  Change to the actual name of the ListBox.
            For Each var In .ItemsSelected
                If Len(strList) > 0 Then strList = strList & ", "
                strList = strList & .ItemData(var)
            Next var
        End With
        strSQL = Replace(c_SQL, "@L", strList)
        strSQL = Replace(strSQL, "@V", lngTransID)
        CurrentDb.Execute strSQL, dbFailOnError
    Have a nice day!

  3. #3
    Join Date
    Oct 2012
    Posts
    56

    Solved

    Hello Sinndho,

    Thank you very much the problem was solved by the SQL query. I Guess that is another area that a person needs to work on. 69 years old and still learning.

    Thanks Again Bob

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Ten years younger and still learning too

    You're welcome!
    Have a nice day!

Posting Permissions

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