Form Not closing using docmd

[Paul Cooke]

Hi guys i am tottally lost with this one, so would really appriciate some help please

sorry if this is posted in the wrong area but not 100% sure what is causing my problem!

I have a navigation form with a few command buttons one of which opens another form called "FindPatientTreatment". the only code behind this is

DoCmd.OpenForm "FindPatientTreatment", , , acFormAdd
    DoCmd.Close acForm, "Navigation"

Simple and works fine!

When the form opens there are no events associated with the 'form'

The form has 2 main controls and textbox 'txtFindPatient' and a listbox 'lstPatients'

there is a query behind the OnChange event of txtFindPatient...

Private Sub txtFindPatient_Change()
'Searches findpatient text box and filters list below
  
On Error GoTo Err_txtFindPatient_Change
Dim strSource As String

    strSource = "SELECT PatientDetails.PatientID, PatientDetails.PatientFirstName,PatientDetails.PatientSurname, PatientDetails.PatientDOB, PatientDetails.HomeAddressPostCode,PatientDetails.PatientOccupationID, PatientOccupations.PatientOccupation " & _
    " FROM PatientOccupations RIGHT JOIN PatientDetails ON PatientOccupations.PatientOccupationID = PatientDetails.PatientOccupationID " & _
    "Where PatientDetails.PatientFirstName Like '*" & Me.txtFindPatient.Text & "*' " _
    & "Or PatientDetails.PatientSurname Like '*" & Me.txtFindPatient.Text & "*' " _
    & "Or PatientDetails.HomeAddressPostCode Like '*" & Me.txtFindPatient.Text & "*' " _
    & "Or PatientOccupations.PatientOccupation Like '*" & Me.txtFindPatient.Text & "*' " & _
    "ORDER BY PatientDetails.PatientFirstName, PatientDetails.PatientSurname, PatientDetails.PatientDOB, PatientDetails.HomeAddressPostCode "

    Me.lstPatients.RowSource = strSource

Exit_txtFindPatient_Change:     Exit Sub
Err_txtFindPatient_Change: MsgBox Err.Number & " " & Err.Description

Resume Exit_txtFindPatient_Change

End Sub

and the only realcode behind the list box that may be causing my problem is

Private Sub lstPatients_GotFocus()
'Msgbox to inform user no record has been found

    If Me.lstPatients.ListCount = 0 Then
    Beep
    MsgResponse = MsgBox("No record has been found for a patient with that name." _
    & vbCrLf & vbCrLf & "Is your spelling correct?", vbQuestion + vbYesNo, "No Record Found")
    
    If MsgResponse = vbNo Then
    MsgBox "Please try again.", vbInformation, "Retry Entry"
    Me.txtFindPatient.SetFocus
    Me.txtFindPatient.Text = ""
    
    Else
    
    MsgBox "Please enter a new record for this patient.", vbInformation, "New Record"
    DoCmd.OpenForm "PatientDetails", , , , acFormAdd, , "AddFormTreatment"
   
    


    End If
End If

End Sub

So the issue I have is no matter what I do I cannot get the form to close after the above event runs.

I either get form is misspelled or missing or this action can't be carried out while processing a form.... I have tried using docmd... close open etc ect but nothing seems to work?

The only thing I can think of that may becausing it is the query behind the text box?? as all the other code seems pretty straight forward to me ?

Can anyone suggest anything I can do to resolve this please

Many thanks

 

[boblarson]

Where are you trying to use the close?

 

[Paul Cooke]

Hi Bob thanks for the reply - I was trying to close it on an afterupdate of a control on another form but also tried with a vb msg response ect ect..

Sorry though, I should of posted back but I have gone another way around this now today by adding a cmd button which works fine for this form - not as 'tidy' as I wanted it to be but maybe another case of me over complicating what i need !

Thanks for the reply again though

Paul