Error 2001 Canceled operation

G

Guest

Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
 
G

Guest

Hello John
Set rs = Me.Recordset.Clone
Click to expand...
Try Me.Recordsetclone


jhmwine said:
Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
 
G

Guest

Still getting the same error message, I'm afraid.

It must be something to do with the filtering; if one chooses no filter everything is fine.
--
John Martin


jhmwine said:
Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
 
G

Guest

I am trying to re-construct your forms & work out what you are doing. One point that does hit me in the eye as I do this is the line:
Forms![Patient input]![Patient Name].SetFocus
I believe you are referring to a field here; you cannot set focus to a field but to an object such as a textbox "PatientName"
Additionally, although this could be ok, Combo43 seems to be mentioned in only one Case line

You also seem to be duplicating a Frame (FilterOptions & ReportFilterOptions) on the two forms - I cannot see why.

What are you trying to do? I think you have some patients split between n doctors (in your example 3) you want to select either one or all doctors and pirnt/preview a selected report for the selected doctor(s). If this is the case you do not need the intermediate form. I would be inclined to put these selectors on the form and to filter the data on the form and, optionally, have a print button. The selectors would apply the filter to the data on the form and the filter can be passed to the report selected.
To work the way you seem to be, you do not need the second selector in the intermediate form, you only need to select the report. When you open the intermediate form pass the filter from the primary form via the OpenArgs to the intermediate form which then opens the correct report with the required filter, eg
sFilter = "[Case open To] = 2"
DoCmd.OpenForm "SelectReport", , , , , , sFilter
OR
DoCmd.OpenForm "SelectReport"
when you want all records

In the intermediate form check the OpenArgs property of the form. If there are no OpenArgs then all records are required.
IMPORTANT:
Any fields passed as part of a WHERE clause to the report MUST be present in the source of the report
If you want more info let me know. I will set up a temp email address so you can send me the actual forms so I can get a better idea. (Sorry! I will not post my email address - last time I did that I ended up getting 20+ spams every Exchange email scan)
Also important (and you have done this)
Pass the filter (eg [case open to] = 3) via the WHERE argument of the OpenReport rather than the Filter argument
DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
HTH
Terry

jhmwine said:
Still getting the same error message, I'm afraid.

It must be something to do with the filtering; if one chooses no filter everything is fine.
--
John Martin


jhmwine said:
Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
Click to expand...
 
G

Guest

I sent a rather extensive reply but it got blown away during the post. I will try again tomorrow as it is rather late now & it took a fair while to prepare. Besides I want to watch the tennis final
Sorry
Terry

jhmwine said:
Still getting the same error message, I'm afraid.

It must be something to do with the filtering; if one chooses no filter everything is fine.
--
John Martin


jhmwine said:
Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
Click to expand...
 
G

Guest

A good final, I thought.

Can you let me have that email address please? I'll send you the forms (and the tables?). I do need additional help. I still do not understand what is happening.
--
John Martin


Terry said:
I sent a rather extensive reply but it got blown away during the post. I will try again tomorrow as it is rather late now & it took a fair while to prepare. Besides I want to watch the tennis final
Sorry
Terry

jhmwine said:
Still getting the same error message, I'm afraid.

It must be something to do with the filtering; if one chooses no filter everything is fine.
--
John Martin


jhmwine said:
Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hello John
Yes.
You can email me the details to (e-mail address removed)
I will keep this address open only for one week. Send me as much as you can but do NOT send any confidential information. Any info you do send will be treated as confidential anyway and will be deleted when we are finished. I will post all replies here.
Cheers
Terry

jhmwine said:
A good final, I thought.

Can you let me have that email address please? I'll send you the forms (and the tables?). I do need additional help. I still do not understand what is happening.
--
John Martin


Terry said:
I sent a rather extensive reply but it got blown away during the post. I will try again tomorrow as it is rather late now & it took a fair while to prepare. Besides I want to watch the tennis final
Sorry
Terry

jhmwine said:
Still getting the same error message, I'm afraid.

It must be something to do with the filtering; if one chooses no filter everything is fine.
--
John Martin


:

Help! I cannot figure out this problem.

I am trying to use VBA for the first time to create a database to look after patient info. I created a form "Patient Input" which allows the input and viewing of info. I placed on the form an option group allowing the filtering of the info to the patients of one of 3 members of staff. This worked OK.

