Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: Update Query built from a Multi Listbox

    It works great except, unable to get the same selected individual to update, and it's usually my first record, until I delete it then it becomes another record

    Here's what am trying to do...

    Multi-listbox contains a list of people I want to group together by assigning a number. I enter a number in a text box, select the people and click a button with the following code. All works great, except one selection (and it's the same selection every time) cannot be assigned for some reason.

    Any assistance would be appreciated. Thanks in advance.

    Here's the code:

    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryMultiSelect")
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    ' Loop through the selected items in the list box and build a text string
    If Me!NamesList.ItemsSelected.Count > 0 Then
    For Each varItem In Me!NamesList.ItemsSelected
    strCriteria = strCriteria & "tbl_Famliy_Members.Shooter_ID = " _
    & Me!NamesList.ItemData(varItem) & "OR "
    Next varItem
    End If
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If

    strCriteria = Left(strCriteria, Len(strCriteria) - 3)

    ' Build the new SQL statement incorporating the string
    strSQL = "UPDATE tbl_Famliy_Members SET tbl_Famliy_Members.Target_Assignment = [Forms]![frm_Flighting]![Target_Assignment] " & _
    "WHERE " & strCriteria & ";"

    'This message box identifies what Shooter ID's are/is selected and are to be updated. Currently turned-off.
    'MsgBox (strSQL)

    ' Apply the new SQL statement to the query
    qdf.SQL = strSQL

    ' Open the query
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryMultiSelect"
    DoCmd.SetWarnings True

    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing


    Me.NamesList.Requery

  2. #2
    Join Date
    Oct 2005
    Posts
    6

    Fixed

    Figured it out. I had my target assignment bound to a field that was supposed to go null after a number was assigned. This was causing my record to go null also.

Posting Permissions

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