Need Help Opening Form From DropDown Box

[ramrags]

I have a small Doctor's office DataBase written on access 97 and I have two problems that are keeping me from finishing. The first is that I have form's opening from a form that I call "open" this is built from an unbound dropdown box that gets it's data from a query of patients names and uses a module on the after-update that uses the SSN as the criteria for the filter. This works fine but I run into a problem with forms with sub-forms. These forms should open with the sub-form opening ready for data-entry. And they will open correctly if I open them without the use of the "open" form but if I use the "open" form if the patient has any data in the underlying table the form opens with the sub-form filled with that data. The Sub for the "open" form is.
Private Sub SelectPatient_AfterUpdate()
Dim Criteria As String
If Not IsNull(Me![SelectPatient]) Then
Criteria = "[SocialSecurityNumber] = '" & Me![SelectPatient].Column(1) & "'"
DoCmd.OpenForm "Chart"
Forms!Chart.Filter = Criteria
Forms!Chart.FilterOn = True

End If

End Sub
Any help is greatly appreciated. My other problem is I have a form called "Reservation" this is a form with a sub-form this is used when a patient need a procedure done at the hospital this form opens with the patient demographics filled in, and the staff fills in the hospital's info. This works well the problem comes in with a command button on the "Reservation" form for "PreTestOrders" this is a form that opens from the "Reservation" form I would like this form to open using the information from the Reservation they are working on. As it is now the "PreTestOrders" opens if there are any past reservations for the patient these come up first. I'm sure there is a where condition needed but not sure what to do with it, in my tables I have fields for reservation and orders numbers hoping the answer is in these somewhere but I can't get it. Once again any help is appreciated and I really need help THANKS.

 

[grtammi]

Hey ramrags ...

You'll need to have the sub form point to a new record, such as follows:

...
DoCmd.OpenForm "Chart"
Forms!Chart.Filter = Criteria
Forms!Chart.FilterOn = True
Forms!Chart!<subform CONTROL name>.Form.SetFocus '**
Docmd.GoToRecord , , acNewRec
...

** in the area labelled <subform CONTROL name> you need to
place the name of the subform control, and not the name
of the subform itself (in Design view, right-click on
the subform, select Properties, and view the Name
property to see the name of the control.)

For example, if your subform control name is MySubform,
but the source object is frmMyform, you would reference
it as follows: Forms!Chart!MySubform.Form.SetFocus

There's the answer to your first question - I cannot gather
enough information from your post to answer your second question, or I would try.

HTH

Greg

If you don't understand, just nod and smile ...

 

[ramrags]

Greg:
I want to thank you for your help on my first problem, if your willing to take a stab at my second one I'll try to explain it a little better. I have a form with a sub-form called frmReservation; this is used for staff to make hospital reservation for a patient procedure. This opens from the dropdown box you helped with now these opens fine. Where I'm having trouble is I have a command button on the form that opens another form called frmOrders this form is for ordering pre-op orders. The way I want this to work when you click the command button I want the orders form to open for the reservation that your working on. The way it is now if the patient has had reservations in the past you have to navigate to the reservation you're on. I hope this makes sense and I appreciate any help you can give. Thanks again

 

[grtammi]

In your command button that you use to open the form, try
doing the following:

1) Use the Docmd.Save command before anything else to save
the active data.

2) Use a form filter something like you used here:

Forms!Chart.Filter = Criteria
Forms!Chart.FilterOn = True

HTH

Greg
If you don't understand, just nod and smile ...