can't open new record on subform

[olivero]

Hi group,

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

--Main
----frmSubOrders1
------frmSubOrderDetails1

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 frmSubOrderDetails1 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 acActiveDataObject. This woks.

Dim stDocName As String
stDocName = "Order Details"
Me.frmSubOrderDetails1.SourceObject = stDocName
Me.frmSubOrderDetails1.SetFocus
DoCmd.GoToRecord , , acNewRec

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

DoCmd.GoToRecord 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
------frmSubOrderDetails1

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 frmSubOrderDetails1 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 acActiveDataObject. This woks.

Dim stDocName As String
stDocName = "Order Details"
Me.frmSubOrderDetails1.SourceObject = stDocName
Me.frmSubOrderDetails1.SetFocus
DoCmd.GoToRecord , , acNewRec

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

DoCmd.GoToRecord 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.Form!Subform2.Form!ControlName.SetFocu s
DoCmd.GoToRecord , , acNewRec

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

Me!frmSubOrders1.Form!frmSubOrderDetails1.Form.Set Focus
DoCmd.GoToRecord , , 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.Form!Subform2.Form!ControlName.SetFocu s
DoCmd.GoToRecord , , acNewRec

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

Me!frmSubOrders1.Form!frmSubOrderDetails1.Form.Set Focus
DoCmd.GoToRecord , , 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!frmSubOrderDetails1.SetFocus

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


If I do this:

Me!frmSubOrderDetails1.Form.SetFocus

I get this error:

"There is an invalid method in an expression"

If I do this:

Me!frmSubOrderDetails1.Form.Set Focus

I get this:

"Application-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!frmSubOrderDetails1.SetFocus

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


If I do this:

Me!frmSubOrderDetails1.Form.SetFocus

I get this error:

"There is an invalid method in an expression"

If I do this:

Me!frmSubOrderDetails1.Form.Set Focus

I get this:

"Application-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 frmSubOrderDetails1 = 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. frmSubOrderDetails1 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 frmSubOrderDetails1 = 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. frmSubOrderDetails1 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.....something like:

Forms!frmMain!frmSubOrders1.Form!OrderNo

[olivero]

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

Forms!frmMain!frmSubOrders1.Form!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?

[olivero]

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?

I did try it!! i didn't work, but....

It's not dirty... I have code at the beginning that cleans it up before proceeding...

If Me.Dirty Then
Me.Dirty = False
End If

How is this causing it to break?

[olivero]

I did try it!! i didn't work, but....

It's not dirty... I have code at the beginning that cleans it up before proceeding...

If Me.Dirty Then
Me.Dirty = False
End If

How is this causing it to break?

hmmm... ok, so it's "working" now, but it only works when I update a second field that's on there. Here's the code I'm using at the moment:

Private Sub btnSaveOrderItemAddNew_Click()
 
On Error GoTo Err_btnSaveOrderItemAddNew_Click
 
    Dim stDocName As String
 
    stDocName = "Order Details"
 
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    Me.frmSubOrderDetails1.Visible = True
    Me!frmSubOrderDetails1.SourceObject = stDocName
    'Me!frmSubOrderDetails1.LinkChildFields = "OrderID"
    'Me!frmSubOrderDetails1.LinkMasterFields = "OrderID"
    Me!frmSubOrderDetails1.SetFocus
 
    DoCmd.GoToRecord , , acNewRec
    'DoCmd.GoToRecord acDataForm, stDocName, acNewRec
 
Exit_btnSaveOrderItemAddNew_Click:
    Exit Sub
 
Err_btnSaveOrderItemAddNew_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveOrderItemAddNew_Click
 
End Sub

Note that it works with even the commented lines...

I'm going to go through the object events and figure out why this is happening, then I'll get back to you.

Thanks for the help so far, because this wasn't working before, even like this. I'm wondering if some code got corrupted somewhere before...

[olivero]

hmmm... ok, so it's "working" now, but it only works when I update a second field that's on there. Here's the code I'm using at the moment:

Private Sub btnSaveOrderItemAddNew_Click()
 
On Error GoTo Err_btnSaveOrderItemAddNew_Click
 
    Dim stDocName As String
 
    stDocName = "Order Details"
 
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    Me.frmSubOrderDetails1.Visible = True
    Me!frmSubOrderDetails1.SourceObject = stDocName
    'Me!frmSubOrderDetails1.LinkChildFields = "OrderID"
    'Me!frmSubOrderDetails1.LinkMasterFields = "OrderID"
    Me!frmSubOrderDetails1.SetFocus
 
    DoCmd.GoToRecord , , acNewRec
    'DoCmd.GoToRecord acDataForm, stDocName, acNewRec
 
Exit_btnSaveOrderItemAddNew_Click:
    Exit Sub
 
Err_btnSaveOrderItemAddNew_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveOrderItemAddNew_Click
 
End Sub

Note that it works with even the commented lines...

I'm going to go through the object events and figure out why this is happening, then I'll get back to you.

Thanks for the help so far, because this wasn't working before, even like this. I'm wondering if some code got corrupted somewhere before...

OK, so it works consistently, except that on Sub2 (frmSubOrderDetails1) I have a combo box with some code on it. When I make a selection from the combo box, the code runs and the button appears on Sub1 (frmSubOrders). If I click the button without tabbing out of the combo box, both forms reset, but if I tab out of the combo box to the next field, THEN click the button only Sub2 resets (which is what I want).

How come it's doing this? I've tried running the code from severl events on the combo box to no avail. I've tried On Dirty, After Update, and On Change, but they all procude the same effect.

