By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

How can you compare contents of two tables?

P: 34
I want to check if a row in the underlying table of my form, changed, so I created a second table, pasted all rows from underlying table in second table, and then want to check if the two tables are identical.... the reason for this, is that if someone wants to update data in the table on the form, but the row that they want to put in, already exists in the table, nothing will happen, as I've put Primary Keys in, so I want to give them a message to say why nothing changed.....

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go_Click()
  2. On Error GoTo Err_Open_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim StDocName2 As String
  6.     Dim newColumnID As Integer
  7.     Dim rst As ADODB.Recordset
  8.     Set rst = New ADODB.Recordset
  9.     Dim strDisplay As String
  10.  
  11.     DoCmd.SetWarnings False
  12.  
  13.         'check if an identical column has been defined before
  14.         Set rst = New ADODB.Recordset
  15.         stDocName = "SELECT * FROM [Product Type p Book] WHERE Book='" & Combo51.Value & "' AND [Front Office Product Type]='" & [FO PT].Value & "'"
  16.         rst.Open stDocName, CurrentProject.Connection
  17.  
  18.         If txtID = "" Or IsNull(txtID) Then
  19.         stDocName = MsgBox("Click on the Row that you want to Update")
  20.     Else
  21.  
  22.         stDocName = "UPDATE [Product Type p Book] SET Book='" & Combo51.Value & "',[Front Office Product Type]='" & [FO PT].Value & "' WHERE [ID]=" & txtID
  23.         DoCmd.RunSQL stDocName
  24.         Me.List55.Requery
  25.  
  26.  
  27.         StDocName2 = "App PT p B1 to 3"
  28.         DoCmd.OpenQuery StDocName2
  29.  
  30.       ' I NEED SOME CODE HERE TO CHECK IF TABLE 2 = TABLE 3....
  31.       'IF IT IS THE CASE, I'LL PUT A MESSAGE IN, SAYING THAT THE UPDATE WAS NOT DONE, BECAUSE THE ROW ALREADY EXISTS IN THE TABLE
  32.  
  33.  
  34.     End If
  35.  
  36.     're-query to refresh all combo boxes
  37.     Combo51.Requery
  38.  
  39.     'clean out all combo boxes
  40.     Combo51.Value = ""
  41.     'Focussed.Value = False
  42.     txtOrderBy = ""
  43.     [FO PT] = ""
  44.     txtID = ""
  45.     List55.Requery
  46.  
  47. Exit_Open_Click:
  48.     Exit Sub
  49.  
  50. Err_Open_Click:
  51.     MsgBox Err.Description
  52.     Resume Exit_Open_Click
  53.  
  54. End Sub 
Please advice on how I can achieve this
Nov 10 '06 #1
Share this Question
Share on Google+
3 Replies


100+
P: 143
Why not check the validity of the record in the uderlying table? If they are trying to add a record wth the same primary key, they will get an error message anyway. That is if you have set the property to No Duplicates. Similarly, if they try to amend a primary key, access objects. You can have your own error message for this. Having duplicate data is dangerous.
Nov 10 '06 #2

PEB
Expert 100+
P: 1,418
PEB
And also if you track for updates you can catch the updates using the Before Update Event and the properties:

Me![Field].Value
Me![Field].OldValue

And you don't need second table to see the previous value of the respective field!
Nov 19 '06 #3

P: 34
Hi guys, tanx for your replies. The BeforeUpdate Code solved my problem.

tnx again
Dec 1 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.