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

Runtime Error 2105

P: 30
I'm new at adding VBA to Access 2007 and could really use some help.
I have two forms used to gather data to go into a datasheet subform. The following code on the second of the two forms populates the subform:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.  
  3.     If ([txt_Act_Qty] > [txt_In_Stock]) Then
  4.     MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  5.     [txt_Act_Qty].SetFocus
  6.     Exit Sub
  7.     Else
  8.     End If
  9.  
  10.     Forms![frm_New_Sale].[sfrm_Sales_Details]![InvOrderNum] = [txtInvOrderNO]
  11.     Forms![frm_New_Sale].[sfrm_Sales_Details]![InvDetailID] = [txtDetailID]
  12.     Forms![frm_New_Sale].[sfrm_Sales_Details]![Item] = [txtItem]
  13.     Forms![frm_New_Sale].[sfrm_Sales_Details]![ItemCost] = [txtItemCost]
  14.     Forms![frm_New_Sale].[sfrm_Sales_Details]![ItemQty] = [txt_Act_Qty]
  15.     Forms![frm_New_Sale].[sfrm_Sales_Details]![SellingPrice] = [txt_Act_Price]
  16.     DoCmd.RunCommand acCmdSaveRecord
  17.     DoCmd.GoToRecord , , acNewRecord
  18.     DoCmd.Close
  19.  
  20. End Sub
After populating the row I want to move to the next record to allow the user to insert another item. However, when Access tries to go to the next record I get "Runtime Error 2105 You can't go to the specified record".

Any advice would be great.

Thanks in advance.
Sep 10 '08 #1
Share this Question
Share on Google+
11 Replies


Megalog
Expert 100+
P: 378
If I'm reading this right, then it's trying to save/advance the records of the main form, rather than telling the subform to save/advance. If the main forms are unbound to any tables, then you'll get that error.

Try specifying the subform name in the docmd lines, and see if that helps.
Sep 10 '08 #2

ADezii
Expert 5K+
P: 8,619
You may want to make your Base Code a little more organized and efficient:
Expand|Select|Wrap|Line Numbers
  1. If ([txt_Act_Qty] > [txt_In_Stock]) Then
  2.   MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  3.   [txt_Act_Qty].SetFocus
  4.     Exit Sub
  5. Else
  6.   With Forms![frm_New_Sale].[sfrm_Sales_Details]
  7.     ![InvOrderNum] = [txtInvOrderNO]
  8.     ![InvDetailID] = [txtDetailID]
  9.     ![Item] = [txtItem]
  10.     ![ItemCost] = [txtItemCost]
  11.     ![ItemQty] = [txt_Act_Qty]
  12.     ![SellingPrice] = [txt_Act_Price]
  13.   End With
  14.     '...   continue processing
  15. End If
  16.  
Sep 10 '08 #3

P: 30
Megalog and ADezii, thank you both for your replies. Adezii, your advice worked great and the code is a lot cleaner. Megalog, my main form and subform are bound to tables and the subform is joined to the mainform on the Invoice Number. I've tried to set focus to the subform first but I get an error stating the method isn't supported. How can I make sure the DoCmd lines run on the subform?

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSaveRecord
  2.     DoCmd.GoToRecord , , acNewRecord  
Sep 11 '08 #4

ADezii
Expert 5K+
P: 8,619
Try:
  1. Set Focus to the Sub-Form Control
  2. Save Record in Sub-Form
  3. Save the Record with SendKeys
  4. Close the Form
Expand|Select|Wrap|Line Numbers
  1. Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  2. DoCmd.RunCommand acCmdSaveRecord
  3. SendKeys "{^}+"
  4. DoCmd.Close
Sep 11 '08 #5

P: 30
Wow! It worked!
However, the record saves to the table and disappears from view. I need the record to remain in the subform until all of the items are added to the invoice. Maybe I should use an unbound form and use code to save the data? Any ideas?

