Form giving error

[MadCat]

Hi all

I have a problem with a form i am building.
From an initial form (TitleForm) i want the user to be taken to a screen that lets them add a patients details if they don't already exist on the system. When the user adds a new patient the "patient details form" opens on a new record and allows the user to add details. This part works fine. The other part of the "TitleForm" is to be able to select an existing patient from a drop down list and once selected, a button appears to allow the user to select the patient. Its at this point the problems begin. i've written some code to that should determine if the drop down box has had a value selected. If so then the code takes the patient identifier of the selected patient and then should place it in the filter of the "patient details form" and display all the details for that patient. Only it doesn't . I get an error message saying "You cancelled the previous operation"

Here's the code for onOpen event for the patient details form:

Private Sub Form_Open(Cancel As Integer)
Me.Refresh
'Check to see if a patient has been chosen from the select patient form
If Not IsNull(Forms!TitleForm.SelectPatient) Then

Dim num As String
Dim cbo As ComboBox
Set cbo = Forms!TitleForm.SelectPatient
num = cbo.Column(3)
Dim patCriteria As String
patCriteria = "PatUnitNo = '" & num & "'"
Me.Filter = patCriteria
Me.FilterOn = True
Else
DoCmd.GoToRecord , , acNewRec
End If

End Sub


Any help on what i may be doing wrong would be most appreciated

Thanks

 

[Fizzio]

It would be easier to use the Where argument of the Docmd.OpenForm action which will automatically filter the patient details for you, rather than the code you are currently using on the Patient Details Form.

Look for the docmd.openform statement behind the Click event of the button and make Sure that it reads something like

docmd.openform "PatientDetailsForm",,,"[PatUnNo] = '" & me.SelectPatient.Column(3) & "'"

If you use the wizard to build the button for you, one of the options is to open a form and display specific information.

hth

 

[MadCat]

Hiya

That didn't seem to work.
I have this piece of code now in the onClick event of the button on the TitleForm

Private Sub SelectPatientBtn_Click()

DoCmd.OpenForm "test", , , "[PatUnitNo] = '" & Me.SelectPatient.Column(3) & "'"
DoCmd.Maximize

End Sub


I have removed the onOpen event code and i am now getting another error message.

It now says:
The OpenForm action was cancelled

any ideas as to why this is the case?

I should mention that "test" is the patient details form in this case.


Thanks for the quick response, i appreciate it.

 

[Fizzio]

What other code do you have in the form "test".

Also the linking field would normally be the ID field of the patient, rather than the PatientNumber. what are the other columns of the SelectPatient Combo?

 

[MadCat]

I never wrote the database in the first place, i'm just trying to adapt a Title form that does what i initially said. From what i can see, there is no more code in the events section of the form "test", apart for a re-query of the "TitleForm" combobox onClose. The combobox on the "TitleForm" shows the firstname, surname, date of birth and the patient unit number. There is far more details in the table but are not needed to be seen by the user. The patient unit number is unique. All of the patient details are stored in the one table.

Let me know if there's anything else you need to know

Cheers

 

[Fizzio]

There must be a docmd.close statement somewhere either on the "test" form or "TitleForm" which is prematurely trying to cancel the form. Check the code (in the vba editor) and get back with anything that looks suspicious.

 

[MadCat]

It seems that there are lots of code fragments relating to button clicks like this which contain doCmd.close commands:

Command1037_Click()

I'm going to try and locate them all and see what they all do but i don't think that they relate to anything on the form. Would that make a difference if it was code form a button which had been deleted?

I'll get back to you once i've found any suspicious code.

Thanks

 

[Fizzio]

It looks like there are many remnants from added and deleted controls. Access deletes the control but 'forgets' to delete the code associated with it!
If there is code with no associated conntrol, delete it - it is taking up space in your Db.

Also check the code in the "test" form for the same, especially Form_open, _Load, _Activate etc

 

[MadCat]

Hi again

Ok, i've checked both "TitleForm" and "Test", all redundant code has been removed and there is no event code in "test" other that the onClose event. The error "The openForm action was cancelled" still appears. When i choose to debug the error seems to come from the doCmd.OpenForm line in this code:

Private Sub SelectPatientBtn_Click()
Dim whereVar
DoCmd.OpenForm "test", , , "[PatUnitNo] = '" & Me.SelectPatient.Column(3) & "'"
DoCmd.Maximize
SelectPatientBtn.Visible = False
SelectPatient = Null
End Sub

I really am puzzled as to why the error is still coming up.

Sorry to be a pain, but is there anything else it may be

Thanks

 

[Fizzio]

Got me puzzled as well

What are the properties of the test form - is allow edits, additions etc set to yes?

I'll have a look myself (for curiosity) if you want to zip it and post it.

 

[MadCat]

The allow options are all set to yes. I have zipped the db but it is still 994K and will not allow me to post it. i have cut back on all the unnecessary tables and forms in the db but cannot get it any smaller.

Thanks

 

[MadCat]

Don't know if this will help but i have played around with the code and tried to open the record in the "test" form using the PatForeName field. This is working. I now believe it is something to do with the PatUnitNo. I'm not sure what would be causing thsi not to work though.

 

[Fizzio]

If patUnitNo is a number (rather than text), have you tried

DoCmd.OpenForm "test", , , "[PatUnitNo] = " & Me.SelectPatient.Column(3)

And is there data in column 3? (remember combos are zero indexed)

 

[MadCat]

I have just tried that prior to you posting the last message. That sorts it, thats what the problem was. Its all working now.
As you suggested, taking out the quotes around the reference to the column allows it to be read as it should be.

Thanks a lot for your help Fizzio, i appreciate it.

 

[Fizzio]

Wish I'd suggested that earlier! Never mind - got there in the end