Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Where Clause with In Parameter (Acess 2000)

    HI,

    I am trying to update a group of records that are selected from a list box. When the user clicks the update button, I have the selected values stored in a hidden text box. This textbox is used for the IN clause in the query. This query is call used a doCmd.openquery method. For some reason, If I only have 1 record selected, I can get it to update, but it I have more then 1 record, then I cannot the records to update. This is what I have:

    Dim stDocName As String
    Dim i As Long
    For i = 0 To listbox_Hostesses.ListCount - 1
    If listbox_Hostesses.Selected(i) = True Then
    strList = strList & ", " & listbox_Hostesses.Column(10, i)

    End If
    Next i

    If strList = "" Then
    MsgBox "No Hostess Selected"
    Exit Sub
    Else

    strList = Mid(strList, 2)

    txtbox_tempID.Value = strList
    stDocName = "qry_UpdateHostessGroup"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    listbox_Hostesses.Requery

    MY Updatequery look like this:
    UPDATE Hostesses SET Hostesses.ConsultantID = "52"
    WHERE ((([Hostesses].[HostessID]) In ([Forms]![FRM_ModifyHostess]![txtbox_tempID])));

    Thanks

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

    Re: Where Clause with In Parameter (Acess 2000)

    I don't think you can use a reference to a control on a form in In(...) criteria except if it contains only one element, as you have found, but then you wouldn't have needed the In(...) construction.

    I recommend setting the SQL of the query in code:

    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    strSQL = "UPDATE Hostesses SET Hostesses.ConsultantID = '52' " & _
    "WHERE [Hostesses].[HostessID] In (" & [Forms]![FRM_ModifyHostess]![txtbox_tempID] & ")"
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs(stDocName)
    qdf.SQL = strSQL
    Set qdf = Nothing
    Set dbs = Nothing
    DoCmd.OpenQuery stDocName

    Note the single quotes around 52 (is ConsultantID really a string?)

    This code needs a reference to the Microsoft DAO 3.6 Object Library (in Tools | References...)

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Where Clause with In Parameter (Acess 2000)

    Excellent. That worked awesome. On a side note, when using a list box, my formatting for my phone number and Postal Cold is non exixstant? Is there a way to get the formatting for those 2 columns to exist?

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

    Re: Where Clause with In Parameter (Acess 2000)

    As far as I know, you can only influence the formatting of the columns in a list box if the Row Source Type is the name of a callback function, not if is Table/Query. Create a query that explicitly formats these fields by using the Format function, and use that as Row Source.

Posting Permissions

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