duplicate check before report runs

[KateB]

Hi,

I have a form that has been added to over time. The latest addition is a
check box that generates a letter to the GP if a patient is on a particular
drug (user activated). A 2nd table (tblPPIreview) has been set up to hold
the information returned via that letter. (It is faxed out and faxed back).

What I want to do is this. When the user checks the box for the letter, it
'sends' the patient's personal unique identifier (NHS number) to the 2nd
table so that it is recorded in that table that a letter was sent. However,
if the NHS number already exists a message pops up saying something like
"previously reviewed, do you wish to continue?" It may be that it is
appropriate to continue if a period of time has elapsed, but doesn't need
doing on a monthly basis, therefore the NHS number will not be unique to
table 2.

Its not a big database so it could be done manually, but I'm just trying to
save myself time. Is it possible for the tick box to do all three things
(generate the report, add NHS number to table 2, check for dupicates) or am I
asking too much? Its quite a busy form already so I didn't want to add more
buttons and boxes if I didn't have to. In reality, the whole DB could do
with rewriting, but time, knowledge and enormity of it has put me off!

I'm self-taught in Access so always try for very simple solutions. If its
very complicated or just not the 'right' way to do then please say so and
I'll find another way! Many thanks for your time,

Kate

 

[Tom van Stiphout]

On Wed, 10 Mar 2010 03:58:01 -0800, KateB

Certainly it can do three things. You probably wrote some code in the
checkbox_AfterUpdate event. Organize your code and do three things
there:
private sub myCheckbox_AfterUpdate()
'*** Check for existence ***
if dcount("NHSnumber", "tblPPlreview", "NHSnumber")>0 then
if MsgBox("Already there - want another one?", vbQuestion or
vbYesNo) = vbNo then Exit Sub
end if

'*** Send email ***
'Put your email code here.

'*** Add row to tblPPlreview ***
'Put that code here. Perhaps run an Append query.

end sub

-Tom.
Microsoft Access MVP

 

[KenSheridan via AccessMonster.com]

Kate:

Presumably the tblPPIreview contains a date column for when the letter is
sent, ReviewDate in the example below. You could make the message a little
more informative by including the date of the latest review with regard to
the current the patient. In the check box's AfterUpdate event procedure out
code like this:

Dim ctrl as Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

