can't open new record on subform

[olivero]

Hi group,

I've got a 3 level hierarchy of forms, as such:

--Main
----frmSubOrders1
------frmSubOrderDeta ils1

Order Details is a subform of Orders and Orders is a subform of Main.

I also have an "Orders" table and an "Order Details" table.

I have a button on Main that opens "Orders" as the .SourceObject of frmSubOrders1. This works.

I have an event on a combo box that opens "Order Details" as the .SourceObject of frmSubOrderDeta ils1 in a similar fashion. This also works.

I'm using the following code to do this, set the focus on the subform, and open a new "Order Details" record using the default acActiveDataObj ect. This woks.

Dim stDocName As String
stDocName = "Order Details"
Me.frmSubOrderD etails1.SourceO bject = stDocName
Me.frmSubOrderD etails1.SetFocu s
DoCmd.GoToRecor d , , acNewRec

BUT... when I try to use the following syntax, it doesn't work:

DoCmd.GoToRecor d acDataForm, stDocName, acNewRec

I get the following error:

The object "Order Details" isn't open.

I can't figure out why this is happening...

Any advice?

Oliver

[puppydogbuddy]

Hi group,

I've got a 3 level hierarchy of forms, as such:

--Main
----frmSubOrders1
------frmSubOrderDeta ils1

Order Details is a subform of Orders and Orders is a subform of Main.

I also have an "Orders" table and an "Order Details" table.

I have a button on Main that opens "Orders" as the .SourceObject of frmSubOrders1. This works.

I have an event on a combo box that opens "Order Details" as the .SourceObject of frmSubOrderDeta ils1 in a similar fashion. This also works.

I'm using the following code to do this, set the focus on the subform, and open a new "Order Details" record using the default acActiveDataObj ect. This woks.

Dim stDocName As String
stDocName = "Order Details"
Me.frmSubOrderD etails1.SourceO bject = stDocName
Me.frmSubOrderD etails1.SetFocu s
DoCmd.GoToRecor d , , acNewRec

BUT... when I try to use the following syntax, it doesn't work:

DoCmd.GoToRecor d acDataForm, stDocName, acNewRec

I get the following error:

The object "Order Details" isn't open.

I can't figure out why this is happening...

Any advice?

Oliver

Olivero,
The syntax rules for referencing between forms and subforms are different when you don't already have focus on the form/subform that you are trying to manipulate. See the following link.

http://www.mvps.org/access/forms/frm0031.htm

If you follow the guide, the general syntax to go to a new record on sub2 (the OrderDetails) when you are executing the code from the main form is as follows:

Me!Subform1.For m!Subform2.Form !ControlName.Se tFocus
DoCmd.GoToRecor d , , acNewRec

Substituting your object names into the general syntax, your syntax should be:

Me!frmSubOrders 1.Form!frmSubOr derDetails1.For m.SetFocus
DoCmd.GoToRecor d , , acNewRec

[olivero]

Olivero,
The syntax rules for referencing between forms and subforms are different when you don't already have focus on the form/subform that you are trying to manipulate. See the following link.

http://www.mvps.org/access/forms/frm0031.htm

If you follow the guide, the general syntax to go to a new record on sub2 (the OrderDetails) when you are executing the code from the main form is as follows:

Me!Subform1.For m!Subform2.Form !ControlName.Se tFocus
DoCmd.GoToRecor d , , acNewRec

Substituting your object names into the general syntax, your syntax should be:

Me!frmSubOrders 1.Form!frmSubOr derDetails1.For m.SetFocus
DoCmd.GoToRecor d , , acNewRec

Puppydogbuddy,

Thanks for the info and the quick reply. I'll read through the link and give it a shot.

Happy New Year!

Oliver

[olivero]

Puppydogbuddy,

Thanks for the info and the quick reply. I'll read through the link and give it a shot.

Happy New Year!

Oliver

