Comparing 2 Recordsets / Matching column data (1 Viewer)

Deke

New member
Local time
Today, 16:58
Joined
Feb 10, 2021
Messages
14
Hi All,
I'm trying to write a function that looks at two record sets and verify that pack numbers match between RetailEntry and tblTemp. If a pack number is in RetailEntry and not tblTemp then I want it give a warning.

What I have below works for a 1 to 1 check BUT there is the possibility of duplicates and that's where my problem is. As long as the pack number appears between the two tables it's a "Pass" otherwise it's a "Fail" and it stops so it can be corrected in the RetailEntry table.

Any help would be greatly appreciated!

Here is my code currently:

Code:
Function Validation()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsm As DAO.Recordset
        
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("RetailEntry")
Set rsm = CurrentDb.OpenRecordset("tblTemp")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Validate offers have not mailed
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True

  If rsm.RecordCount = 0 Or rs.Fields("Pack_Number") <> rsm.Fields("PackNum") Then
    MsgBox ("All prefixes have mailed for Pack Number " & rs.Fields("Pack_Number") & " If you wish to submit an In-season Markdown Request " _
    & "please exit the tool, and submit your request using the In-Season Markdown request file in the CID Request folder")
    Exit Function
 
rs.MoveNext
rsm.MoveNext
Loop
End If

End Function


Thanks!!!
-Deke
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you considered simply using a Find Unmatched Query?
 

Deke

New member
Local time
Today, 16:58
Joined
Feb 10, 2021
Messages
14
Hi. Have you considered simply using a Find Unmatched Query?

I haven't. Trying to think how that would work for my application. I wrote this so it would just validate what my users enter before a final submission. Let me look this up and see if I can make that work. If you have any suggestion on how I could make that work for what I'm trying to do I'd appreciate it! Thanks for your reply!

Edit:
Sorry I haven't slept in a few days... so I'm a little slow. (Had surgery earlier this week). All I need to do here is just do a record count for the unmatched query and if it's greater than 1 I can have my message pop.

Thanks for your help with this! Sorry I was slow on the uptake. lol
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom