[X]My Assistant

[LilAnnCC1]

I went back and studied it some more. The NewID was staying so I added another variable lngID and changed the code to this:

CODE

With CurrentDb.QueryDefs("qPassR")
        .SQL = "EXEC spSalesCopy " & OldID & "," & strDate & "," & intTeam & ",1," & PO & "," & Store & "," & ShipTax & "," & ShipAddress
        With .OpenRecordset
          If Not (.BOF And .EOF) Then
            NewID = .Fields("ID")
            lngID = NewID  -------------------------------------this is what I added
          End If
          .Close
        End With
    End With
      
'Open to newly created
    With Forms("f_Sale")
      .Requery
      With .RecordsetClone
        .FindFirst "ID = " & lngID-------------------getting the lngID
        If Not .NoMatch Then
          Set Forms("f_Sale").Bookmark = .Bookmark
        End If
      End With
    End With
    DoCmd.Close acForm, "sf_SaleCopy"

It works wonderfully. Again, thank you so much!

[cheekybuddha]

I'm not sure why that is necessary, since you are just passing the value of NewID into another variable ...

...but if it works, then stick with it!!!



d

[LilAnnCC1]

Tested again. I can't get it to open using a recordset.


So I'm using:

'Open to newly created
DoCmd.Close acForm, "f_Sale"
DoCmd.OpenForm "f_Sale", , , , , , NewID (so I don't need the extra variable). It must have something to do with the way I set up the sales form. There is only 1 record on the sales form at all times, so that's maybe why it will only open to a new record.
DoCmd.Close acForm, "sf_SaleCopy"

[cheekybuddha]

Yeah, that makes more sense, since the form is using a filtered recordset.

One other way to try (but untested) is:

CODE

' ...
    With Forms("f_Sale")
      .Requery
      .Filter = "ID = " & NewID
      .FilterOn = True
    End With
' ...

but in the end it may just be easier to close and reopen the form.