Opening this form is making me nuts!!

[ramrags]

I'm having real trouble with this form… I have a form with a sub-form called "NewReservation" the form opens with patient information on the main form. The sub-form opens blank ready for data, this works fine. The problem comes in, with a command button on the main form the button is used to open another form called "OrdersRes"; this form is another form with a sub-form. I want the main part of the "Orders" form to open with data from the both the "Reservation and ReservationSub". I can get the "Orders" form to open with the patient information by using the code.
Dim MyOrders As String
MyOrders = "OrdersRes"
DoCmd.Save acForm, "NewReservation"
DoCmd.OpenForm MyOrders, , , "SocialSecurityNumber= '" & Forms!NewReservation !SocialSecurityNumber & "'"
When I filter with this the "OrdersRes" open with the correct patient the problem is if the patient has multiple Reservations the filter comes up with all the records for the patient. This is still working correctly but I'm trying to filter it down to the reservation I'm working on. I have a field on the "NewReservationSub" named "ReservationNumber" this is an auto number field used to make the record unique, my trouble is trying to pass this value to the Where clause in the code. Please help this is making me crazy, Thank you for any help.

 

[WildHare]

It sounds like your Reservation record is not being saved yet, so you have no "ReservationNumber" to pass along. On your command button that opens the 2nd form, make sure you explicitly save the RESERVATION record. Then you can grab the autonumber key that's being generated and pass it along to the 2nd form.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ramrags]

No thats not the problem, the auto number is generated as soon as any field receives data. And I start my code with DoCmd.Save acForm, "NewReservation" this saves the form. My trouble starts when i try to pass the Criteria for the Order to open from the form i'm on. if you have any idea's please help.

 

[WildHare]

OK, let's see. If you have both the values that you'll need to pass along to the SECOND form, you should be able to build a "where" clause using both of them:

DoCmd.OpenForm MyOrders , , ,
"SocSecNum = ' " & Me!SocSecNum & "' and ResNumber = ' " & me!NewReservationsControl.Form!ResNumber & "'"

Remember that you refer to the CONTROL name that has your subform in it, not necessarily the subforms's actual Name. I always make it a point to NAME my controls with something short and sweet, like "subF", so I don't have to worry about typing a long form name. And don't forget the reference to ".FORM!"

E.g, given a control name of "subf" for the subform, and an field on it called "resNum", the correct way to refer to that field is

MainFormName!Subf.Form!ResNum

And make sure your quoting is correct.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm