How to display records that don't match between two recordsets

[Jake1776]

EDIT: Solution below, read on! (Spoiler alert: I used DCount.)

As stated in the title I have two recordsets and am comparing multiple values from one to the other. My code works fine for the perfect matches but the excel sheet I'm importing isn't always complete so I will need to know what records to update manually.

Though I wrote this code I am new to Access and to VBA so I have no clue how to effectively retrieve or even display the information I need. (I was thinking about displaying with a Report type recordset?)

IPAC is basically a spreadsheet we get with the cost of jobs. They don't have a Unique Identifier so I compared several fields. In case you are wondering why I didn't use a "With" statement that's because it's already pretty confusing to me without nested "withs" haha. I hope that is enough relevant detail, I really appreciate any help you can give. Even if you can't solve the problem I welcome any input about my practices.

Thanks!

Private Sub cmdImortIPAC_Click()
 
    Dim intResponse As Integer
 
    intResponse = msgbox("Do you wish to import the IPAC from" & vbLf & _
    "C:\TransferSpreadsheet\IPAC.xls?" & vbLf & vbLf & vbLf & _
    "This action will change mulitple jobs." & vbLf & _
    "It cannot easily be undone!", vbYesNo, "Are you sure?")
    If intResponse = vbYes Then
 
'   The spreadsheeet has to have this exact name and location
    DoCmd.TransferSpreadsheet acImport, , "tblIPAC", _
          "C:\TransferSpreadsheet\IPAC.xls", True
 
    Dim dbJMM As DAO.Database
    Dim rsIPAC As DAO.Recordset
    Dim rsPRINT_JOBS As DAO.Recordset
    Dim i As Integer
 
    Set dbJMM = CurrentDb
    Set rsIPAC = dbJMM.OpenRecordset("tblIPAC")
    Set rsPRINT_JOBS = dbJMM.OpenRecordset("PRINT_JOBS")
 
    Do Until rsIPAC.EOF
        Do Until rsPRINT_JOBS.EOF
            If rsPRINT_JOBS!JOB_STATUS = "OPEN" _
                And rsIPAC!Print = rsPRINT_JOBS!WORK_ORDER_NUM _
                And rsIPAC!Req_Num = rsPRINT_JOBS!REQUISITION_NUM _
                And rsPRINT_JOBS!BEST_COST = 0 Then
 
                Dim curIPACBillAmt As Currency
                curIPACBillAmt = rsIPAC!Bill_Amt
 
                rsPRINT_JOBS.Edit
                rsPRINT_JOBS!BEST_COST = curIPACBillAmt
                rsPRINT_JOBS!JOB_STATUS = "CLOSED"
                rsPRINT_JOBS.Update
'               i is the counter to tell you how many records you are updating
                i = (i + 1)
 
            End If
                rsPRINT_JOBS.MoveNext
            Loop
 
        rsPRINT_JOBS.MoveFirst
        rsIPAC.MoveNext
        Loop
 
        msgbox "You have updated " & i & " jobs.", , "Notice"
 
'   SQL query to delete all fields from the IPAC table after it has been used.
'   If the table isn't emptied the search would take forever.
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE tblIPAC.[IPACTempID], tblIPAC.[Jacket], tblIPAC.[Req_Num], tblIPAC.[Print], tblIPAC.[Bill_Amt], tblIPAC.[B_A_C], tblIPAC.[Prod_Code], tblIPAC.[IPAC_Cycle], tblIPAC.[Inv_Num], tblIPAC.[Branch], tblIPAC.[FY], tblIPAC.[Quantity], tblIPAC.[Postage], tblIPAC.[Pages], tblIPAC.[Copies], tblIPAC.[Trans], tblIPAC.[Bill_Date], tblIPAC.[Alc_Code], tblIPAC.[IPAC_Ind], tblIPAC.[CC_Ind], tblIPAC.[Filler1], tblIPAC.[Auto_Ind], tblIPAC.[FXK_Crdnum], tblIPAC.[FXK_Invdte], tblIPAC.[FXK_Invnum], tblIPAC.[FXK_Jobref] FROM tblIPAC;"
    DoCmd.SetWarnings True
       Exit Sub
    Else
        msgbox "IPAC not imported!", 48, "Attention"
        Exit Sub
    End If
 
    dbJMM.Close
    rsIPAC.Close
    Set dbJMM = Nothing
    Set rsIPAC = Nothing
 