Here's a recap of the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.  
  3.     If ([txt_Act_Qty] > [txt_In_Stock]) Then
  4.         MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  5.         [txt_Act_Qty].SetFocus
  6.     Exit Sub
  7.  
  8.     Else
  9.         With Forms![frm_New_Sale].[sfrm_Sales_Details]
  10.         ![InvOrderNum] = [txtInvOrderNO]
  11.         ![InvDetailID] = [txtDetailID]
  12.         ![Item] = [txtItem]
  13.         ![ItemCost] = [txtItemCost]
  14.         ![ItemQty] = [txt_Act_Qty]
  15.         ![SellingPrice] = [txt_Act_Price]
  16.     End With
  17.  
  18.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  19.     DoCmd.RunCommand acCmdSaveRecord
  20.     SendKeys "{^}+"
  21.     DoCmd.Close
  22.  
  23.     End If
  24. End Sub
Sep 11 '08 #6

ADezii
Expert 5K+
P: 8,619
Wow! It worked!
However, the record saves to the table and disappears from view. I need the record to remain in the subform until all of the items are added to the invoice. Maybe I should use an unbound form and use code to save the data? Any ideas?

Here's a recap of the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.  
  3.     If ([txt_Act_Qty] > [txt_In_Stock]) Then
  4.         MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  5.         [txt_Act_Qty].SetFocus
  6.     Exit Sub
  7.  
  8.     Else
  9.         With Forms![frm_New_Sale].[sfrm_Sales_Details]
  10.         ![InvOrderNum] = [txtInvOrderNO]
  11.         ![InvDetailID] = [txtDetailID]
  12.         ![Item] = [txtItem]
  13.         ![ItemCost] = [txtItemCost]
  14.         ![ItemQty] = [txt_Act_Qty]
  15.         ![SellingPrice] = [txt_Act_Price]
  16.     End With
  17.  
  18.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  19.     DoCmd.RunCommand acCmdSaveRecord
  20.     SendKeys "{^}+"
  21.     DoCmd.Close
  22.  
  23.     End If
  24. End Sub
Try a Requery on the Sub-Form

P.S. - Are you closing the Form in Line #21?
Sep 11 '08 #7

P: 30
I have a sales main form that contains details about the overall sale (Invoice Number, customerID etc...). The main form has a bound subform that holds information about each item added to the sale. The user clicks a button that opens a query form to search Inventory Orders for the items thay want to add. The user double clicks the record they want to add and it transfers all of the data from the selected row to a middle form that forces the user to enter a quantity and price. The user clicks on the OK button that triggers the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.  
  3.     If ([txt_Act_Qty] > [txt_In_Stock]) Then
  4.         MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  5.         [txt_Act_Qty].SetFocus
  6.     Exit Sub
  7.  
  8.     Else
  9.         With Forms![frm_New_Sale].[sfrm_Sales_Details]
  10.         ![InvOrderNum] = [txtInvOrderNO]
  11.         ![InvDetailID] = [txtDetailID]
  12.         ![Item] = [txtItem]
  13.         ![ItemCost] = [txtItemCost]
  14.         ![ItemQty] = [txt_Act_Qty]
  15.         ![SellingPrice] = [txt_Act_Price]
  16.     End With
  17.  
  18.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  19.     DoCmd.RunCommand acCmdSaveRecord
  20.     SendKeys "(TAB)"
  21.     SendKeys "(TAB)"
  22.     SendKeys "(TAB)"
  23.     SendKeys "(TAB)"
  24.     SendKeys "(TAB)"
  25.     SendKeys "(TAB)"
  26.     SendKeys "(TAB)"
  27.     SendKeys "+{ENTER}"
  28.     'SendKeys "{^}+"
  29.     'DoCmd.GoToRecord , , acLast
  30.     DoCmd.Close
  31.  
  32.     End If
  33. End Sub
I had the subform configured for data entry. After changing data entry back to No, the process is saving the row to the table and the items remain on the subform. However, sometime after it saves the row, I get a "Ding" like there was an error and it doesn't advance to the next row.

There are no required fields except the primary key and all of the fields are filled in.

Any Ideas
Sep 12 '08 #8

