Multi-Function Module/Macro/VBA

[andysgirl8800]

I'm sorry to report that it didn't work. I put that code behind my button. When I clicked it, nothing happened. I didn't get a message box, and it doesn't see my duplicate.

Would it help if I let you know the form (frmHalfTab) I am using the button (cmdDuplicates) on is built on a query (qryHalfTab2) which is built on my table (tblHalfTab2). The SQL for the (qryHalfTab2) is as follows:

SELECT tblHalfTab2.MemberNum, tblHalfTab2.MBRLast, tblHalfTab2.MBRFirst, tblHalfTab2.MRBAddress, tblHalfTab2.MBRCity, tblHalfTab2.MRBState, tblHalfTab2.MBRZip, tblHalfTab2.MBRDrug, tblHalfTab2.MDLast, tblHalfTab2.MDFirst, tblHalfTab1.Field2, tblHalfTab2.MemberContacted
FROM tblHalfTab2 INNER JOIN tblHalfTab1 ON tblHalfTab2.MBRDrug = tblHalfTab1.Field1;

I'm not sure if that changes anything or not. Sometimes, it's just one tiny thing that can throw off the whole program. Sucks. Also, the field most likely to initially suggest a duplicate exists is the (MemberNum) field. Each member has his or her own ID number. However, they may be taking more than one medication, so the second field that would prove it's a duplicate is the (MBRDrug), so that if both the (MemberNum) field and the (MBRDrug) field are identical for more than one record, then it's safe to assume that he or she has already been contacted and can be removed from the list. I had included (MBRLast) as a further indicator, but it's not totally necessary, more for the user's information than anything else. Thank you so much for your help. I've gotten much further already than I thought I would. Hopefully I can get this silly duplicate thing ironed out and be good to go!

 

[Bat17]

if you are not even getting the message then this bit must be ending the code

If Nz(Me.MemberNum, "") = "" Or Nz(Me.MBRDrug, "") = "" Or Nz(Me.MBRLast, "") = "" Or Me.MemberContacted = True Then
Exit Sub
End If

It is set to only run if something is in
MemberNum
MBRDrug
MBRLast
and MemberContacted is not ticked.
For checking try replacing it with this which will tell you why it is bombing out.

If Nz(Me.MemberNum, "") = "" Then
    MsgBox "No value - MemberNum"
    Exit Sub
ElseIf Nz(Me.MBRDrug, "") = "" Then
    MsgBox "No value - MBRDrug"
    Exit Sub
ElseIf Nz(Me.MBRLast, "") = "" Then
    MsgBox "No value - MBRLast"
    Exit Sub
ElseIf Me.MemberContacted = True Then
    MsgBox "MemberContacted = true"
    Exit Sub
End If

HTH

Peter

 

[andysgirl8800]

Sadly, I'm certain all the appropriate fields are completed and all the tick boxes are currently empty. I've only got 5 test records in the table so far, with only one pair of duplicates, so ideally, I should be left with only 3 unticked records left. I input the additional code to check for null values, but it didn't return any.

 

[Bat17]

You got no message when you clicked the button? that sounds like the code is not running at all!

Put a break point at the start of the code and try following it through to see what happens.

peter

 

[andysgirl8800]

Nope, no message at all. How do I put in a break? I've repasted the code below (I don't know how to put it in the spearate little boxes like you did)


Private Sub cmdDuplicates_Click()

Dim intAllow
Dim strMsg As String
Dim strSql As String
Dim strSql2 As String
'check that MemberNum, MBRDrug and MBRLast all have data and
'that MemberContaced has not already been checked.
If Nz(Me.MemberNum, "") = "" Or Nz(Me.MBRDrug, "") = "" Or Nz(Me.MBRLast, "") = "" Or Me.MemberContacted = True Then
Exit Sub
End If

' string for update
strSql = "UPDATE tblHalfTab2 SET tblHalfTab2.MemberContacted = Yes "
strSql = strSql & "WHERE (((tblHalfTab2.MemberNum) =" & Me.MemberNum & ") And "
strSql = strSql & "((tblHalfTab2.MBRDrug) ='" & Me.MBRDrug & "'" & ") And "
strSql = strSql & "((tblHalfTab2.MBRLast) ='" & Me.MBRLast & "'))"
' string for lookup
strSql2 = "MemberNum =" & Me.MemberNum & " And MBRDrug ='" & Me.MBRDrug
strSql2 = strSql2 & "'" & " And MBRLast ='" & Me.MBRLast & "'"

strMsg = "This member has already been contacted, do you wish to contact again?"

DoCmd.RunCommand acCmdSaveRecord ' save record to get correct record count.
If DCount("MemberNum", "tblHalfTab2", strSql2) > 1 Then
intAllow = MsgBox(strMsg, vbYesNo, "Duplicate Warning")
If intAllow = 7 Then ' no
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.RunCommand acCmdRefresh
DoCmd.SetWarnings True
End If
End If
End Sub

 

[Bat17]

see what you get with this

Peter

Private Sub cmdDuplicates_Click()

Dim intAllow
Dim strMsg As String
Dim strSql As String
Dim strSql2 As String
'check that MemberNum, MBRDrug and MBRLast all have data and
'that MemberContaced has not already been checked.
If Nz(Me.MemberNum, "") = "" Then
    MsgBox "No value - MemberNum"
    Exit Sub