End Sub
 

[PhilOfWalton]

I may be totally wrong, but I have a gut feeling that the whole thing can be done with a query.

Have a look at showing the 2 queries in the QBE grid and linking
rsPRINT_JOBS!BEST_COST with curIPACBillAmt
rsIPAC!Req_Num with rsPRINT_JOBS!REQUISITION_NUM
and setting the criteria to rsPRINT_JOBS!JOB_STATUS = "OPEN" and
And rsPRINT_JOBS!BEST_COST = 0.

Run that as a select query and see if it produces the required results. If so, you can change it into an update query.

Your delete part of your code is simply

DoCmd.RunSQL "DELETE tblIPac.* FROM tblIPac"
 

Phil

[ADezii]

excel sheet I'm importing isn't always complete so I will need to know what records to update manually.

1. What exactly do you mean when you state that the Sheet 'isn't always complete'?
2. What are the minimal requirements for Updating a Record?

[Jake1776]

Thanks for the input Phil! I chose to use code because the guy that set up this database has hundreds of queries forms and tables. It's a tangled mess. Basically I'm paranoid about making that worse. Also I can never seem to get SQL to do what I want, hence the overcomplication of my delete query haha.

I am using three fields from the excel sheet to identify a job in my database then using the bill amount from the spreadsheet to update that job. In my code above I only used two of the fields because the third sometimes is just zeros (on the spreadsheet). I can update the jobs based on two fields but the problem is that the closest thing to a unique identifier on the spreadsheet is a work order number that repeats every year and jacket number repeats every ten years. My table has 22,700 records that date back to 2007. The jacket number begins with the last digit of the current year. So if it can't match all three to a job it's hard to know I have the right one. I would at minimum want to use a msgbox to display which records from the IPAC couldn't be matched, but really I wish I had a better way to validate.

Thanks again for the help!

[PhilOfWalton]

Believe me, I know the problem with lots of queries, and I wrote them all myself. New report - new query, and almost certainly there would have been an existing query that would have done the job. Nonetheless, I still think that is the way to go, and name your new queries in a way that distinguishes them from the older queries.

As it’s 2016 and your records start in 2007, there should be no duplicated works order numbers. If there are, is there a date within the record that could be used to sort them out?

Is this a “one off” operation, or is the Excel spreadsheet going to be continued to be used?

Back to using a query. By changing relationships between the fields from left to right to equi joins, you can easily see which records have not been matched.

Phil

[Jake1776]

To re iterate: What I am looking for is a way to show jobs that were not found during the search in the above code.

Phil: Interesting point. Like I said I am open to different methods, I will research joins and try to get a better grasp of them. That may be the best solution.

This spreadsheet will be imported every two weeks. The jacket numbers are about to repeat and the while it's unlikely that the workorder will again coincide with a specific jacket, it is possible.

My main concern is that other than a counter that shows how many records were updated I can't tell what is going on when the code executes. Maybe this is just a phobia I need to get over, but I have considered printing a report to show what has been updated.

Edit: To answer your question about dates: The idea is that a closed job from years ago would be filtered out, reducing the chance of overlap; however the jobs are not often closed. They wait for the payment to process and the bill to come. That can take up to six years.

[PhilOfWalton]

Sorry to keep asking and not answering.

Is there a good reason for using Excel rather than Access as source of your information?

Is there a possibility of using 2 digits for the year. At least that will only repeat every 100 years and unless you know where to find the elixir of youth, it's unlikely to be your problem.

If you have Skype and would care to share a screen, I could possibly see what you are trying to achieve

Phil

[Jake1776]

Thanks very much for the offer but my company would not like that at all haha.

Unfortunately this spreadsheet comes from a different source. The numbers are assigned by them, I track our jobs by assigning a new number that is unique and am then left with identifying them individually and manually based on these preassigned numbers. The goal is to automate this process.

