Unmatched Records notification (1 Viewer)

[stonegold87]

Hello everyone
I m working on a small project can anyone help me with a vba code to identify unmatched records between 2 Data Tables and remind user through msg box to resolve the problem and it would be really helpful if msg box show which records are unmatched

 

[theDBguy]

Hi. Have you tried using the Find Unmatched Query Wizard first?

 

[moke123]

Have you tried the unmatched query wizard?

MS Access - Unmatched Query Wizard

MS Access - Unmatched Query Wizard - In Access, there is another very useful wizard and that is Find Unmatched Query Wizard. The Find Unmatched Query Wizard creates a query that finds records or rows in one table that have no related records in another table.

www.tutorialspoint.com

 

[stonegold87]

Hi. Have you tried using the Find Unmatched Query Wizard first?

Yes i am getting results with unmatched query

 

[theDBguy]

Yes i am getting results with unmatched query

Okay, good. Now, you have two options:
1. You can use DCount() to tell the user there are records to review, or
2. Create a Form based on your unmatched query and just open it, so the user can review the results.

 

[stonegold87]

Okay, good. Now, you have two options:
1. You can use DCount() to tell the user there are records to review, or
2. Create a Form based on your unmatched query and just open it, so the user can review the results.

Yes i can do both but i want a reminder a msg box to show unmatched records is it possible?

 

[nortonm]

Yes i can do both but i want a reminder a msg box to show unmatched records is it possible?

maybe something like If DCount("*", "YourQueryName") > 0 Then MsgBox "There are unmatched queries for you to deal with.", or did you mean you want the msgbox to actually contain the record numbers?

 

[stonegold87]

maybe something like If DCount("*", "YourQueryName") > 0 Then MsgBox "There are unmatched queries for you to deal with.", or did you mean you want the msgbox to actually contain the record numbers?

Yes i want Record ID in msg box

 

[theDBguy]

Yes i want Record ID in msg box

Lots of options there too. But, may I propose that opening a Form (you can make it look like a message box, if you like) would give you more options and control and also give the user lots of options to react. Just my 2 cents...

 

[nortonm]

I agree with theDBGuy; I guess you could try and concatenate all the records in the query using the superb Allen Brown instructions Microsoft Access tips: Concatenate values from related records (allenbrowne.com) - create a query that concatenates the Record IDs from that query, add the concat query to your form set as not visible, then have your message box refer to that concatenated field in the msgbox text string. Do-able but really quite messy. There's probably an easier way that I don't know of.

Actually, looking at that page again you could squeeze the ConcatRelated code into the control source of a text box?

 

[Isaac]

You can loop through the query results in a recordset, then display them in a message box, if there aren't going to be more than 5 or 10

 

[Pat Hartman]

Create a form bound to the unmatched query.
When the app opens, run a dcount on that query. If the query returns at least one record, open the form, otherwise open normally.

 

[stonegold87]

Create a form bound to the unmatched query.
When the app opens, run a dcount on that query. If the query returns at least one record, open the form, otherwise open normally.

Thx Pat will definitely try your solution

 

[Pat Hartman]

Let us know how it works

 

[stonegold87]

Thanks everyone for your time and effort ,
with Dcount IF Statement And Concatenation I achieved desired results
By Showing unmatched records in a Form gives more control and flexibility.

 

[theDBguy]

Thanks everyone for your time and effort ,
with Dcount IF Statement And Concatenation I achieved desired results
By Showing unmatched records in a Form gives more control and flexibility.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[stonegold87]

Need one more suggestion i hide text box with if statement only visible when dcount>1 but this text box is on Dashboard Form which is always open after dealing with unmatched records when i click Dashboard Form nothing happens unless i close and open Dashboard
what should i do ?
Code:

Private Sub Form_Load()
If Me.TC1 > 0 Then
Me.TC2.Visible = True
Me.TC3.Visible = True
Else
Me.TC2.Visible = False
Me.TC3.Visible = False
End If
End Sub

 

[theDBguy]

...nothing happens...

What did you want to happen? If you want to "unhide" it after "dealing" with unmatched records, we'll need to understand how you are "dealing" with the unmatched records. Did you end up using a Form to display the unmatched records? If so, how exactly are you opening and closing it and where do you perform the "hiding" of the textbox?

 

[stonegold87]

What did you want to happen? If you want to "unhide" it after "dealing" with unmatched records, we'll need to understand how you are "dealing" with the unmatched records. Did you end up using a Form to display the unmatched records? If so, how exactly are you opening and closing it and where do you perform the "hiding" of the textbox?

ok i'l try to explain with Unmatched Query i get records
TC1 is Text Box on Dashboard (Dcount in Control Source)
TC2 is Text Box on Dashboard (TC1 &"Some Statement")
TC3 is Command Btn (Open Form Customers to Deal with unmatched Records)
Vba code in Dashboard On Load Event
I just want to show Text box and Cmd Btn only if dcount >1

 

[theDBguy]

ok i'l try to explain with Unmatched Query i get records
TC1 is Text Box on Dashboard (Dcount in Control Source)
TC2 is Text Box on Dashboard (TC1 &"Some Statement")
TC3 is Command Btn (Open Form Customers to Deal with unmatched Records)
Vba code in Dashboard On Load Event
I just want to show Text box and Cmd Btn only if dcount >1

So, what have you tried? Was it something like this?

If Me.TC1 > 1 Then
    Me.TC2.Visible = True
    Me.TC3.Visible = True
Else
    Me.TC2.Visible = False
    Me.TC3.Visible = False
End If