Add New Record - "You can't go to the specified record" Error

Posted on 2010-01-12
Medium Priority
Last Modified: 2013-12-26
I saw many entries for this question but couldn't find one that fit my situation.  

I have a form that uses 1 table as its recordset (no query).  Form properties are set to allow additions.  When I use the Add New Button, I get the error.  The only thing I can find is that  subroutines called "Form_Clear"  and "ClearItems"  run before the GoToRecord command initiates.  Maybe the recordset for the main form loses focus to the subform recordset?  Just a thought.  I don't know how to change focus if that's the problem.  Here's the code for both:

 Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
    DoCmd.GoToRecord , , acNewRec
    Exit Sub
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
 End Sub
Private Sub Form_Clear()
Me!UnitID = Null
Me!unitName = Null
Me!Addr1 = Null
Me!Addr2 = Null
Me!City = Null
Me!Zip = Null
Me!UnitPhone = Null
Me!PrcGroupID = Null
Me!Operator = Null
Me!OpPhone = Null
Me!Notes = Null
Me!isClosed = Null
Me!State = Null
Me!Unit_Type = Null
Me!OperatorUnit = Null
Me!Market = Null
Me.chkFSU = False
Me.chkMALL = False
DoCmd.SetWarnings False
DoCmd.SetWarnings True
End Sub
Public Sub ClearItems()
Dim strDelete As String
strDelete = "Delete * from tblRollOuttoAdd"
DoCmd.SetWarnings False
DoCmd.RunSQL strDelete
DoCmd.SetWarnings True
End Sub
Question by:sarahellis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 77

Expert Comment

ID: 26295112
Why are you using the Form_Clear.  You would only do something like that if the form is unbound (no controlsource)
For a bound form just moving to the new record is all you need.

Author Comment

ID: 26295333
OK - I don't need the Form_Clear since the bound fields clear themselves and I can set the unbound search controls to null.  But I have to have the ClearItem routine run in case the record I was looking at previously had items (thus populating the subform).

So, was that the problem?  Is there a way to put the focus back on the report's main recordset?
LVL 77

Accepted Solution

peter57r earned 2000 total points
ID: 26295428
If you 'clear' a bound control you are changing the value in the table- I take it you are clear (!!!)(:-)  on that point.

Clearing unbound controls is fine.

The message you are getting suggests you are starting to create a new record and then trying to move to a new record before completing the first one.  You must complete the first new record in some way or other. Either save it or undo it.

Author Comment

ID: 26295509
Ug.  OK, this is when I'm using the Form_Clear.  Also, I noticed that once trying to search for another Unit (main form) after running this sub, I get an error regarding putting nulls in primary key fields.  How can I change this as well as the Add New Items code?

Private Sub btnSaveNewItems_Click()
If IsNull(Me!UnitID) Then
    MsgBox ("Please add a Unit in the top portion of form before proceeding.")
    Exit Sub
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * from tblRollOut " & _
                "where tblRollOut.Unitid = " & Me!UnitID
    DoCmd.RunSQL "INSERT INTO tblRollOut " & _
     "Select unitid, item, qty from tblRollOutToAdd"
    DoCmd.SetWarnings True
End If

Dim response As String
response = MsgBox("Do you want to make changes to another Unit?", vbYesNo)

If response = vbYes Then
End If

End Sub

Author Closing Comment

ID: 31676202
I added a Save button and removed the subroutine Clear_Form, seperately clearing the search controls and subform.  That did the trick.  Thank you for your help!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question