Link to home
Start Free TrialLog in
Avatar of sarahellis
sarahellis

asked on

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

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
    Form_Clear
    DoCmd.GoToRecord , , acNewRec
Exit_cmdAdd_Click:
    Exit Sub
Err_cmdAdd_Click:
    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
ClearItems
DoCmd.SetWarnings True
End Sub
________________________________________
Public Sub ClearItems()
Dim strDelete As String
strDelete = "Delete * from tblRollOuttoAdd"
DoCmd.SetWarnings False
DoCmd.RunSQL strDelete
Me.subfRollOuttoAdd.Form.Requery
DoCmd.SetWarnings True
End Sub
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of sarahellis
sarahellis

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sarahellis
sarahellis

ASKER

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
Else
    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
    Form_Clear
Else
    Form_Clear
    DoCmd.Close
End If

End Sub
Avatar of sarahellis
sarahellis

ASKER

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!