ElseIf Nz(Me.MBRDrug, "") = "" Then
    MsgBox "No value - MBRDrug"
    Exit Sub
ElseIf Nz(Me.MBRLast, "") = "" Then
    MsgBox "No value - MBRLast"
    Exit Sub
ElseIf Me.MemberContacted = True Then
    MsgBox "MemberContacted = true"
    Exit Sub
End If

End If

' string for update
strSql = "UPDATE tblHalfTab2 SET tblHalfTab2.MemberContacted = Yes "
strSql = strSql & "WHERE (((tblHalfTab2.MemberNum) =" & Me.MemberNum & ") And "
strSql = strSql & "((tblHalfTab2.MBRDrug) ='" & Me.MBRDrug & "'" & ") And "
strSql = strSql & "((tblHalfTab2.MBRLast) ='" & Me.MBRLast & "'))"
' string for lookup
strSql2 = "MemberNum =" & Me.MemberNum & " And MBRDrug ='" & Me.MBRDrug
strSql2 = strSql2 & "'" & " And MBRLast ='" & Me.MBRLast & "'"

strMsg = "This member has already been contacted, do you wish to contact again?"

DoCmd.RunCommand acCmdSaveRecord ' save record to get correct record count.
If DCount("MemberNum", "tblHalfTab2", strSql2) > 1 Then
intAllow = MsgBox(strMsg, vbYesNo, "Duplicate Warning")
If intAllow = 7 Then ' no
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.RunCommand acCmdRefresh
DoCmd.SetWarnings True
End If
End If
End Sub

 

[andysgirl8800]

I don't get it...nothing happened again, almost like I have absolutely nothing behind the button....it's not recognising the code at all...?

 

[Bat17]

goto the properties of the button, make sure it has [Event Procedure] set for its click event and then hit the duild button and make sure it takes you to your code.

Peter

 

[andysgirl8800]

Yep. It's in the right place OnClick and it took me to the code....

 

[andysgirl8800]

OH! I sould also let you know that I deleted the contents of one of the fields in my form, MBRLast and clicked the button to see what would happen, and it DID pop up with "No Value-MBRLast"....so the button programming did work for that.....but it still doesn't seem to be "seeing" the duplicates....and if it's not recognising the dups and all the fields are filled in, then it won't do anything, right?

 

[Bat17]

and if it's not recognising the dups and all the fields are filled in, then it won't do anything, right?

Right, so the problem is in finding the duplicates.
I will knock up a small DB in the morning (my time!) to show how it should be working and then maybe you will be able to spot the difference that is making your fail.

Only thing I can think of at the moment is data types, I have assumed that MemberNum is a number and that MBRDrug and MBRLast are text. If that is not the case then it could be messing things up.

Peter

 

[andysgirl8800]

That's a very good question....let me check....MemberNum Format= General Number, no specified format for MBRDrug or MBRLast.

I'll warn you that zipped DBs don't seem to be able to download to my work PC, might be a firewall issue. My noodle is really getting baked on this one. I really thought it would be a simple thing to do. I'm sorry for the complications.

 

[andysgirl8800]

As a different challenge, might you be able to take a look at my post in the Forms forum? I think that's just a simple code change, but after searching and messing around with the code, I can't seem to make it work properly. I think it's titled "Word Merge Printing Options".

 

[andysgirl8800]

So I've been fiddling with the code some more, and I can't get it to recognise the duplicate records. I keep thinking I ought to be able to use the existing duplicate query created with the query wizard. Is there some way to incorporate it into my form to not just find them, but mark them as contacted? Can I build it into a macro?

 

[andysgirl8800]

Ok ladies and gents...
This is the last little irritating coding problem I'm trying to work out. Haven't been able to make much progress, either by searching or by asking. I can SEE the duplicate records with the pre-fabricated duplicate query wizard, but I can't seem to DO anything with that data. I want those records REMOVED from my table. So the records that appear in the duplicate query results need to be removed from the table completely. How can I do this?

 

[Bat17]

Just to make sure I have it right this time
you want to check for duplicates in tblHalfTab2
You want to remove the original and duplicates from that table.
These 3 fields all have to be the same for the record to be a duplicate:-
MemberNum
MBRDrug
MBRLast


Is there a unique ID field in tblHalfTab2 ?

Peter

 

[andysgirl8800]

Yes. The unique ID is the MemberNum.

 

[Bat17]

Yes. The unique ID is the MemberNum.

Then there can be no duplicates that include member number

Would it be possible for you to create a new db and just import the bits that are relevant to this problem and post it for us to have a look at?

Peter

 

[andysgirl8800]

Perhaps I'm misunderstanding. The unique ID is the MemberNum...it can have duplicates because I have existing data in the table, then I add more data (in a lump amount) and then there is the possibility of duplicates. The column is set to allow duplicates because the same member may be taking more than one drug. It's why the drug name needs to be an additional qualifier to identify the records that are duplicates, versus just the members that have duplicate appearances.

 

[Bat17]

OK then it is not a unique ID
No problem, you can always add an Autonumber fields to give a unique reference to delete by.

Looking at the SQL of your Duplicate query again it appears that it is only finding duplicates based on MemberNum not all three fields at once.
Are you sure that in your test data there are records with all three fields matching? if not that would explain the problem with Dlookup

Peter