Multi-Function Module/Macro/VBA

[andysgirl8800]

Hi all!
I'm looking for some help, but not really sure where to post this question. I want to put a button on a form (frmHalfTab)that runs a macro/module/ or VBA (not sure which will be the best approach) that will accomplish simultaneous tasks.

1) When clicked, I would like it to check the contents of one field (MBRDrug) with an "If" statement and auto-fill a second field (MBRDrugAlt) with the results of the "If" statement. For example, if the MBRDrug field says "Zocor 10MG" I want it to auto-fill the MBRDrugAlt with "Zocor 20MG". The drug names and their partner alternates are stored on a table (tblHalfTab1). I'm not sure how to build this "If" statement to use that table.
2) I then want it to check for a duplicate entry in the field MemberNum AND MBRDrug so that if member number 111 with the drug "Zocor 10MG" appears twice, a message box will appear saying something like "this member has already been contacted, do you wish to contact again?" and two buttons with "yes" or "no", clicking on "yes" will return to the form with no changes, clicking on "no" will check a box called MemberContacted.
3) Finally, I want to check for all records with MemberContacted unchecked, and merge them into an MSWord document/letter. I know how to do the merge, just not the check for the MemberContacted parameter.

Is this all possible to do with a single button, other than the pop-up interruption for duplicate records? I could really use some help in getting started. Maybe if I had a point in the right direction, I could figure it out on my own. Thank you all for your valuable wealth of knowledge!

 

[Bat17]

1) you should be able to automate this by adding the table tblHalfTab1 to the query behind the form and linking the two fields.

2) not sure if you are looking for duplicates on the form or within previously saved data. You could use a Dcount function to test for it in previous records.

3) for the mailmerge you would need to base it on a query that has the checkbox as a criteria.

Peter

 

[andysgirl8800]

Bat17-
Thanks for your help, I appreciate it.

1) How do I link the two fields? Should I use an If/Then sort of thing?

2) I was able to build a duplicate search query, just need to figure out how to best incorporate it into the button programming, might be able to get that one on my own, soon as I figure out the first part.

3) Would this also be an If/Then sort of thing? Where would I put this criteria in the query?

Again, thanks for your input!

 

[Bat17]

1) add the table to the query the form is based on. tou join the fields you can just drag the field from one table and drop it on the appropriate field on the other table.

2)If you have a query that works then you should be able to Dcount that

Dim intAllow
If DCount("ID", "YourQuery") Then
  intAllow = MsgBox("This member has already been contacted, do you wish to contact again?", vbYesNo, "Duplicate Warning")
  If intAllow = 6 Then ' yes
    Me.MemberContaced = True
    End If
End If

3) just add False to the criteria for the MemberContaced field

HTH

Peter

 

[andysgirl8800]

I don't think the coffee has kicked in yet this morning, because I seem to be having a blonde moment...

1) I'm not sure I understand joining the fields. The form is build on a table (tblHalfTab2), not a query. Should I change this? The data for the two fields (MBRDrug) and (MBRDrugAlt) are both located in (tblHalfTab1) in two seperate columns. I COULD do this as a cascading combo, but I'm trying to minimize user interface as much as possible. The more I can automate, the happier my boss will be.

2) That code looks great, where should I put it...behind a button? In a query? As the control source of the (MBRDrugAlt) field on the form?

3) Hopefully, if I can work out these other things, this third step should fall into place easily enough.

Thank you so much for your advice and input, you're being very helpful!

 

[Bat17]

The code could either go with a button or use the afterupdate event of the field when they enter the data you want to check

Yes base the form on a query, you can just drag the * from the table to the grid if you like. use both tables and create the join on the MBRDrug field. if you have fields with the same name in both tables you will need to reselect them in the form so it knows which table to write to.

Peter

 

[Bat17]

Been thinking
Forget the query!
MBRDrug on the form should be a two colum combo bound to MBRDrug, with its rowsource set to
SELECT tblHalfTab1.MBRDrug, tblHalfTab1.MBRDrugAlt FROM tblHalfTab1;
set its column count to 2 and second column width to 0
MBRDrugAlt is an unbound text box with its control source set to =[MBRDrug].[column](1)
When a drug is selected the Alt will automaticaly update it's self.

HTH

Peter

 

[andysgirl8800]

Coolies!
Got that working now. The MBRDrugAlt is populating as it should. I decided not to use the cascading combo, as my boss wants to avoid interacting with the form. I took your earlier suggestion and went with the query and tied my form to the query. Thanks for all your help. Now, I need to eliminate the duplicates...how should I do that? Can I use an Iif statement in the query criteria? I need to make sure records that have the (MemberNum) AND (MBRDrug) AND (MBRLast) fields as identical are removed from the query results. Can you help with that? Thanks again.

 

[Bat17]

try this code

Sub checkDupes()
Dim intAllow
Dim strMsg 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.MemberContaced = True Then
 Exit Sub
End If

strMsg = "This member has already been contacted, do you wish to contact again?"
If DCount("ID", "YourQuery") Then
  intAllow = MsgBox(strMsg, vbYesNo, "Duplicate Warning")
  If intAllow = 7 Then ' no
    Me.MemberContacted= True
    End If
End If
End Sub

call it from the after update events of MemberNum, MBRDrug and MBRLast. If they have all been filled in then it will check againt your query for duplicates unless the Tick box has already been done.

Peter

 

[andysgirl8800]

Thanks,
I put in that code, and modified it to represent the correct field names, but get an error that says "The expression you entered as a query parameter produced this error: The object doesn't contain the Automation object 'ID' ". Does this have to do with the primary key of the query the form is built on, or the duplicate query I built? In other words, the code:

If DCount("ID", "YourQuery")

should "YourQuery" be the query the form is based on (qryHalfTab2) or the duplicates query that I built (DUPtblHalfTab2)?

 

[Bat17]

DUPtblHalfTab2 and ID is some field in there

Peter

 

[andysgirl8800]

With your help, I am slowly getting through this. Thanks. Ok, I have all the coding in correctly...and I THINK it's working, but now I'm having a problem with the check box (MemberContacted) on the form. If I leave it as unbound, checking one record ends up checking them all. If I try to bind it to the field in the table/query on which the form is built, I can't check any of them, says the "recordset cannot be updated". How do I get around this? (I hate how picky access can be!)

 

[andysgirl8800]

ok,
I finally resolved the checkbox issue by changing the RecordsetType property on the form itself to allow inconsistent updates. Now, the code you gave me, I put behind a button to run. However, it is not actually finding any duplicates (I created one in the table to test this), all it's doing is saying the record I happen to be on is the duplicate one and is asking me if I want to contact them again...I checked the duplicate query I created and it IS finding the duplicate I created. What's gone wrong?

 

[Bat17]

all it's doing is saying the record I happen to be on is the duplicate one and is asking me if I want to contact them again

I thought that was all it was meant to do then, depending on the answer, check the box.

What did I miss?

Peter

 

[andysgirl8800]

Sorry for the confusion...
An example may be more helpful. When I click the button that uses the code you provided, it doesn't seem to actually be FINDING the duplicate records. It will simply identify the current record as the duplicate, which it isn't.

In other words, if I happen to be on John Johnson's record and he DOES NOT have a duplicate record, when I click the button, it is telling me that John Johnson's record IS the duplicate. However, it's Lisa Linchen's record that has the duplicate (the only duplicate so far out of my 9 test records) and the button is not seeing the two Lisa Linchen records. Does that make sense? Unless I manually go to Lisa Linchen's record and press the button, the code is not seeing the duplicates.

Also, (assuming I get it to actually recognise the dups) how can I get it to put the check mark on BOTH the original record AND the duplicate record, so that BOTH of Lisa Linchen's records are now checked off as already contacted...make sense? (Stupid Access!)

 

[Bat17]

OK
Duplicate query, how have you created it? using the query wizard so that it is finding all duplicates or a query that references the form? You need a query that use a reference to the form fields that make up a duplicate.
if you run your query when you are in John Johnson's record how many records does it return?

how can I get it to put the check mark on BOTH the original record AND the duplicate record, so that BOTH of Lisa Linchen's records are now checked off as already contacted...make sense?

Nope
You add a record for john doe and you want to contact him, then you add another record for john doe that duplicates the first and now you either want to contact him twice or not at all
I suspect what you need is a method that sets all of the people that you have contacted previously to "MemberContacted = true" when you run your mail merge.


Peter

 

[andysgirl8800]

I created the duplicate query using the wizard. My mistake in assuming it would actually do what I wanted it to!

When I run the query with the button programming within the form, and I'm on John Johnson's record, it only finds that one record and tells me that John Johnson's record is the duplicate. If I go to any other record, let's say Timothy Timmons and press the button, then it says Timothy is the duplicate. It doesn't look like it is actually SEEING the duplicate records, because it isn't finding either the original Lisa Linchen's record, or her duplicate record.

When I open the query outside of the form and it's programmed button, it will find only Lisa Linchen's original and duplicate record. If it's finding both records on the query as a stand alone, why won't it find them in the form? Also, if it will find both the original and the duplicate, why can't I tell it to then mark them BOTH as contacted. I think you're right that I need something to indicate who has and hasn't been contacted, but the person who did the first mailing, or the first round of contacting, did not do it within MSAccess, but did it manually. So all I have is a list of names from the first mailing. I want to upload that list of names to a table, then add the second list of names for the new mailing, then check for duplicates to avoid contacting the same person twice. If I can't check off both records, then one will still appear in my "to be contacted" list and will end up getting contacted twice anyway. Is this just more trouble than it's worth? It seems that I should be able to "talk" to Access properly to use the data in the duplicates query to manipulate my data, but I can't seem to use the right language here.

 

[Bat17]

The duplicate query just finds duplicates in a set of data so it will find the duplicate every time
can you post the SQL of the query you have and the names of the fields on the form that indicate the duplicate and I should be able to rework it for you.

we can add code that will go back and tick existing records for you as well.

Peter

 

[andysgirl8800]

You're awesome! That would be fantastic! I've pasted the code below:

For the existing duplicate query, the SQL is as follows:

SELECT DISTINCTROW tblHalfTab2.MemberNum, tblHalfTab2.MBRDrug, tblHalfTab2.MBRLast
FROM tblHalfTab2
WHERE (((tblHalfTab2.MemberNum) In (SELECT [MemberNum] FROM [tblHalfTab2] As Tmp GROUP BY [MemberNum] HAVING Count(*)>1 )))
ORDER BY tblHalfTab2.MemberNum;

The three fields that indiacte whether a record is a duplicate or not are:

MemberNum
MBRDrug
MBRLast

Let me know if you need any additional information.

 

[Bat17]

Hi
Try the code below and see if it does what you want.
I have had to make it save the record before checking for duplicates otherwise the the current record being added would not show up in the count. You may need to add extra checking to ensure that all required fields are filled in before running it.
I have assumed that MemberContacted is in tblHalfTab2 and not a related table.
It will tick the boxes of previous records but if you cange your mind you will have to go back and untick them manualy

Peter

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