We're almost there!

[puppydogbuddy]

I did try it!! i didn't work, but....

It's not dirty... I have code at the beginning that cleans it up before proceeding...

If Me.Dirty Then
Me.Dirty = False
End If

How is this causing it to break?

the above code saves all changes to the form since the last save. If nothing is pending, then it makes sense that your button currently generates a new order record in addition to the order detail record.

Maybe you can try modifying your button code to check if the orders form is dirty. If it is dirty (see below) , it should generate just a new detail record the way you want; if it isn't dirty, I believe your button code will generate a new order record and a new order detail record. If you don't want this then throw out a message or take some oher action.

Private Sub YourButton_Click()
If Me.Dirty Then 'Me refers to the order form
your button code from above should create only a new orderDetail record
else
messge
End if
End Sub


If this does not work, then I can't help you any further unless you can email me a sanitized (erase sensitive info) copy of your db in access 2000. You can get my email address by downloading my Vcard located with my profile.

[olivero]

the above code saves all changes to the form since the last save. If nothing is pending, then it makes sense that your button currently generates a new order record in addition to the order detail record.

Maybe you can try modifying your button code to check if the orders form is dirty. If it is dirty (see below) , it should generate just a new detail record the way you want; if it isn't dirty, I believe your button code will generate a new order record and a new order detail record. If you don't want this then throw out a message or take some oher action.

Private Sub YourButton_Click()
If Me.Dirty Then 'Me refers to the order form
your button code from above should create only a new orderDetail record
else
messge
End if
End Sub


If this does not work, then I can't help you any further unless you can email me a sanitized (erase sensitive info) copy of your db in access 2000. You can get my email address by downloading my Vcard located with my profile.

ok, i've got it working with this code on the button:

Private Sub btnSaveOrderItemAddNew_Click()
 
On Error GoTo Err_btnSaveOrderItemAddNew_Click
 
    Dim stDocName As String
 
    stDocName = "Order Details"
 
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    Me.frmSubOrderDetails1.Visible = True
    Me!frmSubOrderDetails1.SourceObject = stDocName
    Me!frmSubOrderDetails1.SetFocus
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnSaveOrderItemAddNew_Click:
    Exit Sub
 
Err_btnSaveOrderItemAddNew_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveOrderItemAddNew_Click
 
End Sub

and this extra code on the combo box to tab to the next control:

Me.txtQuantityOrderDetails.SetFocus

Do you know if there is a better way to change the focus to the next TabIndex in line, as opposed to having to explicitely use an object's name (in this case txtQuantityOrderDetails) to set the focus? Something like this is what I need:

Me.NextTabIndex.SetFocus

[olivero]

After getting it to work, I played around with things a little more and it was happening specifically because the focus was being removed from the sub2 Order Details form. The focus was removed for different reasons at different times, which is why I couldn't pin it down, but that's why.

Thanks for all the help, I really appreciate it!

Oliver

[puppydogbuddy]

ok, i've got it working with this code on the button:

Private Sub btnSaveOrderItemAddNew_Click()
 
On Error GoTo Err_btnSaveOrderItemAddNew_Click
 
    Dim stDocName As String
 
    stDocName = "Order Details"
 
    If Me.Dirty Then
        Me.Dirty = False
    End If
 
    Me.frmSubOrderDetails1.Visible = True
    Me!frmSubOrderDetails1.SourceObject = stDocName
    Me!frmSubOrderDetails1.SetFocus
 
    DoCmd.GoToRecord , , acNewRec
 
Exit_btnSaveOrderItemAddNew_Click:
    Exit Sub
 
Err_btnSaveOrderItemAddNew_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveOrderItemAddNew_Click
 
End Sub

and this extra code on the combo box to tab to the next control:

Me.txtQuantityOrderDetails.SetFocus

Do you know if there is a better way to change the focus to the next TabIndex in line, as opposed to having to explicitely use an object's name (in this case txtQuantityOrderDetails) to set the focus? Something like this is what I need:

Me.NextTabIndex.SetFocus

Off the top of my head, I don't know of any way to avoid using the name of a control with the tab index property, you can set the TabIndex property to an integer representing the position of the control within the tab order of the form. Valid settings are 0 for the first tab position, up to the total number of controls minus 1 for the last tab position,but you still have reference the control name. Here is an example:

Me!txtQuantityOrderDetails.TabIndex = 1

[olivero]

Off the top of my head, I don't know of any way to avoid using the name of a control with the tab index property, you can set the TabIndex property to an integer representing the position of the control within the tab order of the form. Valid settings are 0 for the first tab position, up to the total number of controls minus 1 for the last tab position,but you still have reference the control name. Here is an example:

Me!txtQuantityOrderDetails.TabIndex = 1

All my research points to that limitation as well.

I think I'm going to have to loop through the controls on the form, compare to the current tabindex, find the name of the next tabindex, then insert that. it' should be too complicated to do... :)

Thanks again for all the help!

Oliver

[puppydogbuddy]

All my research points to that limitation as well.

I think I'm going to have to loop through the controls on the form, compare to the current tabindex, find the name of the next tabindex, then insert that. it' should be too complicated to do... :)

Thanks again for all the help!

Oliver

You are welcome. Glad you got it resolved! If there is one thing you learn when you work with Access, it's that many errors are usually have more then one cause, with the underlying cause "masked" until the outlying problems are uncovered one by one.

Looping the data set should work and is usually pretty straightforward,

Thanks for documenting the final solution to the set focus problem. It should help others in a similar situation.