By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

can't open new record on subform

P: 32
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
Dec 31 '07 #1
Share this Question
Share on Google+
23 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
Dec 31 '07 #2

P: 32
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
Dec 31 '07 #3

P: 32
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?
Jan 1 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Me!frmSubOrderDetails1.Form.Set Focus
  2. DoCmd.GoToRecord , , acNewRec
Jan 1 '08 #5

P: 32
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:
Expand|Select|Wrap|Line Numbers
  1. Me!frmSubOrderDetails1.Form.Set Focus
  2. 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
Jan 1 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 2 '08 #7

P: 32
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
Jan 2 '08 #8

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 2 '08 #9

P: 32
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
Jan 2 '08 #10

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Me!frmSubOrders1.LinkChildFields = "         "
  2. Me!frmSubOrders1.LinkMasterFields = "         "
  3. Me!frmSubOrderDetails1.Set Focus
  4. DoCmd.GoToRecord , , acNewRec
Jan 2 '08 #11

P: 32
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:
Expand|Select|Wrap|Line Numbers
  1. Me!frmSubOrders1.LinkChildFields = "         "
  2. Me!frmSubOrders1.LinkMasterFields = "         "
  3. Me!frmSubOrderDetails1.Set Focus
  4. 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?
Jan 3 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 3 '08 #13

P: 32
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.
Jan 3 '08 #14

puppydogbuddy
Expert 100+
P: 1,923
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?
Jan 3 '08 #15

P: 32
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?
Jan 3 '08 #16

P: 32
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveOrderItemAddNew_Click()
  2.  
  3. On Error GoTo Err_btnSaveOrderItemAddNew_Click
  4.  
  5.     Dim stDocName As String
  6.  
  7.     stDocName = "Order Details"
  8.  
  9.     If Me.Dirty Then
  10.         Me.Dirty = False
  11.     End If
  12.  
  13.     Me.frmSubOrderDetails1.Visible = True
  14.     Me!frmSubOrderDetails1.SourceObject = stDocName
  15.     'Me!frmSubOrderDetails1.LinkChildFields = "OrderID"
  16.     'Me!frmSubOrderDetails1.LinkMasterFields = "OrderID"
  17.     Me!frmSubOrderDetails1.SetFocus
  18.  
  19.     DoCmd.GoToRecord , , acNewRec
  20.     'DoCmd.GoToRecord acDataForm, stDocName, acNewRec
  21.  
  22. Exit_btnSaveOrderItemAddNew_Click:
  23.     Exit Sub
  24.  
  25. Err_btnSaveOrderItemAddNew_Click:
  26.     MsgBox Err.Description
  27.     Resume Exit_btnSaveOrderItemAddNew_Click
  28.  
  29. 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...
Jan 3 '08 #17

P: 32
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveOrderItemAddNew_Click()
  2.  
  3. On Error GoTo Err_btnSaveOrderItemAddNew_Click
  4.  
  5.     Dim stDocName As String
  6.  
  7.     stDocName = "Order Details"
  8.  
  9.     If Me.Dirty Then
  10.         Me.Dirty = False
  11.     End If
  12.  
  13.     Me.frmSubOrderDetails1.Visible = True
  14.     Me!frmSubOrderDetails1.SourceObject = stDocName
  15.     'Me!frmSubOrderDetails1.LinkChildFields = "OrderID"
  16.     'Me!frmSubOrderDetails1.LinkMasterFields = "OrderID"
  17.     Me!frmSubOrderDetails1.SetFocus
  18.  
  19.     DoCmd.GoToRecord , , acNewRec
  20.     'DoCmd.GoToRecord acDataForm, stDocName, acNewRec
  21.  
  22. Exit_btnSaveOrderItemAddNew_Click:
  23.     Exit Sub
  24.  
  25. Err_btnSaveOrderItemAddNew_Click:
  26.     MsgBox Err.Description
  27.     Resume Exit_btnSaveOrderItemAddNew_Click
  28.  
  29. 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!
Jan 3 '08 #18

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 3 '08 #19

P: 32
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveOrderItemAddNew_Click()
  2.  
  3. On Error GoTo Err_btnSaveOrderItemAddNew_Click
  4.  
  5.     Dim stDocName As String
  6.  
  7.     stDocName = "Order Details"
  8.  
  9.     If Me.Dirty Then
  10.         Me.Dirty = False
  11.     End If
  12.  
  13.     Me.frmSubOrderDetails1.Visible = True
  14.     Me!frmSubOrderDetails1.SourceObject = stDocName
  15.     Me!frmSubOrderDetails1.SetFocus
  16.  
  17.     DoCmd.GoToRecord , , acNewRec
  18.  
  19. Exit_btnSaveOrderItemAddNew_Click:
  20.     Exit Sub
  21.  
  22. Err_btnSaveOrderItemAddNew_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_btnSaveOrderItemAddNew_Click
  25.  
  26. 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
Jan 4 '08 #20

P: 32
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
Jan 4 '08 #21

puppydogbuddy
Expert 100+
P: 1,923
ok, i've got it working with this code on the button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveOrderItemAddNew_Click()
  2.  
  3. On Error GoTo Err_btnSaveOrderItemAddNew_Click
  4.  
  5.     Dim stDocName As String
  6.  
  7.     stDocName = "Order Details"
  8.  
  9.     If Me.Dirty Then
  10.         Me.Dirty = False
  11.     End If
  12.  
  13.     Me.frmSubOrderDetails1.Visible = True
  14.     Me!frmSubOrderDetails1.SourceObject = stDocName
  15.     Me!frmSubOrderDetails1.SetFocus
  16.  
  17.     DoCmd.GoToRecord , , acNewRec
  18.  
  19. Exit_btnSaveOrderItemAddNew_Click:
  20.     Exit Sub
  21.  
  22. Err_btnSaveOrderItemAddNew_Click:
  23.     MsgBox Err.Description
  24.     Resume Exit_btnSaveOrderItemAddNew_Click
  25.  
  26. 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
Jan 4 '08 #22

P: 32
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
Jan 4 '08 #23

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 5 '08 #24

Post your reply

Sign in to post your reply or Sign up for a free account.