[ADezii]

To re iterate: What I am looking for is a way to show jobs that were not found during the search in the above code.

Why not a simple Else Clause capturing the Unmatched Records, writing these to a Temporary Table?

[Jake1776]

I could be wrong, but wouldn't that return all failed matches and not just the jobs that never matched?

In other words if it didn't match up with the first thousand records then the else clause would catch all of that as well as the ones that could never be found. Not totally sure on that though...

[ADezii]

the else clause would catch all of that as well as the ones that could never be found

The Else Clause would return any Records NOT matching the If Criteria, namely:

NOT (rsPRINT_JOBS!JOB_STATUS = "OPEN" _
                 And rsIPAC!Print = rsPRINT_JOBS!WORK_ORDER_NUM _
                 And rsIPAC!Req_Num = rsPRINT_JOBS!REQUISITION_NUM _
                 And rsPRINT_JOBS!BEST_COST = 0)

P.S. - Sorry if I am missing the point here.

[Jake1776]

So I added

 
Else
debug.print rsIPAC!PRINT
 

and it returns the last job's print number repeatedly. I believe that is because the if then statement finds that the last job did not match in the table PRINT_JOBS thousands of times until it did find it, but maybe the immediate screen doesn't hold all of the times it printed.

I will continue looking for a solution, at this point I am mainly documenting in case someone with a similar issue comes across this thread.
Thanks again for all the support.

[Jake1776]

If anyone comes across this post looking for an answer here it is.

I used DCount with multiple criteria to see how many times a job is found. This is good because it also tells me if the criteria matched more than one time (which would change more than one job). It works and was actually a fairly simple solution. There may be other ways to do it but I couldn't find any type of query that did this. I will probably eventually store the jobs that didn't match to a table to make it easier for the user to work with.

Thanks again for those that helped! If you have a question about this message me and I'll try to help.

'   Checks to make sure a job in IPAC is in PRINT_JOBS only once
CheckIPAC:
    Do While Not rsIPAC.EOF
        strPrint = rsIPAC!Print
        strReq_Num = rsIPAC!Req_Num
        strCheck = DCount("[WORK_ORDER_NUM] And [REQUISITION_NUM]", "[PRINT_JOBS]", _
        "[WORK_ORDER_NUM] = '" & strPrint & "' And" _
        & "[REQUISITION_NUM] = '" & strReq_Num & "'")
'       strCheck = DCount("[WORK_ORDER_NUM]", "[PRINT_JOBS]", "[WORK_ORDER_NUM] = '" & strPrint & "'")
        If strCheck = 0 Then
            msgbox "A job was not found!" & vbLf & _
                   "Please look up this job to ensure accuracy." & vbLf & vbLf & _
                   "Work Order Num: " & rsIPAC!Print & vbLf & _
                   "Jacket Num: " & rsIPAC!Jacket & vbLf & _
                   "Requisition Num: " & rsIPAC!Req_Num & vbLf & _
                   "Bill Amount: " & rsIPAC!Bill_Amt, 48
                   j = (j + 1)
            Else
                If strCheck > 1 Then
                    msgbox "A job was found " & strCheck & " times." & vbLf & _
                           "Please look up this job to ensure accuracy." & vbLf & _
                           vbLf & "Work Order Num: " & rsIPAC!Print & vbLf & _
                           "Jacket Num: " & rsIPAC!Jacket & vbLf & _
                           "Requisition Num: " & rsIPAC!Req_Num & vbLf & _
                           "Bill Amount: " & rsIPAC!Bill_Amt, 48
                           j = (j + 1)
                End If
        End If
        rsIPAC.MoveNext
    Loop
 
    If j > 0 Then
        intCheckAgain = msgbox("The IPAC contained " & j & " jobs that could not be matched." & vbLf & _
                             "You will not have another chance to identify these jobs." & vbLf & _
                             "Whould you like to run this check again?", vbYesNo)
        If intCheckAgain = vbYes Then
            rsIPAC.MoveFirst
            GoTo CheckIPAC
        End If
    End If