Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    27

    Unanswered: data validation compare records in 2 tables

    Help

    I would like to use VB code to compare records in two tables and display a message to the user when the data can’t be found in the master table. The code would be linked to the on click event of a command button.

    Any insight would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Try something like this


    Dim q as string ‘ query
    Dim rst as recordset ‘add ActiveX reference library to file

    q = “select field1 from table1 “
    q = q & “where field1 not in (select field1 from table2)
    on error resume next
    set rst = new adodb.recordset
    rst.Open q, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    if err then
    msgbox “tell user what happened” & error$
    else
    if rst.recordcount = 0 then
    msgbox “tell user what you wanted to tell him”
    endif
    rst.close
    endif
    Cathy

  3. #3
    Join Date
    Feb 2004
    Posts
    27
    Originally posted by csbaker
    Try something like this

    Thanks for your response. I'm realtively new to Access/VB so I will have some stupid questions. My first is the code (q=“select field1 from table1 “) is that the SQL terminology or should that be a Select Query name? Is the next line (q = q & “where field1 not in (select field1 from table2) ) also SQL?

    Thanks again for your help

    Dim q as string ‘ query
    Dim rst as recordset ‘add ActiveX reference library to file

    q = “select field1 from table1 “
    q = q & “where field1 not in (select field1 from table2)
    on error resume next
    set rst = new adodb.recordset
    rst.Open q, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    if err then
    msgbox “tell user what happened” & error$
    else
    if rst.recordcount = 0 then
    msgbox “tell user what you wanted to tell him”
    endif
    rst.close
    endif

Posting Permissions

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