Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

571 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