I then created anoth form "Report Filter Form" which allows the user to select a particular report and filter the info to one of the members of staff. This worked as well (eventually).

When I next tried to filter the info on the "Patient Input" form, I received a message, "Runtime error 2001 You canceled the previous operation." This happens even if I have not called the second form.

In case it is helpful, I have set out the relevant code below.

I would appreciate any help. This is driving me crazy.

Option Compare Database
Option Explicit
Dim pstrReportName As String
Dim pstrFilter As String

Behind the "Report Filter Form":-

Private Sub ExitReportFilterButton_Click()
On Error GoTo Err_ExitReportFilterButton_Click


DoCmd.Close acForm, "Report Filter Form"

Exit_ExitReportFilterButton_Click:
Exit Sub

Err_ExitReportFilterButton_Click:
MsgBox Err.Description
Resume Exit_ExitReportFilterButton_Click

End Sub



Private Sub ProceedReportButton_Click()
On Error GoTo Err_ProceedReportButton_Click


DoCmd.OpenReport pstrReportName, acViewPreview, , pstrFilter
DoCmd.Close acForm, "Report Filter Form"


Exit_ProceedReportButton_Click:
Exit Sub

Err_ProceedReportButton_Click:
MsgBox Err.Description
Resume Exit_ProceedReportButton_Click

End Sub

Private Sub ReportSelecterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportSelecterOption
Case 1
pstrReportName = "Patients (Alphabetical)"
Case 2
pstrReportName = "Patients (By Case Open for)"
Case 3
pstrReportName = "Patients (By case open to & case open for)"
Case 4
pstrReportName = "Patients by registration date"
Case 5
pstrReportName = "Patients (by case open to)"
End Select
End Sub
Private Sub ReportFilterOption_BeforeUpdate(Cancel As Integer)
On Error Resume Next

Select Case ReportFilterOption
Case 2 'KM selected
pstrFilter = "[case open to] = 2"
Case 3 'CW selected
pstrFilter = "[case open to] = 3"
Case 4 'HvL selected
pstrFilter = "[case open to] = 1"
Case 5 'Unallocated selected
pstrFilter = "[case open to] = 4"
Case Else 'no value for intFilter if All selected
pstrFilter = ""
End Select

End Sub
++++++++++++++++++++++++++++++
Behind the "Patient Input" form:

Private Sub FilterOptions_AfterUpdate()
' Apply or remove filter

Select Case FilterOptions
Case 1 'All patients
Me.FilterOn = False
Me.Combo29.Visible = True
Case 2 'Patients of KM
Me.Filter = "[Case Open to] = '2'"
Me.FilterOn = True
Me.Combo29.Visible = False
Me.Combo43.BackColor = vbYellow
Case 3 'Patients of CW
Me.Filter = "[Case Open to] = '3'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 4 'Patients of HvL
Me.Filter = "[Case Open to] = '1'"
Me.FilterOn = True
Me.Combo29.Visible = False
Case 5 'Unallocated
Me.Filter = "[Case Open to] = '4'"
Me.FilterOn = True
Me.Combo29.Visible = False

End Select

End Sub

Private Sub Form_AfterUpdate()
Forms![Patient input]![Patient Name].SetFocus
End Sub


Private Sub Combo29_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo29], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Patient Name].SetFocus
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Set the option group value to match the user's filtering action

If applttype = acShowAllRecords Then
FilterOptions = 1 'Set the All Patients option
ElseIf Filter <> "[Case Open to] = '1'" And Filter <> "[Case Open to] = '2'" _
And Filter <> "[Case Open to] = '3'" And Filter <> "[Case Open to] = '4'" Then
FilterOptions = Null ' Don't set any option value
End If

End Sub

Private Sub Form_Close()

End Sub

Private Sub Form_Current()

Combo29 = ID ' Update the Find Record combo box.

End Sub


Private Sub PatientInputExitButton_Click()
On Error GoTo Err_PatientInputExitButton_Click


DoCmd.Close acForm, "Patient Input"

Exit_PatientInputExitButton_Click:
Exit Sub

Err_PatientInputExitButton_Click:
MsgBox Err.Description
Resume Exit_PatientInputExitButton_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Message Box to Display a count of Records 3
Get the sum of Filtered form 0
Multiple Filter Criteria 4
filter form by listbox selection 1
Access VBA in Access confusion. 1
Runtime error 2001 2
data access page search code 1
Option Button as Filter 3

Top