Compulsory field - if certain conditions are filled

[Johanna Gronlund]

Hi,

I have a database with a query with patient referral details plus some
calculated fields. We need to monitor whether the patients are referred on
fast enough. There are the following fields (in addition to others): date
referral received, date referral passed on, today's date, date when referral
has breached and patient not suitable for surgery.

I have a form that collects and displays all this information. There is a
combo box with reason for breach. I need to make it compulsory for the data
entry clerk to fill in that box if the following criteria are true:
1. Breach date is greater than today's date AND
2. Surgery date is blank AND
3. Patient not suitable for surgery tick box is not ticked

Is it possible to do this in a form?

Many thanks for your help in advance.

 

[Jeff Boyce]

Johanna

Based on your description, it sounds like your table looks a lot like ... a
spreadsheet! Having "repeating fields" (date of this, date of that, date of
another thing) may be how you'd have to approach this if you were limited to
using a spreadsheet, but you won't get the best use of Access'
relationally-oriented features and functions if you feed it 'sheet data.

Since it all starts with data, I'll suggest that you step back and make sure
your table structure is well-normalized before proceeding.

Or have I misinterpreted your description?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Johanna Gronlund]

Jeff,

Thanks for reply. There are many other fields on the table and the structure
of the database utilises the relation abilities of Access (this database
wasn't build by myself). The referrals table just needs to record several
dates as they are vital in the referral process, espcially in auditing the
speed of referrals from primary care to secondary care.

Essentially, what I need to know is that:
Can I set criteria on my form fields so that it's compulsory for the data
entry clerk to choose something in one of the combo boxes?

Many thanks,

Johanna

 

[Klatuu]

Jeff has a good point; however, it is possible to do what you want. For this
sort of situation, you would use the Before Update event of the form to test
for the conditions and take the appropriate actions.

Private Sub frmReferrral_BeforeUpdate(Cancel As Integer)

If Me.txtBreachDate > Date And IsNull(Me.txtSurgeryDate) And Not
Me.chkSurgeryOk Then
If IsNull(Me.cboBreachReason) Then
MsgBox "A Breach Region is Required"
Cancel = True
Me.cboBreachReason.SetFocus
End If
End If

End Sub

 

[Johanna Gronlund]

Thanks very much for your reply. Together with the users of the database, we
have reviewed the requirement for all the dates. It has been agreed that not
all of them are needed any more.

The criteria what I need to set has changed slightly and I am not able to
modify the code provided as I am not very familiar with VB. The combo box
'AppointmentBeyond18Weeks' needs to be filled in when 'Surgery Date' >
'18Weeks'. I keep getting error messages when trying to write it myself.

Many thanks for you help!

Johanna

 

[Klatuu]

The easy solution is to tell the users to modify the business rules to align
with how your application works
But, sometimes they are stubborn and you have to actually change your code.
Is '18Weeks' a value in a control? Can you be a bit more detailed place

 

[Johanna Gronlund]

Yes - it would have been easier if they changed their requirements to suit my
skills

I have a form 'Data_Entry' with details of each referral.

The 'Surgery Date' is the date that the patient will have their surgery
The '18Weeks' is the date 126 days after their referral date. The standard
that the service needs to achieve is an appointment 126 days after the
referral date. So '18Weeks' is a calculated field in the underlying query.

The combobox is called 'AppointmentBeyond18Weeks' and contains reasons for
why appointment was after the 18 week deadline, eg. patient choice,
appointments not available, etc.

I would need to have a warning box for those referrals where 'Surgery Date'

'18Weeks' and no field is selected in 'AppoitnmentBeyond18Weeks' combobox.

Does that make more sense? Thanks soooo much for your help.

Johanna

 

[Klatuu]

Ok, still in the Form Before Update event:
If Me.SurgeryDate > [18Weeks] And IsNull(Me.AppointmentBeyond18Weeks) Then
MsgBox "Appointment is Beyond 18 Weeks"
Me.AppointmentBeyond18Weeks.SetFocus
Cancel = True
End If

 

[Johanna Gronlund]

Hi,

I tried writing the code in the relevant place but nothing happens. This is
what I wrote:

Private Sub frm() 'Data_Entry'_BeforeUpdate(Cancel As Integer)
If Me.Surgery_Date > [18Weeks] And IsNull(Me.AppointmentBeyond18Weeks)
Then
MsgBox "Appointment is Beyond 18 Weeks"
Me.AppointmentBeyond18Weeks.SetFocus
Cancel = True
End If

End Sub

Did I do something wrong? Some of the field names have spaces and I wasn't
sure how to deal with that. Would it be best to change them?

Sorry to be so dim that I can't even follow the explicit instructions!

Johanna
--
Johanna G

Ok, still in the Form Before Update event:
If Me.SurgeryDate > [18Weeks] And IsNull(Me.AppointmentBeyond18Weeks) Then
MsgBox "Appointment is Beyond 18 Weeks"
Me.AppointmentBeyond18Weeks.SetFocus
Cancel = True
End If

I would need to have a warning box for those referrals where 'Surgery Date'

--
Dave Hargis, Microsoft Access MVP

Yes - it would have been easier if they changed their requirements to suit my
skills

I have a form 'Data_Entry' with details of each referral.

The 'Surgery Date' is the date that the patient will have their surgery
The '18Weeks' is the date 126 days after their referral date. The standard
that the service needs to achieve is an appointment 126 days after the
referral date. So '18Weeks' is a calculated field in the underlying query.

The combobox is called 'AppointmentBeyond18Weeks' and contains reasons for
why appointment was after the 18 week deadline, eg. patient choice,
appointments not available, etc.

I would need to have a warning box for those referrals where 'Surgery Date'

Does that make more sense? Thanks soooo much for your help.

Johanna

 

[Klatuu]

This looks wrong:
Private Sub frm() 'Data_Entry'_BeforeUpdate(Cancel As Integer)

Did you write this line yourself?

The best way to do this is to go into the Properties Dialog for the form,
select the Events tab and select Before Update. Select Code Builder. The
VBA editor will open with the correct name already in place.
--
Dave Hargis, Microsoft Access MVP

Hi,

I tried writing the code in the relevant place but nothing happens. This is
what I wrote:

Private Sub frm() 'Data_Entry'_BeforeUpdate(Cancel As Integer)
If Me.Surgery_Date > [18Weeks] And IsNull(Me.AppointmentBeyond18Weeks)
Then
MsgBox "Appointment is Beyond 18 Weeks"
Me.AppointmentBeyond18Weeks.SetFocus
Cancel = True
End If

End Sub

Did I do something wrong? Some of the field names have spaces and I wasn't
sure how to deal with that. Would it be best to change them?

Sorry to be so dim that I can't even follow the explicit instructions!

Johanna
--
Johanna G

Ok, still in the Form Before Update event:
If Me.SurgeryDate > [18Weeks] And IsNull(Me.AppointmentBeyond18Weeks) Then
MsgBox "Appointment is Beyond 18 Weeks"
Me.AppointmentBeyond18Weeks.SetFocus
Cancel = True
End If

I would need to have a warning box for those referrals where 'Surgery Date'
'18Weeks' and no field is selected in 'AppoitnmentBeyond18Weeks' combobox.

--
Dave Hargis, Microsoft Access MVP

Yes - it would have been easier if they changed their requirements to suit my
skills

I have a form 'Data_Entry' with details of each referral.

The 'Surgery Date' is the date that the patient will have their surgery
The '18Weeks' is the date 126 days after their referral date. The standard
that the service needs to achieve is an appointment 126 days after the
referral date. So '18Weeks' is a calculated field in the underlying query.

The combobox is called 'AppointmentBeyond18Weeks' and contains reasons for
why appointment was after the 18 week deadline, eg. patient choice,
appointments not available, etc.

I would need to have a warning box for those referrals where 'Surgery Date'
'18Weeks' and no field is selected in 'AppoitnmentBeyond18Weeks' combobox.

Does that make more sense? Thanks soooo much for your help.

Johanna

--
Johanna G


:

The easy solution is to tell the users to modify the business rules to align
with how your application works
But, sometimes they are stubborn and you have to actually change your code.
Is '18Weeks' a value in a control? Can you be a bit more detailed place
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your reply. Together with the users of the database, we
have reviewed the requirement for all the dates. It has been agreed that not
all of them are needed any more.

The criteria what I need to set has changed slightly and I am not able to
modify the code provided as I am not very familiar with VB. The combo box
'AppointmentBeyond18Weeks' needs to be filled in when 'Surgery Date' >
'18Weeks'. I keep getting error messages when trying to write it myself.

Many thanks for you help!

Johanna

--
Johanna G


:

Jeff has a good point; however, it is possible to do what you want. For this
sort of situation, you would use the Before Update event of the form to test
for the conditions and take the appropriate actions.

Private Sub frmReferrral_BeforeUpdate(Cancel As Integer)

If Me.txtBreachDate > Date And IsNull(Me.txtSurgeryDate) And Not
Me.chkSurgeryOk Then
If IsNull(Me.cboBreachReason) Then
MsgBox "A Breach Region is Required"
Cancel = True
Me.cboBreachReason.SetFocus
End If
End If

End Sub
2. Surgery date is blank AND
3. Patient not suitable for surgery tick box is not ticked

--
Dave Hargis, Microsoft Access MVP


:

Hi,

I have a database with a query with patient referral details plus some
calculated fields. We need to monitor whether the patients are referred on
fast enough. There are the following fields (in addition to others): date
referral received, date referral passed on, today's date, date when referral
has breached and patient not suitable for surgery.

I have a form that collects and displays all this information. There is a
combo box with reason for breach. I need to make it compulsory for the data
entry clerk to fill in that box if the following criteria are true:
1. Breach date is greater than today's date AND
2. Surgery date is blank AND
3. Patient not suitable for surgery tick box is not ticked

Is it possible to do this in a form?

Many thanks for your help in advance.