Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount (Acess 2000)

    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 ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Acess 2000)

    A Find unmatched query as suggested by ThreeCrow is probably the easiest way. You already asked how to create one - see <post:=565,543>post 565,543</post:>.

    You could also try

    Dim lngCount As Long
    Dim lngCount1 As Long
    lngCount = DCount("*", "Products")
    lngCount1 = DCount("*", "Products1")
    If Not lngCount = lngCount1 Then
    MsgBox "Product contains " & lngCount & _
    " records, while Product1 contains " & _
    lngCount1 & " records.", vbInformation
    End If

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (Acess 2000)

    <P ID="edit" class=small>(Edited by ThreeCrow on 25-Mar-06 06:20. To add previous post)</P>As a thought,

    You could use the "Find Unmatched Query Wizard" to create a query that will not only give you a count of those records in the two tables that do not match, it will give you a listing of those records.

    You could run this query from code or use a macro, and if you like have a report print.

    Hope this helps

    PS: I see from another post that you do not have "Find Unmatched Query Wizard" on your computer.
    refer to <post#=565,549>post 565,549</post#>
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (Acess 2000)

    Thank you both for your excellent advice.The suggestion suits me perfect.Please give me an advice.I will put in the new funcion called Compareproducts in my function CollectOne.My function collects and processes tables from database from differenct sources.So, when the function compareProducts works, the message should give an information where it is from, for exaample is it fm DSo, or DVa, etc.I am confused ho to do it.
    Public Function Collect()
    CollectOne (DSo)
    CollectOne (DVa)
    CollectOne (DBl)
    CollectOne (DHa)
    CollectOne (DPl)
    CollectOne (DTa)
    CollectOne (DTr)
    CollectOne (DRs)
    CollectOne (DSz)
    CollectOne (dbs)

    End Function


    Public Sub CollectOne(strDatabaseName As String)
    If Dir(strDatabaseName, vbNormal) <> "" Then
    ImportAllTables (strDatabaseName)
    CompareProducts
    ProcessTables
    Kill strDatabaseName
    Else
    DoCmd.Beep
    End If
    End Sub




    Public Function CompareProducts()
    Dim lngCount As Long
    Dim lngCount1 As Long
    lngCount = DCount("*", "Products")
    lngCount1 = DCount("*", "Products1")
    If Not lngCount = lngCount1 Then
    MsgBox "Product contains " & lngCount & _
    " records, while Product1 contains " & _
    lngCount1 & " records.", vbInformation
    End If
    End Function

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Acess 2000)

    You could pass the database name to theCompareProducts function:

    Public Sub CollectOne(strDatabaseName As String)
    If Dir(strDatabaseName, vbNormal) <> "" Then
    ImportAllTables strDatabaseName
    CompareProducts strDatabaseName
    ProcessTables
    Kill strDatabaseName
    Else
    DoCmd.Beep
    End If
    End Sub

    Public Function CompareProducts(strDatabaseName As String)
    Dim lngCount As Long
    Dim lngCount1 As Long
    lngCount = DCount("*", "Products")
    lngCount1 = DCount("*", "Products1")
    If Not lngCount = lngCount1 Then
    MsgBox "In " & strDatabaseName & ", Product contains " & lngCount & _
    " records, while Product1 contains " & _
    lngCount1 & " records.", vbInformation
    End If
    End Function

  6. #6
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (Acess 2000)

    Superb, then I included it both in my code.Thanks for your help

Posting Permissions

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