Results 1 to 5 of 5
  1. #1
    JustinC is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    8

    Check if record exist and update the status

    Hello All,

    I have 2 table in access. I am trying to check if record in table 2 exist and update the status of table 1 to "Exist" or "No". There is two column UserID and name.
    Below shows a sample of what I am trying to do.

    Table 1:
    UserID Name
    123456 James
    234561 Mary
    789164 Leonard
    897652 Mark

    Table 2:
    UserID Name
    123456 James
    789164 Leonard

    My desired output:
    UserID Name Status


    123456 James Exist
    234561 Mary No
    789164 Leonard Exist
    897652 Mark. No

    Thank you for your help!

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    How are you trying to determine this? One way of course would be to use a Combo and not in list. Another would be a DLookup or DCount.

    Air Code Example:
    If DCount("*", "[TableName]", "[FieldName] = " & PrimaryKey) <= 0 Then

    If MsgBox("Do you want to add it?", _
    vbExclamation + vbYesNo + vbDefaultButton2) = vbYes Then

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE YourTableNameHere SET YourTableNameHere.SomeField = SomeValue " & _
    "WHERE (((YourTableNameHere.userID)='" & Me.txtUserID.Value & "'));"
    DoCmd.SetWarnings True


    Else
    Exit Sub
    End If
    Last edited by burrina; 08-17-2014 at 07:40 AM. Reason: Further Code Example

  3. #3
    JustinC is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2014
    Posts
    8
    Hi thanks for your reply.

    I am thinking of using a SQL statement with inner join in a vba but I have no idea how to achieve it? May I get some help if my idea is possible.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Justin,

    What are you trying to do in very general terms? What is your database about?

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    You could do this with two SQL statements. The first one will change all of Table1 Status to "No", (just to initialize it) and the second one will change status to "Exist" for all the ones that are in Table2:

    Currentdb.execute "Update Table1 set Staus = 'No' "
    currentdb.Execute "Update Table1 set Status = 'Exist' where Table1.UserID in (Select Table2.UserID from Table2)"

    John

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

Similar Threads

  1. Check if record exist
    By sahand in forum Forms
    Replies: 2
    Last Post: 06-11-2014, 07:07 AM
  2. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  3. check table, if exist then update another table
    By JeroenMioch in forum Programming
    Replies: 6
    Last Post: 07-06-2012, 09:12 AM
  4. Check if record exist
    By khhess in forum Programming
    Replies: 1
    Last Post: 06-14-2011, 06:56 PM
  5. Check Box status
    By NOTLguy in forum Forms
    Replies: 5
    Last Post: 11-27-2010, 08:59 PM

Tags for this Thread

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