So that works and I'm able to go to a new record in my Sub2 form by using a button on the Sub1 form, but when then Sub2 form record advances to a new record, so does the Sub1 Form record... I need the Sub1 form record to stay where it is and I want the Sub2 form record to advance to a new record. This is because there may be several Order Detail records per order.

Any advice?

[puppydogbuddy]

So that works and I'm able to go to a new record in my Sub2 form by using a button on the Sub1 form, but when then Sub2 form record advances to a new record, so does the Sub1 Form record... I need the Sub1 form record to stay where it is and I want the Sub2 form record to advance to a new record. This is because there may be several Order Detail records per order.

Any advice?

The syntax I gave you was assuming the button was on the main form. if the button is on sub1, try this syntax and see if it helps:

Me!frmSubOrderDetails1.Form.Set Focus
DoCmd.GoToRecord , , acNewRec

[olivero]

The syntax I gave you was assuming the button was on the main form. if the button is on sub1, try this syntax and see if it helps:

Me!frmSubOrderDetails1.Form.Set Focus
DoCmd.GoToRecord , , acNewRec

The button is on Sub1, but I figured out the difference from the link you sent, so I ended up with the same syntax as above. But I'm now sending Sub1 to a new record as well as Sub2, and I don't want that to happen, I only want Sub1 to go to a new record.

If I do this:

Me!frmSubOrderD etails1.SetFocu s

it works, but the Sub1 form also gets reset to a new record.


If I do this:

Me!frmSubOrderD etails1.Form.Se tFocus

I get this error:

"There is an invalid method in an expression"

If I do this:

Me!frmSubOrderD etails1.Form.Se t Focus

I get this:

"Applicatio n-defined or object-defined error"

Thanks for the help.

Oliver

[puppydogbuddy]

The button is on Sub1, but I figured out the difference from the link you sent, so I ended up with the same syntax as above. But I'm now sending Sub1 to a new record as well as Sub2, and I don't want that to happen, I only want Sub1 to go to a new record.

If I do this:

Me!frmSubOrderD etails1.SetFocu s

it works, but the Sub1 form also gets reset to a new record.


If I do this:

Me!frmSubOrderD etails1.Form.Se tFocus

I get this error:

"There is an invalid method in an expression"

If I do this:

Me!frmSubOrderD etails1.Form.Se t Focus

I get this:

"Applicatio n-defined or object-defined error"

Thanks for the help.

Oliver

In logical terms, I believe you need to set focus on the master form and then issue the goto new record from the master...by that I mean your button should be on the order form and create a new order detail from there. if this is the way you have it working and it creates a new order each time you request a new order detail, then I would suspect the relationships were not set up correctly.

First look at your table relationships, then look at how you have the master/child links set between sub1 and sub2...it should correspond to the one to many relationship between order and order details....you can go to the Northwind sample database that comes with Access.....they have an order and order details form and subform. If the relationship is defined correctly, it should not give you a new master record when you go to a new child record.

[olivero]

In logical terms, I believe you need to set focus on the master form and then issue the goto new record from the master...by that I mean your button should be on the order form and create a new order detail from there. if this is the way you have it working and it creates a new order each time you request a new order detail, then I would suspect the relationships were not set up correctly.

First look at your table relationships, then look at how you have the master/child links set between sub1 and sub2...it should correspond to the one to many relationship between order and order details....you can go to the Northwind sample database that comes with Access.....they have an order and order details form and subform. If the relationship is defined correctly, it should not give you a new master record when you go to a new child record.

The relationsips are as such:

Orders (one) --> to <--(many) Order Details

Link Child Fields and Link Master Fields on frmSubOrderDeta ils1 = OrderID and OrderID.

Link Child Fields and Link Master Fields on frmSubOrders1 = nothing and nothing.


frmSubOrders1 is an unbound subform that is used to present different forms at different times. frmSubOrderDeta ils1 is bound.

