Runtime Error 2105

[scottbouley]

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:

Private Sub OK_Click()
 
    If ([txt_Act_Qty] > [txt_In_Stock]) Then
    MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
    [txt_Act_Qty].SetFocus
    Exit Sub
    Else
    End If
 
    Forms![frm_New_Sale].[sfrm_Sales_Details]![InvOrderNum] = [txtInvOrderNO]
    Forms![frm_New_Sale].[sfrm_Sales_Details]![InvDetailID] = [txtDetailID]
    Forms![frm_New_Sale].[sfrm_Sales_Details]![Item] = [txtItem]
    Forms![frm_New_Sale].[sfrm_Sales_Details]![ItemCost] = [txtItemCost]
    Forms![frm_New_Sale].[sfrm_Sales_Details]![ItemQty] = [txt_Act_Qty]
    Forms![frm_New_Sale].[sfrm_Sales_Details]![SellingPrice] = [txt_Act_Price]
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRecord
    DoCmd.Close
 
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.

[Megalog]

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.

[ADezii]

You may want to make your Base Code a little more organized and efficient:

If ([txt_Act_Qty] > [txt_In_Stock]) Then
  MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
  [txt_Act_Qty].SetFocus
    Exit Sub
Else
  With Forms![frm_New_Sale].[sfrm_Sales_Details]
    ![InvOrderNum] = [txtInvOrderNO]
    ![InvDetailID] = [txtDetailID]
    ![Item] = [txtItem]
    ![ItemCost] = [txtItemCost]
    ![ItemQty] = [txt_Act_Qty]
    ![SellingPrice] = [txt_Act_Price]
  End With
    '...   continue processing
End If
 

[scottbouley]

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?

DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRecord  

[ADezii]

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

Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
DoCmd.RunCommand acCmdSaveRecord
SendKeys "{^}+"
DoCmd.Close

[scottbouley]

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:

Private Sub OK_Click()
 
    If ([txt_Act_Qty] > [txt_In_Stock]) Then
        MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
        [txt_Act_Qty].SetFocus
    Exit Sub
 
    Else
        With Forms![frm_New_Sale].[sfrm_Sales_Details]
        ![InvOrderNum] = [txtInvOrderNO]
        ![InvDetailID] = [txtDetailID]
        ![Item] = [txtItem]
        ![ItemCost] = [txtItemCost]
        ![ItemQty] = [txt_Act_Qty]
        ![SellingPrice] = [txt_Act_Price]
    End With
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    SendKeys "{^}+"
    DoCmd.Close
 
    End If
End Sub

[ADezii]

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:

Private Sub OK_Click()
 
    If ([txt_Act_Qty] > [txt_In_Stock]) Then
        MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
        [txt_Act_Qty].SetFocus
    Exit Sub
 
    Else
        With Forms![frm_New_Sale].[sfrm_Sales_Details]
        ![InvOrderNum] = [txtInvOrderNO]
        ![InvDetailID] = [txtDetailID]
        ![Item] = [txtItem]
        ![ItemCost] = [txtItemCost]
        ![ItemQty] = [txt_Act_Qty]
        ![SellingPrice] = [txt_Act_Price]
    End With
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    SendKeys "{^}+"
    DoCmd.Close
 
    End If
End Sub

Try a Requery on the Sub-Form

P.S. - Are you closing the Form in Line #21?

[scottbouley]

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:

Private Sub OK_Click()
 
    If ([txt_Act_Qty] > [txt_In_Stock]) Then
        MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
        [txt_Act_Qty].SetFocus
    Exit Sub
 
    Else
        With Forms![frm_New_Sale].[sfrm_Sales_Details]
        ![InvOrderNum] = [txtInvOrderNO]
        ![InvDetailID] = [txtDetailID]
        ![Item] = [txtItem]
        ![ItemCost] = [txtItemCost]
        ![ItemQty] = [txt_Act_Qty]
        ![SellingPrice] = [txt_Act_Price]
    End With
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "+{ENTER}"
    'SendKeys "{^}+"
    'DoCmd.GoToRecord , , acLast
    DoCmd.Close
 
    End If
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

[ADezii]

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:

Private Sub OK_Click()
 
    If ([txt_Act_Qty] > [txt_In_Stock]) Then
        MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
        [txt_Act_Qty].SetFocus
    Exit Sub
 
    Else
        With Forms![frm_New_Sale].[sfrm_Sales_Details]
        ![InvOrderNum] = [txtInvOrderNO]
        ![InvDetailID] = [txtDetailID]
        ![Item] = [txtItem]
        ![ItemCost] = [txtItemCost]
        ![ItemQty] = [txt_Act_Qty]
        ![SellingPrice] = [txt_Act_Price]
    End With
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "(TAB)"
    SendKeys "+{ENTER}"
    'SendKeys "{^}+"
    'DoCmd.GoToRecord , , acLast
    DoCmd.Close
 
    End If
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.

[ADezii]

Try:

Private Sub OK_Click()
If ([txt_Act_Qty] > [txt_In_Stock]) Then
..MsgBox "Sales Quantity cannot exceed Quantity In Stock", vbOKOnly
          ..[txt_Act_Qty].SetFocus
    ..Exit Sub
 
Else
  ..With Forms![frm_New_Sale].[sfrm_Sales_Details]
        ....![InvOrderNum] = [txtInvOrderNO]
        ....![InvDetailID] = [txtDetailID]
        ....![Item] = [txtItem]
        ....![ItemCost] = [txtItemCost]
        ....![ItemQty] = [txt_Act_Qty]
        ....![SellingPrice] = [txt_Act_Price]
  ..End With
    ..Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    ..DoCmd.RunCommand acCmdSaveRecord
    ..DoCmd.GoToRecord , , acNewRec
    ..SendKeys "{TAB 2}"
    ..DoCmd.Close acForm, "frm_Qty_Price_Input", acSaveYes
End If
End Sub

[scottbouley]

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:

Forms![frm_New_Sale].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec

Thanks for all of your help.

[ADezii]

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:

Forms![frm_New_Sale].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
 
    Forms![frm_New_Sale].[sfrm_Sales_Details].SetFocus
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec

Thanks for all of your help.

Anytime, that's what we are all here for.