ADezii
Expert 5K+
P: 8,619
I have a sales main form that contains details about the overall sale (Invoice Number, customerID etc...). The main form has a bound subform that holds information about each item added to the sale. The user clicks a button that opens a query form to search Inventory Orders for the items thay want to add. The user double clicks the record they want to add and it transfers all of the data from the selected row to a middle form that forces the user to enter a quantity and price. The user clicks on the OK button that triggers the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2.  
  3.     If ([txt_Act_Qty] > [txt_In_Stock]) Then
  4.         MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  5.         [txt_Act_Qty].SetFocus
  6.     Exit Sub
  7.  
  8.     Else
  9.         With Forms![frm_New_Sale].[sfrm_Sales_Details]
  10.         ![InvOrderNum] = [txtInvOrderNO]
  11.         ![InvDetailID] = [txtDetailID]
  12.         ![Item] = [txtItem]
  13.         ![ItemCost] = [txtItemCost]
  14.         ![ItemQty] = [txt_Act_Qty]
  15.         ![SellingPrice] = [txt_Act_Price]
  16.     End With
  17.  
  18.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  19.     DoCmd.RunCommand acCmdSaveRecord
  20.     SendKeys "(TAB)"
  21.     SendKeys "(TAB)"
  22.     SendKeys "(TAB)"
  23.     SendKeys "(TAB)"
  24.     SendKeys "(TAB)"
  25.     SendKeys "(TAB)"
  26.     SendKeys "(TAB)"
  27.     SendKeys "+{ENTER}"
  28.     'SendKeys "{^}+"
  29.     'DoCmd.GoToRecord , , acLast
  30.     DoCmd.Close
  31.  
  32.     End If
  33. End Sub
I had the subform configured for data entry. After changing data entry back to No, the process is saving the row to the table and the items remain on the subform. However, sometime after it saves the row, I get a "Ding" like there was an error and it doesn't advance to the next row.

There are no required fields except the primary key and all of the fields are filled in.

Any Ideas
None off the top of my head. I am going on Vacation shortly and will be back on the 18th. If you like, assuming this problem has not been resolved, you can send me the Database at that time and I would be happy to have a look at it.
Sep 12 '08 #9

ADezii
Expert 5K+
P: 8,619
Try:
Expand|Select|Wrap|Line Numbers
  1. Private Sub OK_Click()
  2. If ([txt_Act_Qty] > [txt_In_Stock]) Then
  3. ..MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  4.           ..[txt_Act_Qty].SetFocus
  5.     ..Exit Sub
  6.  
  7. Else
  8.   ..With Forms![frm_New_Sale].[sfrm_Sales_Details]
  9.         ....![InvOrderNum] = [txtInvOrderNO]
  10.         ....![InvDetailID] = [txtDetailID]
  11.         ....![Item] = [txtItem]
  12.         ....![ItemCost] = [txtItemCost]
  13.         ....![ItemQty] = [txt_Act_Qty]
  14.         ....![SellingPrice] = [txt_Act_Price]
  15.   ..End With
  16.     ..Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  17.     ..DoCmd.RunCommand acCmdSaveRecord
  18.     ..DoCmd.GoToRecord , , acNewRec
  19.     ..SendKeys "{TAB 2}"
  20.     ..DoCmd.Close acForm, "frm_Qty_Price_Input", acSaveYes
  21. End If
  22. End Sub
Sep 24 '08 #10

P: 30
The subform still wouldn't advance to a new record. I would get an error Ding sound each time it ran so I figured it was erroring out saving the record. Then I remembered that Im enforcing referential integrity on the relationship. Since I don't know how to step through my code I tried saving the parent record first and it worked. I added the first two lines below:

Expand|Select|Wrap|Line Numbers
  1. Forms![frm_New_Sale].SetFocus
  2.     DoCmd.RunCommand acCmdSaveRecord
  3.  
  4.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  5.     DoCmd.RunCommand acCmdSaveRecord
  6.     DoCmd.GoToRecord , , acNewRec
Thanks for all of your help.
Sep 24 '08 #11

ADezii
Expert 5K+
P: 8,619
The subform still wouldn't advance to a new record. I would get an error Ding sound each time it ran so I figured it was erroring out saving the record. Then I remembered that Im enforcing referential integrity on the relationship. Since I don't know how to step through my code I tried saving the parent record first and it worked. I added the first two lines below:

Expand|Select|Wrap|Line Numbers
  1. Forms![frm_New_Sale].SetFocus
  2.     DoCmd.RunCommand acCmdSaveRecord
  3.  
  4.     Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
  5.     DoCmd.RunCommand acCmdSaveRecord
  6.     DoCmd.GoToRecord , , acNewRec
Thanks for all of your help.
Anytime, that's what we are all here for.
Sep 24 '08 #12

Post your reply

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