Results 1 to 7 of 7

Check if value exists in table from query parameter before executing query

  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102

    Check if value exists in table from query parameter before executing query

    I need to add a field to a single column table using a parameter append query but not if the value is already in the table.


    How do I pass the parameter to lookup the value, check then finish ? Something like this

    Code:
    Private Sub CmdAddToExclusion_Click()
    
    'Opens append query with parameter box
    DoCmd.OpenQuery "qry_AppendTo_tbl_Requisition_Exclusions"
    
    
    
    If DCount("typed query parameter", "tbl_Requisition_Exclusions", "[Item_ID]") > 0 Then
    
    'Stop the query
    MsgBox "Already Exists"
    
    
    Else
    
    
    'Execute the query
    
    
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,375
    I'd use a form to gather the user input and use that in both the test and the append query. You just don't have any real control with bracketed prompts in queries. Your append query would go in the Else clause.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Makes sense, did not think of that, thanks

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,375
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Similar to a form, I used an unbound text box and a button that triggers checking if the value already exists in the target table, if not adds it and requery the subform

    The exclusion test is not really needed since once the item is in the exclusion table it won't be selected anymore so it does not give a chance the user to see it again but I added it for the sake of data testing.

    Code:
    Private Sub CmdExcludeItem_Click()
    
    'Set variable
    Dim sItem As String
    Me.txtExcludeItem.SetFocus: sItem = Me.txtExcludeItem.Value
    
    
    'test if value already in exclusion table
    If DCount("[Item_ID]", "tbl_Requisition_Exclusions", "[Item_ID] = txtExcludeItem") > 0 Then
    
    
    MsgBox "Item already excluded"
    
    
    'Emtpy text box value
    Me.txtExcludeItem.Value = Null
    
    
    Else
    
    
    DoCmd.RunSQL "INSERT INTO tbl_Requisition_Exclusions (Item_ID) VALUES ('" & txtExcludeItem & "')"
    
    
    MsgBox "Item added to exclustion list"
    
    
    Me.txtExcludeItem.Value = Null
    
    
    'Refresh subform data
    DoCmd.OpenQuery "qry_DELETE_tbl_Requisitions_Select"
    DoCmd.OpenQuery "qry_AppendTo_tbl_Requisitions_Select"
    Me.frm_sub_Requisition_Items_Selection.Requery
    
    
    End If
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,375
    A couple of thoughts. You declare and set a string variable but never use it. You don't need to set focus to the textbox to get its value; that's only needed if you refer to the .Text property of the control. The DCount() is working? I'd normally concatenate the value into the criteria, as you did for the SQL. Some thoughts on RunSQL:

    http://www.baldyweb.com/SQLWarnings.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    102
    Quote Originally Posted by pbaldy View Post
    A couple of thoughts. You declare and set a string variable but never use it. You don't need to set focus to the textbox to get its value; that's only needed if you refer to the .Text property of the control. The DCount() is working? I'd normally concatenate the value into the criteria, as you did for the SQL. Some thoughts on RunSQL:

    http://www.baldyweb.com/SQLWarnings.htm
    Thanks for the tips. Indeed removing Focus is working too. I see I ended up not using the variable, I started in one direction and ended in another, I'll fix that.

    Yes DCount() is working, I'm using it in a similar fashion to detect yes/no records like DCount(yes)<1 Then "no items were selected" Else execute.

    Did not know about turning warnings back on, I added that too, thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Function To Check If Table Exists
    By chalupabatman in forum Programming
    Replies: 6
    Last Post: 11-16-2017, 01:23 PM
  2. Replies: 1
    Last Post: 08-09-2015, 10:03 AM
  3. VBA To Check If Query Exists If Does Rename
    By jo15765 in forum Macros
    Replies: 3
    Last Post: 07-28-2014, 02:25 PM
  4. Replies: 3
    Last Post: 11-26-2013, 03:40 PM
  5. How to check if Table already exists
    By riaarora in forum Access
    Replies: 1
    Last Post: 08-12-2012, 09:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums