Results 1 to 4 of 4
  1. #1
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14

    Wink VBA NOOB - Compare value in textbox on form vs field in table to check if duplicate

    Good day,

    So this forum has helped me in the past and hopefully can come to my aid now.

    I need to create an After_Update event on my textbox that checks the value in a textbox vs. a field in a table. If it finds the value in the field of the table, gives an error message and clear the textbox.



    The information are as follows:

    Form Name: frm_new_item
    Control Name: text221
    Table to be checked: tbl_itemlisting
    Field to be checked: item_code

    All the inverted commas and brackets confuse the living daylights out of me and this is used in conjunction with a dcount / dlookup to achieve this.

    Thanks in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you use square brackets when table and field names have spaces or none alphanumeric character in them or they are reserved words.

    you use the double quotes to create strings, which is the datatype the dcount etc accepts for its parameters

    you use single quotes to define a string within a string

    so your code will look something like this if item_code is a number

    Code:
    if dcount("item_code","tble_itemlisting","item_code=" & me.text221)<>0 then
        msgbox "some message here"
    end if
    of item_code is text then you need a string within a string, so your code would be

    Code:
    if dcount("item_code","tble_itemlisting","item_code='" & me.text221 & "'")<>0 then
        msgbox "some message here"
    end if
    suggest you get into the habit of giving your control names meaningful names. It will significantly reduce the chance of errors and make your code more readable

  3. #3
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Hi Ajax,

    Thanks for the help. I appreciate it.

    Your suggestion solved it for me. I am still quite new to VBA and Access, so any help I get is greatly appreciated.

    I would actually love to get some contacts onto Skype to speak to directly if anyone is willing. Please throw me an invite if you are keen

    My Skype ID is: michael.van.jaarsveld

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Hi Michael,

    My business is developing and extending solutions for clients - for a fee. I help others FOC through this and other forums when I have the time, so thank you for your offer, but regret I will have to decline.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2013, 10:06 AM
  2. check duplicate records within same table
    By cthorn112 in forum Queries
    Replies: 0
    Last Post: 06-19-2012, 12:23 PM
  3. Replies: 5
    Last Post: 05-02-2012, 07:56 AM
  4. Replies: 1
    Last Post: 01-25-2012, 06:46 PM
  5. Replies: 1
    Last Post: 06-01-2009, 04:05 PM

Posting Permissions

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