I have the button on the master form. It creates both a new record for Orders AND a new record for Order Details, not just for one of them.

This "looks" right to me, but maybe I've missed something...

Oliver

[puppydogbuddy]

The relationsips are as such:

Orders (one) --> to <--(many) Order Details

Link Child Fields and Link Master Fields on frmSubOrderDeta ils1 = OrderID and OrderID.

Link Child Fields and Link Master Fields on frmSubOrders1 = nothing and nothing.


frmSubOrders1 is an unbound subform that is used to present different forms at different times. frmSubOrderDeta ils1 is bound.

I have the button on the master form. It creates both a new record for Orders AND a new record for Order Details, not just for one of them.

This "looks" right to me, but maybe I've missed something...

Oliver

Oliver,
I may have misunderstood you, but I thought you wanted the button on sub1, which is why you changed the first code I gave you that was for a button on the main form. If you are satisfied with the button on the main form, then you are set. Just so you know, you can dynamically bind the master/child links for frmSubOrders1 at run time using VBA code, if you wanted to.

[olivero]

Oliver,
I may have misunderstood you, but I thought you wanted the button on sub1, which is why you changed the first code I gave you that was for a button on the main form. If you are satisfied with the button on the main form, then you are set. Just so you know, you can dynamically bind the master/child links for frmSubOrders1 at run time using VBA code, if you wanted to.

Actually, I may have misunderstood you! My button is on Sub1 which I believe is the master form for Sub2. It is not on Main, which is at the top of the hierarchy.

I'm still having the problem with the Orders recrod being reset every time the Order Details record is reset.... I'm close, but not close enough!

Oliver

[puppydogbuddy]

Actually, I may have misunderstood you! My button is on Sub1 which I believe is the master form for Sub2. It is not on Main, which is at the top of the hierarchy.

I'm still having the problem with the Orders recrod being reset every time the Order Details record is reset.... I'm close, but not close enough!

Oliver

OK, let's try setting the master/child links dynamically in your button code as shown below. Put the link field names in between the quotes. Also, I believe your button should be on your orders form, not the orders detail. Focus, however, needs to be set on the detailssub before creating a new record:

Me!frmSubOrders1.LinkChildFields = "         "
Me!frmSubOrders1.LinkMasterFields = "         "
Me!frmSubOrderDetails1.Set Focus
DoCmd.GoToRecord , , acNewRec

[olivero]

OK, let's try setting the master/child links dynamically in your button code as shown below. Put the link field names in between the quotes. Also, I believe your button should be on your orders form, not the orders detail. Focus, however, needs to be set on the detailssub before creating a new record:

Me!frmSubOrders1.LinkChildFields = "         "
Me!frmSubOrders1.LinkMasterFields = "         "
Me!frmSubOrderDetails1.Set Focus
DoCmd.GoToRecord , , acNewRec

ok, so the button is on Sub1 which is Orders, and I added the above links to the button code, but it's the same result.

?! :)

any more ideas?

[puppydogbuddy]

ok, so the button is on Sub1 which is Orders, and I added the above links to the button code, but it's the same result.

?! :)

any more ideas?

what did you put for Master/Child links? The master link should reference the current value of the control on your form.....someth ing like:

Forms!frmMain!f rmSubOrders1.Fo rm!OrderNo

[olivero]

what did you put for Master/Child links? The master link should reference the current value of the control on your form.....someth ing like:

Forms!frmMain!f rmSubOrders1.Fo rm!OrderNo

Both links refer to the OrderID field that exists in each table.

[puppydogbuddy]

Both links refer to the OrderID field that exists in each table.

Oliver,
The reason I am tryng get you to point your master link to the form is that if the one to many relationship between orders and orderDetails is being enforced via your form, the button click on the order form should not generate a new order because the value of OrderID on form will be the current value as long as the order form is dirty....so give it a try.

Also, did you look at the northwind Db to see how the master/child links were set up between order and order details?