Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Access 2010: Parameter Action query

    I have a couple of things I am trying to accomplish.
    1. Allow users to select one or more than one title from the list and run a parameter query.
    2. Append this data to a table that will make sure that all duplicates have been removed. Duplicates are inherent from my original downloaded data from a remote server.

    SelectMember_frm has a combo field which allows the user to only select one title at a time. I have even tried entering “Board President” And “District Administrator” but I get no results returned. Although doable, I would prefer not to write a new append query for each possible combination of titles.

    Users could select one title at a time and append to a table. I know that I cannot run a parameter on an append query. so I thought that maybe I could select all records copy and then paste into the final table. This has the contact # set as a primary key, which will ensure that no duplicates are pasted into the table.

    Private Sub cmdMbrOk_Click()
    DoCmd.OpenQuery "EMemberList_qry", acViewNormal, acEdit ‘This query opens with the list of names having the selected title

    DoCmd.RunCommand acCmdSelectAllRecord ‘ I get Run-time error ‘2501’ The RunCommand Acction was canceled; I have set Recordset Type to Dynaset (Inconsistent Updates)

    DoCmd.RunCommand acCmdCopy
    DoCmd.OpenTable "SpecificTitlesEMailing_tbl", acViewNormal, acEdit
    DoCmd.SetWarnings (False)
    DoCmd.GoToRecord acDataTable, "SpecificTitlesEMailing_tbl", acNewRec
    DoCmd.RunCommand acCmdPasteAppend
    DoCmd.Close acForm, "SelectMember_frm"
    End Sub


    As always your assistance is greatly appreciated. Thank you, Djsmith

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why not just run an append query after selecting the title from the combo box? An append query looks like INSERT INTO Tablename SELECT * FROM InputTable WHERE Title = Forms!YourFormName!cmbTitle . You can run this append query from VBA (behind a button) via a command CurrentDB.Execute sSql where sSql is a text string containing the above query. Excuse the lack of carriage returns, I have a Windows 7 laptop that has Windows 8 installed.

  3. #3
    New Lounger
    Join Date
    Apr 2012
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you, I had thought of that, but I am depending on users to click both buttons before using the final data. I was hoping to find a way to make sure that they only have to click one button.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Well then use a multi select list box and loop for the titles they have chosen. There are multiple clicks as you select each title from the list box.

Posting Permissions

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