DCount 1

[peljo]

I am trying to build a code that compares two tables that should be identical.If they are not identical, which means that the second table may contain less or more products than the original table products, a warning
must be sent.
One of my efforts:
Dim lngCount As Long
lngCount = DCount("*", "products","products1",products.productid <> products1.productid)
If lngCount <> 0 Then
MsgBox " The table Products1 does not correspond to the Table Products", vbExclamation
End If
i am not sure whether this is the right method and also i have not written the above code properly.I get the message "variable not defined"
Could you help me ?

 

[PHV]

You may try this:
Dim lngCount As Long
lngCount = DCount("*", "products", "productid Not In (SELECT productid FROM products1)")
If lngCount <> 0 Then
MsgBox "The table Products1 lacks " & lngCount & " records from the table Products", vbExclamation
End If
lngCount = DCount("*", "products1", "productid Not In (SELECT productid FROM products)")
If lngCount <> 0 Then
MsgBox "The table Products1 have " & lngCount & " records not in the table Products", vbExclamation
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886