strCriteria = "[NHS Number] = """ & Me.[NHS Number] & """"

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confrmation
' to continue
varDate = DMax("[ReviewDate]", "[tblPPIreview]", strCriteria)
If Not IsNull(vardate) Then
strMessage = "Last review on " & _
Format(vardate, "dd mmmm yyyy") & _
vbNewline & "Do you wish to continue?")
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
'code to insert row into tblPPIreview and generate letter goes
here
Else
' if user answers No set check box back to False
ctrl = False
End If
End If
End If

The above assumes that the NHS Number column is a text data type, which would
be most appropriate as, even though its numeric, it is really an encoding
system rather than a 'number' as such, having no ordinal or cardinal
significance. If a number data type has been used, however, amend the code
as follows:

strCriteria = "[NHS Number] = " & Me.[NHS Number]

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

Oops! Missed a line:

Dim ctrl as Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

' add this line
ctrl = Me.ActiveControl

strCriteria = "[NHS Number] = """ & Me.[NHS Number] & """"

Ken Sheridan
Stafford, England

 

[KenSheridan via AccessMonster.com]

I think I'm in need of NHS treatment! Just spotted a redundant closing
parenthesis:

strMessage = "Last review on " & _
Format(vardate, "dd mmmm yyyy") & _
vbNewline & "Do you wish to continue?")

should have been:

strMessage = "Last review on " & _
Format(vardate, "dd mmmm yyyy") & _
vbNewline & "Do you wish to continue?"

Ken Sheridan
Stafford, England

 

[KateB]

Hi Ken,

Thanks for your help with this. I've been trying to get it to work but am
struggling! Firstly, the extra line you gave me
ctrl = Me.ActiveControl
gives me a runtime error '91': Object variable or With block variable not
set. If I remove it I get the same error further along at
If ctrl = True Then

Second: the NHS number is a 10 digit number - it has to be saved as a number
because it is possible to check its a valid number with a long algorythm.
(This isn't done in this database, but can be pasted into an excel sheet I
have to be verified if necessary). The field is double rather than integer
as it wouldn't accept a 10 digit number otherwise. I don't know if that
makes a difference to the Dim strCriteria As String?

The third problem is that I am checking the NHS number between 2 tables so I
assume I have to put the table name in somewhere in this line:

strCriteria = "[NHS Number] = " & Me.[NHS Number]

but can't solve that either.

Can you offer me any more advice to get a bit further down the line?! Your
help is very much appreciated and I'll make sure the NHS treat you well!!

Kate

Kate:

Presumably the tblPPIreview contains a date column for when the letter is
sent, ReviewDate in the example below. You could make the message a little
more informative by including the date of the latest review with regard to
the current the patient. In the check box's AfterUpdate event procedure out
code like this:

Dim ctrl as Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

strCriteria = "[NHS Number] = """ & Me.[NHS Number] & """"

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confrmation
' to continue
varDate = DMax("[ReviewDate]", "[tblPPIreview]", strCriteria)
If Not IsNull(vardate) Then
strMessage = "Last review on " & _
Format(vardate, "dd mmmm yyyy") & _
vbNewline & "Do you wish to continue?")
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
'code to insert row into tblPPIreview and generate letter goes
here
Else
' if user answers No set check box back to False
ctrl = False
End If
End If
End If

The above assumes that the NHS Number column is a text data type, which would
be most appropriate as, even though its numeric, it is really an encoding
system rather than a 'number' as such, having no ordinal or cardinal
significance. If a number data type has been used, however, amend the code
as follows:

strCriteria = "[NHS Number] = " & Me.[NHS Number]

Ken Sheridan
Stafford, England

Hi,

I have a form that has been added to over time. The latest addition is a
check box that generates a letter to the GP if a patient is on a particular
drug (user activated). A 2nd table (tblPPIreview) has been set up to hold
the information returned via that letter. (It is faxed out and faxed back).

What I want to do is this. When the user checks the box for the letter, it
'sends' the patient's personal unique identifier (NHS number) to the 2nd
table so that it is recorded in that table that a letter was sent. However,
if the NHS number already exists a message pops up saying something like
"previously reviewed, do you wish to continue?" It may be that it is
appropriate to continue if a period of time has elapsed, but doesn't need
doing on a monthly basis, therefore the NHS number will not be unique to
table 2.

Its not a big database so it could be done manually, but I'm just trying to
save myself time. Is it possible for the tick box to do all three things
(generate the report, add NHS number to table 2, check for dupicates) or am I
asking too much? Its quite a busy form already so I didn't want to add more
buttons and boxes if I didn't have to. In reality, the whole DB could do
with rewriting, but time, knowledge and enormity of it has put me off!

I'm self-taught in Access so always try for very simple solutions. If its
very complicated or just not the 'right' way to do then please say so and
I'll find another way! Many thanks for your time,

Kate

 

[KenSheridan via AccessMonster.com]

Kate:

It must have been getting late when I last replied! The line should have
been:

Set ctrl = Me.ActiveControl

What the line does is set the object variable ctrl to return a reference to
the form's currently active control, which, as the code is in the check box's
AfterUpdate event procedure, is the check box. You'll see that later in the
code the ctrl variable is used without the 'Set'. That's because the code is
then referring to the value of the control. The Value property is the
default property of a control so does not have to be included specifically,
so a line such as ctrl = False is the same as ctrl.Value = False.

Using a number data type for the NHS number merely means its value doesn't
need to be wrapped in quotes.

With the strCriteria = "[NHS Number] = " & Me.[NHS Number] line you don't
need to include the table name. What this does is build a string expression
which is then used by the DMax function as its criterion in the following
line:

varDate = DMax("[ReviewDate]", "[tblPPIreview]", strCriteria)

As you see the table name is the second argument when calling the function,
so does not need to be in the criterion string. So if he current NHS number
in the form is 1234567890 the expression for the criterion would be:

[NHS Number] = 1234567890

So what the DMax function is asking is:

What is the latest ReviewDate in the table tblPPIreview where the NHS number
is 1234567890?

If no row exists in the table for that NHS number a Null will be returned,
and assigned to the varDate variable, so the following line:

If Not IsNull(vardate) Then

means that the rest of the code will only be executed and the message box
displayed if the return value is not a Null, i.e. its a real date value.

BTW you've invented a new word, algorythm, which sounds like it means musical
seaweed (alga + rhythm). I think you had algorithm in mind; unless the NHS
has come up with a treatment which involves eating laver bread while
listening to Charlie Parker records, of course?

Ken Sheridan
Stafford, England

Hi Ken,

Thanks for your help with this. I've been trying to get it to work but am
struggling! Firstly, the extra line you gave me
ctrl = Me.ActiveControl
gives me a runtime error '91': Object variable or With block variable not
set. If I remove it I get the same error further along at
If ctrl = True Then

Second: the NHS number is a 10 digit number - it has to be saved as a number
because it is possible to check its a valid number with a long algorythm.
(This isn't done in this database, but can be pasted into an excel sheet I
have to be verified if necessary). The field is double rather than integer
as it wouldn't accept a 10 digit number otherwise. I don't know if that
makes a difference to the Dim strCriteria As String?

The third problem is that I am checking the NHS number between 2 tables so I
assume I have to put the table name in somewhere in this line:

strCriteria = "[NHS Number] = " & Me.[NHS Number]

but can't solve that either.

Can you offer me any more advice to get a bit further down the line?! Your
help is very much appreciated and I'll make sure the NHS treat you well!!

Kate

Kate:

[quoted text clipped - 70 lines]

 

[KateB]

Thanks Ken - I'm now past that error and on to the next! I solved one myself
- the NHS number is on the master form, not the subform, so I amended the
strCriteria to this:
strCriteria = "[NHSnumber] = " & Forms!AlertOrg9!zz_subfrm_fax2.Form!NHSnumber
(Is that right?!)

It is now erroring with the following message:
Run-time error '3075':
Syntax error (missing operator) in query expression '[NHSnumber] = '

and highlights this:
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)

I have checked and double checked the field names, punctuation etc and can't
see anything wrong. It was doing this before I referenced the sub-form, so I
don't think that's impacting on it. Can you help me again please?!

I quite like the thought of musical seaweed. I'd like to say that the NHS
is forward thinking and has begun diversifying into more unusual treatments,
but of course that's not true. I thought laver bread was a dough product the
French washed with, until I googled both it and Charlie Parker. I'm not big
on jazz, prefer 80s classics myself!

Many thanks yet again,
Kate

Kate:

It must have been getting late when I last replied! The line should have
been:

Set ctrl = Me.ActiveControl

What the line does is set the object variable ctrl to return a reference to
the form's currently active control, which, as the code is in the check box's
AfterUpdate event procedure, is the check box. You'll see that later in the
code the ctrl variable is used without the 'Set'. That's because the code is
then referring to the value of the control. The Value property is the
default property of a control so does not have to be included specifically,
so a line such as ctrl = False is the same as ctrl.Value = False.

Using a number data type for the NHS number merely means its value doesn't
need to be wrapped in quotes.

With the strCriteria = "[NHS Number] = " & Me.[NHS Number] line you don't
need to include the table name. What this does is build a string expression
which is then used by the DMax function as its criterion in the following
line:

varDate = DMax("[ReviewDate]", "[tblPPIreview]", strCriteria)

As you see the table name is the second argument when calling the function,
so does not need to be in the criterion string. So if he current NHS number
in the form is 1234567890 the expression for the criterion would be:

[NHS Number] = 1234567890

So what the DMax function is asking is:

What is the latest ReviewDate in the table tblPPIreview where the NHS number
is 1234567890?

If no row exists in the table for that NHS number a Null will be returned,
and assigned to the varDate variable, so the following line:

If Not IsNull(vardate) Then

means that the rest of the code will only be executed and the message box
displayed if the return value is not a Null, i.e. its a real date value.

BTW you've invented a new word, algorythm, which sounds like it means musical
seaweed (alga + rhythm). I think you had algorithm in mind; unless the NHS
has come up with a treatment which involves eating laver bread while
listening to Charlie Parker records, of course?

Ken Sheridan
Stafford, England

Hi Ken,

Thanks for your help with this. I've been trying to get it to work but am
struggling! Firstly, the extra line you gave me
ctrl = Me.ActiveControl
gives me a runtime error '91': Object variable or With block variable not
set. If I remove it I get the same error further along at
If ctrl = True Then

Second: the NHS number is a 10 digit number - it has to be saved as a number
because it is possible to check its a valid number with a long algorythm.
(This isn't done in this database, but can be pasted into an excel sheet I
have to be verified if necessary). The field is double rather than integer
as it wouldn't accept a 10 digit number otherwise. I don't know if that
makes a difference to the Dim strCriteria As String?

The third problem is that I am checking the NHS number between 2 tables so I
assume I have to put the table name in somewhere in this line:

strCriteria = "[NHS Number] = " & Me.[NHS Number]

but can't solve that either.

Can you offer me any more advice to get a bit further down the line?! Your
help is very much appreciated and I'll make sure the NHS treat you well!!

Kate

Kate:

[quoted text clipped - 70 lines]

Kate

 

[KenSheridan via AccessMonster.com]

Kate:

If the code is in the subform’s module and the NHSnumber control is in the
parent form’s module, the following should work:

strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

Thanks Ken - I'm now past that error and on to the next! I solved one myself
- the NHS number is on the master form, not the subform, so I amended the
strCriteria to this:
strCriteria = "[NHSnumber] = " & Forms!AlertOrg9!zz_subfrm_fax2.Form!NHSnumber
(Is that right?!)

It is now erroring with the following message:
Run-time error '3075':
Syntax error (missing operator) in query expression '[NHSnumber] = '

and highlights this:
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)

I have checked and double checked the field names, punctuation etc and can't
see anything wrong. It was doing this before I referenced the sub-form, so I
don't think that's impacting on it. Can you help me again please?!

I quite like the thought of musical seaweed. I'd like to say that the NHS
is forward thinking and has begun diversifying into more unusual treatments,
but of course that's not true. I thought laver bread was a dough product the
French washed with, until I googled both it and Charlie Parker. I'm not big
on jazz, prefer 80s classics myself!

Many thanks yet again,
Kate

Kate:

[quoted text clipped - 81 lines]

 

[KateB]

Ken, you are wonderful!!! This works perfectly - apart from one little
problem. If the NHS number isn't in the second table it doesn't run the
report that generates the letter. I assume I need something added in so that
if it doesn't find the number it skips down to the report generation. I've
tried various things but can't solve it, so would really really appreciate it
if you could help one last time! Here's the code (in case its useful to
someone else - a colleague is already looking to utilise it!) and so that you
can hopefully put me right this last time.

Many many thanks,
Kate

Private Sub PPIletter_AfterUpdate()

Dim ctrl As Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

Set ctrl = Me.ActiveControl

'compares NHSnumber in PPI review table with NHS number on parent form
strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confrmation
' to continue
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)
If Not IsNull(varDate) Then
strMessage = "Last review on " & _
Format(varDate, "dd mmmm yyyy") & _
vbNewLine & "Do you wish to continue?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
'code to insert row into tblPPIreview and generate letter goes
here

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[AutoID] = " & Me.[AutoID]
DoCmd.OpenReport "rpt_PPI_letter", acViewPreview, , strWhere
End If
Else

' if user answers No set check box back to False
ctrl = False
End If
End If
End If

End Sub

 

[KenSheridan via AccessMonster.com]

Kate:

I think you need to shuffle the code around so that it checks for an existing
row in tbl_PPIReview independently of inserting a new row into that table and
opening the report. See if this fits the bill:

Private Sub PPIletter_AfterUpdate()

Dim ctrl As Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

Set ctrl = Me.ActiveControl

'compares NHSnumber in PPI review table with NHS number on parent form
strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confirmation
' to continue
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)
If Not IsNull(varDate) Then
strMessage = "Last review on " & _
Format(varDate, "dd mmmm yyyy") & _
vbNewLine & "Do you wish to continue?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbNo Then
' if user answers No set check box back to False
ctrl = False
' and exit procedure
Exit Sub
End If
End If

‘ otherwise continue
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If IsNull(Me.[AutoID]) Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
'code to insert row into tblPPIreview goes here
strWhere = "[AutoID] = " & Me.[AutoID]
DoCmd.OpenReport "rpt_PPI_letter", acViewPreview, , strWhere
End If
End If

End Sub

Notice that I’ve changed the test for Me.NewRecord to one for IsNull(Me.
[AutoID]). You might have just inserted the data for a new record. A Null
AutoID, however, would mean that a record hasn’t been created.

Ken Sheridan
Stafford, England

Ken, you are wonderful!!! This works perfectly - apart from one little
problem. If the NHS number isn't in the second table it doesn't run the
report that generates the letter. I assume I need something added in so that
if it doesn't find the number it skips down to the report generation. I've
tried various things but can't solve it, so would really really appreciate it
if you could help one last time! Here's the code (in case its useful to
someone else - a colleague is already looking to utilise it!) and so that you
can hopefully put me right this last time.

Many many thanks,
Kate

Private Sub PPIletter_AfterUpdate()

Dim ctrl As Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

Set ctrl = Me.ActiveControl

'compares NHSnumber in PPI review table with NHS number on parent form
strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confrmation
' to continue
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)
If Not IsNull(varDate) Then
strMessage = "Last review on " & _
Format(varDate, "dd mmmm yyyy") & _
vbNewLine & "Do you wish to continue?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
'code to insert row into tblPPIreview and generate letter goes
here

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[AutoID] = " & Me.[AutoID]
DoCmd.OpenReport "rpt_PPI_letter", acViewPreview, , strWhere
End If
Else

' if user answers No set check box back to False
ctrl = False
End If
End If
End If

End Sub

 

[KateB]

Ken, you are a star! Thank you so much for all your patience and help, it is
working perfectly and I am eternally grateful.

Best regards,
Kate

Kate:

I think you need to shuffle the code around so that it checks for an existing
row in tbl_PPIReview independently of inserting a new row into that table and
opening the report. See if this fits the bill:

Private Sub PPIletter_AfterUpdate()

Dim ctrl As Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

Set ctrl = Me.ActiveControl

'compares NHSnumber in PPI review table with NHS number on parent form
strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confirmation
' to continue
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)
If Not IsNull(varDate) Then
strMessage = "Last review on " & _
Format(varDate, "dd mmmm yyyy") & _
vbNewLine & "Do you wish to continue?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbNo Then
' if user answers No set check box back to False
ctrl = False
' and exit procedure
Exit Sub
End If
End If

‘ otherwise continue
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If IsNull(Me.[AutoID]) Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
'code to insert row into tblPPIreview goes here
strWhere = "[AutoID] = " & Me.[AutoID]
DoCmd.OpenReport "rpt_PPI_letter", acViewPreview, , strWhere
End If
End If

End Sub

Notice that I’ve changed the test for Me.NewRecord to one for IsNull(Me.
[AutoID]). You might have just inserted the data for a new record. A Null
AutoID, however, would mean that a record hasn’t been created.

Ken Sheridan
Stafford, England

Ken, you are wonderful!!! This works perfectly - apart from one little
problem. If the NHS number isn't in the second table it doesn't run the
report that generates the letter. I assume I need something added in so that
if it doesn't find the number it skips down to the report generation. I've
tried various things but can't solve it, so would really really appreciate it
if you could help one last time! Here's the code (in case its useful to
someone else - a colleague is already looking to utilise it!) and so that you
can hopefully put me right this last time.

Many many thanks,
Kate

Private Sub PPIletter_AfterUpdate()

Dim ctrl As Control
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

Set ctrl = Me.ActiveControl

'compares NHSnumber in PPI review table with NHS number on parent form
strCriteria = "[NHSnumber] = " & Me.Parent.NHSnumber

' first make sure control has been checked, not unchecked
If ctrl = True Then
' look up latest date for current patient and if exists
' show date in message box and get user confrmation
' to continue
varDate = DMax("[ReviewDate]", "[tbl_PPIReview]", strCriteria)
If Not IsNull(varDate) Then
strMessage = "Last review on " & _
Format(varDate, "dd mmmm yyyy") & _
vbNewLine & "Do you wish to continue?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Confirm") = vbYes Then
'code to insert row into tblPPIreview and generate letter goes
here

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[AutoID] = " & Me.[AutoID]
DoCmd.OpenReport "rpt_PPI_letter", acViewPreview, , strWhere
End If
Else

' if user answers No set check box back to False
ctrl = False
End If
End If
End If

End Sub

--



.

 

[KenSheridan via AccessMonster.com]

It’s a small recompense for all the excellent service I’ve had from the NHS
over the years. Keep working on the musical seaweed treatment – it could be
a world beater!

Ken Sheridan
Stafford, England

Ken, you are a star! Thank you so much for all your patience and help, it is
working perfectly and I am eternally grateful.

Best regards,
Kate

Kate:

[quoted text clipped - 112 lines]