Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query SQL change from Listbox AfterUpdate error (A2K)

    Ok, this was working fine up until about an hour ago, now I'm receiving an error (see screenshot)...any troubleshooting tips? This form works fine in a previous version of the database...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query SQL change from Listbox AfterUpdate error (A2K)

    Check your references. This is usually indicative of a "Missing Reference".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query SQL change from Listbox AfterUpdate error (A2K)

    Mark,
    Thanks a lot. I went into the references, and it showed Microsoft Office 9.0 library, (MISSING Microsoft Office 10.0 Library)....so I unchecked it, closed out the dialog box, opened it, and when I scrolled down it showed Office 10.0 library again. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    New problem though - now I receive an error Run-time error '3296':

    Join expression not supported.

    ---I highlighted the line that is highlighted when I click debug...

    Here is my code:
    -------------------------------------------------------------------
    Private Sub lstNOKNames_AfterUpdate()
    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As QueryDef

    strSQL = "SELECT tblPersonnel.RANK, tblPersonnel.LNAME, tblPersonnel.FNAME, tblPersonnel.MI, tblPersonnel.SSN, tblPersonnel.MOS, tblPersonnel.COMPANY, tblAddresses.NOK_NAME, tblAddresses.NOK_RELATION, tblAddresses.NOK_ADDRESS, tblAddresses.NOK_CITY_STATE_ZIP, tblAddresses.NOK_PHONE FROM tblPersonnel INNER JOIN tblAddresses ON tblPersonnel.SSN = tblAddresses.SSN"

    For Each varItem In Me.ActiveControl.ItemsSelected
    strTemp = strTemp & "((tblPersonnel.SSN) = '" & Me.ActiveControl.ItemData(varItem) & "') Or "
    Next

    strSQL = strSQL & "HAVING (" & Left(strTemp, Len(strTemp) - 4) & ")" & ";"

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">CurrentDb.QueryDefs("qryNOKRoster").SQL = strSQL</span hi>

    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query SQL change from Listbox AfterUpdate error (A2K)

    Next time you get to that place, go to immediate window and type ?strSQL so you can see what the value of strSQL is. You might even want to create a new query and paste-in the value, then try to run it or go to design mode. Access will probably give you a better explanation of your problem then.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query SQL change from Listbox AfterUpdate error (A2K)

    Mark, I found my error, it was that I was using "HAVING", instead of "WHERE". I guess HAVING is used only when grouping records...

    Thanks again for your help <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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