UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Output Values In Stored Procedure?, Any Versions    
 
   
LilAnnCC1
post Nov 5 2019, 03:55 PM
Post#1



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


Hello, all. I am using SQL Server 2017 as my back end and Office 365 Access as front end.

I am slowly learning SQL Server, so bear with me.

I have a procedure that gets the next sales number (based on Date and Teams--each team has their own set of sales order numbers and it resets each calendar year).

I call this procedure in Access and it seems to be working quite well.

Here is my Stored Procedure:
CODE
ALTER procedure [dbo].[spSalesGetNumber]
@Date dateTime
,@Team int
,@Revision int


AS
BEGIN
DECLARE
@orderDate nvarchar(2)
,@Prefix nvarchar(3)
,@OrderNo nvarchar(5)
,@RevNo nvarchar(2)
,@Short nvarchar(11)
,@Long nvarchar(11)


        Set @orderDate=Right(Year(@Date),2)
        Set @Prefix=(SELECT concat(@Team,@orderDate))
        Set @Orderno= (SELECT Case When Max([OrderNo]) Is Null then 1 ELSE Max([OrderNo]) + 1 END as NextOrder
                  FROM tSale
                  WHERE OrderPrefix=@Prefix
                  Group By  OrderPrefix)
        Set @RevNo=(SELECT RevNumber FROM tRevision WHERE tRevision.ID=@Revision)
        Set @Short= CONCAT(@OrderNo, '-', @RevNo,'-',@Prefix)
        Set @Long=CONCAT(@Prefix,Right('00000'+CONVERT([nvarchar](4),@OrderNo),5),'-',@RevNo)

        SELECT Convert(int,@OrderNo) as [Number], @Short as [OrderShort], @Long as [OrderLong]        
END

GO


I use a pass-through query to return the values that I need and it updates my new sales order with the correct information.

But now I need to create another procedure that would "Clone" a Sales Order. In other words, instead of the user retyping the entire order, they would give me a new Customer Purchase Order number and a new Shipping location. So I was thinking that I would follow these steps:

In the SQL Management studio,

1. Insert all the fields from the Sales Table WHERE the ID = @SalesID into a Temp Table
2. Update the fields I need to change (Customer Purchase Order number, Shipping Address, and TaxID)
3. Get the next sales order number from the above procedure--Don't know how to do this!!!
4. Insert the newly updated fields from the Temp Table back into the Sales Table
5. Get the new ID for the newly created record
6. Insert the Details from the copied Order into the Sales Detail table using the new ID from the newly created Sales Order.

7. Open the form in access to the newly created sales order.

Is my list correct? Is there a better way to copy a sales order? I do allow the Order Number fields to be null, so should I just copy the Sales Order without worrying about the order number and let Access pull the above procedure before the user makes any changes to it?

Any guidance is really appreciated. Thank you.




--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 5 2019, 04:41 PM
Post#2


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


Hi LilAnnCC1,

One possible way to do this:

First, I would turn your procedure in to a function.

Then, to clone the order, I would just do an Insert ... Select (ie not bother with temp table) using the function to return the new Order No.

Something like:
CODE
IF object_id('fnSalesGetNumber') > 0
  DROP FUNCTION fnSalesGetNumber;
GO

CREATE FUNCTION fnSalesGetNumber (
  @Date               DATETIME,
  @Team               INT,
  @Revision           INT
) RETURNS @tbl TABLE (
  Number              INT NOT NULL,
  OrderShort          NVARCHAR(11),
  OrderLong           NVARCHAR(11)
)
AS
BEGIN

  DECLARE @orderDate  NVARCHAR(2),
          @Prefix     NVARCHAR(3),
          @OrderNo    NVARCHAR(5),
          @RevNo      NVARCHAR(2),
          @Short      NVARCHAR(11),
          @Long       NVARCHAR(11)

  SET @orderDate  = RIGHT(YEAR(@Date), 2)
  SET @Prefix     = (SELECT CONCAT(@Team, @orderDate))
  SET @Orderno    = (
    SELECT
      CASE
        WHEN MAX([OrderNo]) IS NULL THEN 1
        ELSE MAX([OrderNo]) + 1
      END AS NextOrder
    FROM tSale
    WHERE OrderPrefix = @Prefix
    GROUP BY  
      OrderPrefix
  )
  SET @RevNo      = (SELECT RevNumber FROM tRevision WHERE tRevision.ID = @Revision)
  SET @Short      = CONCAT(@OrderNo, '-', @RevNo,'-', @Prefix)
  SET @Long       = CONCAT(@Prefix, RIGHT('00000' + CONVERT([nvarchar](4),@OrderNo),5), '-', @RevNo)

  INSERT @tbl (
    [Number],
    OrderShort,
    OrderLong
  ) VALUES (
    CONVERT(INT, @OrderNo),
    @Short,
    @Long
  )

  RETURN

END


Then just use a query like:
CODE
INSERT INTO tSale (
  -- Field list here
)
  SELECT
    Fld1,
    Fld2,
    12345 AS PONumber, -- put new value in here, you can pass a variable,
    (SELECT [Number] FROM fnSalesGetNumber) AS SalesOrderNo,
    Fld5
-- etc
  FROM tSale
  WHERE SalesID = @SalesID
;


Obviously untested! But to try and give you the idea.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 6 2019, 08:22 AM
Post#3



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


Thank you!

I created the function and I am redoing the VBA to call for the function instead of the stored procedure on the sales order form. It works as long as there are existing orders for 2019.

But in testing it for 2020, it fails to get a number. The function accounts for null, but it doesn't seem to be working.
CODE
SET @Orderno    = (
    SELECT
      CASE
        WHEN MAX([OrderNo]) IS NULL THEN 1
        ELSE MAX([OrderNo]) + 1
      END AS NextOrder
    FROM tSale
    WHERE OrderPrefix = @Prefix
    GROUP BY  
      OrderPrefix

I tried using ISNull(Max([OrderNo])+1,1) but that it worked the same, the function fails because [Number] is null.


How can I rewrite this to return 1 if the Max(OrderNo) is null?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 08:32 AM
Post#4


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


It's curious why that doesn't work if it worked previously in the SP, but perhaps you can hack around it and use:
CODE
-- ...
  INSERT @tbl (
    [Number],
    OrderShort,
    OrderLong
  ) VALUES (
    CONVERT(INT, COALESCE(@OrderNo, 1)),
    @Short,
    @Long
  )


Oops! I edited this - please check again the COALESCE() function

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 6 2019, 09:12 AM
Post#5



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


I just checked the stored procedure and the [Number] is null instead of 1, so it wasn't working in my stored procedure.

Will try your suggestion.

Thank you!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
LilAnnCC1
post Nov 6 2019, 09:25 AM
Post#6



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


Got it, finally. The function needed to be updated everywhere it was called for. It's a shame it won't handle the null in the first place, but this is your final function:

CODE
IF object_id('fnSalesGetNumber') > 0
  DROP FUNCTION fnSalesGetNumber;
GO

Create FUNCTION fnSalesGetNumber (
  @Date               DATETIME,
  @Team               INT,
  @Revision           INT
) RETURNS @tbl TABLE (
  Number              INT NOT NULL,
  OrderShort          NVARCHAR(11),
  OrderLong           NVARCHAR(11)
)
AS
BEGIN

  DECLARE @orderDate  NVARCHAR(2),
          @Prefix     NVARCHAR(3),
          @OrderNo    NVARCHAR(5),
          @RevNo      NVARCHAR(2),
          @Short      NVARCHAR(11),
          @Long       NVARCHAR(11)

  SET @orderDate  = RIGHT(YEAR(@Date), 2)
  SET @Prefix     = (SELECT CONCAT(@Team, @orderDate))
  SET @Orderno    = (
    --SELECT
   --   CASE
   --     WHEN MAX([OrderNo]) IS NULL THEN 1
   --     ELSE MAX([OrderNo]) + 1
         --END AS NextOrder
SELECT Coalesce(MAX([OrderNo])+1,1) AS NextOrder
    
    FROM tSale
    WHERE OrderPrefix = @Prefix
    GROUP BY  
      OrderPrefix
  )
  SET @RevNo      = (SELECT RevNumber FROM tRevision WHERE tRevision.ID = @Revision)
  SET @Short      = CONCAT(Coalesce(@OrderNo,1), '-', @RevNo,'-', @Prefix)
  SET @Long       = CONCAT(@Prefix, RIGHT('00000' + CONVERT([nvarchar](4),Coalesce(@OrderNo,1)),5), '-', @RevNo)

  INSERT @tbl (
    [Number],
    OrderShort,
    OrderLong
  ) VALUES (
    CONVERT(INT, Coalesce(@OrderNo,1)),
    @Short,
    @Long
  )

  RETURN

END


Will now work on this to get it to work for my "copy" of sales order.

Thank you so much for your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 6 2019, 11:26 AM
Post#7


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


thumbup.gif

As you will note, the function returns a table, so you can SELECT from it.

So for your clone process you can create an SP along the lines of:
CODE
IF object_id('CloneSalesOrder') > 0
  DROP PROCEDURE CloneSalesOrder;
GO

CREATE PROCEDURE CloneSalesOrder
  @SalesID                INT
AS
BEGIN

  DECLARE @SalesNo        INT,
          @SalesNoShort   NVARCHAR(11),
          @SalesNoLong    NVARCHAR(11)

-- Other variable declarations

  SET NOCOUNT ON;

-- Populate other variables

  SELECT
    @SalesNo      = [Number],
    @SalesNoShort = OrderShort,
    @SalesNoLong  = OrderLong
  FROM fnSalesGetNumber(GETDATE(), @Team, @Revision);

-- INSERT INTO tSale (
--   FieldList
-- )
--   SELECT
--     bla bla

END


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 7 2019, 07:18 AM
Post#8



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


Thank you so much for your help.

This is the code I finally got to work between the SQL server and Access. It works really well.

CODE
ALTER Procedure [dbo].[spSalesCopy]

@CopyID            INT,
@Date              DATETIME,
@Team              INT,
@Revision          INT,
@PO                NVARCHAR(25),
@Store                INT,
@ShipTax            INT,
@ShipAddress        NVARCHAR(255)
AS
SET NOCOUNT ON;
DECLARE @NewID INT

BEGIN TRANSACTION --(Sale Header)
BEGIN TRY
--Insert copied sales order header into tSales
INSERT INTO tSale(
OrderNo,
OrderShort,
SalesOrder,
TeamID,
SalesRepID,
JobProjectID,
OrderDate,
RTS,
OrderDue,
ProjectID,
ContractID,
CustomerID,
CustomerPO,
TermsID,
OBO_ID,
StoreID,
ShipOptionID,
FOBID,
BillTaxID,
BillAddress,
ShipTaxID,
ShipAddress,
AddressTaxID
)

SELECT
(SELECT NewNumber FROM fnSalesGetNumber(@Date,@Team,@Revision)),
(SELECT ShortNo From fnSalesGetNumber(@Date,@Team,@Revision)),
(SELECT LongNo FROM fnSalesGetNumber(@Date,@Team,@Revision)),
TeamID,
SalesRepID,
JobProjectID,
GetDate(),
DateAdd(day, 10,GetDate()),
DateAdd(day, 45,GetDate()),
ProjectID,
ContractID,
CustomerID,
@PO,
TermsID,
OBO_ID,
@Store,
ShipOptionID,
FOBID,
BillTaxID,
BillAddress,
@ShipTax,
@ShipAddress,
AddressTaxID
FROM tSale
WHERE ID=@CopyID

SET @NewID=SCOPE_IDENTITY()

--SALES ORDER DETAILS
INSERT INTO tSaleDetail(
OrderID,
DueDate,
JobProjectID,
GLID,
OBOID,
StoreID,
AddressLine,
AddressTaxID,
PartID,
Description,
UOMID,
OrderQTY,
OrderPrice,
Line,
NWO
)
SELECT
@NewID,
@Date,
JobProjectID,
GLID,
OBOID,
@Store,
@ShipAddress,
@ShipTax,
PartID,
Description,
UOMID,
OrderQTY,
OrderPrice,
Line,
NWO
FROM tSaleDetail
WHERE OrderID=@CopyID

COMMIT TRANSACTION
SELECT * FROM tSale WHERE ID=@NewID
END TRY

BEGIN CATCH
            DECLARE
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
        ROLLBACK TRANSACTION
    END CATCH

GO


If you see any glaring issues, could you please let me know?

I appreciate all your time and help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 7 2019, 02:16 PM
Post#9


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


Hi LilAnnCC1,

All looks pretty good!

The one thing I would change is this portion:
CODE
-- ...
AddressTaxID
)

SELECT
(SELECT NewNumber FROM fnSalesGetNumber(@Date,@Team,@Revision)),
(SELECT ShortNo From fnSalesGetNumber(@Date,@Team,@Revision)),
(SELECT LongNo FROM fnSalesGetNumber(@Date,@Team,@Revision)),
TeamID,
-- ...


I would declare some variables and populate them from the function in one go like I showed in Post#7.

Then use the variables instead of the 3 SELECT statements.

Like:
CODE
ALTER Procedure [dbo].[spSalesCopy]
  @CopyID                 INT,
  @Date                   DATETIME,
  @Team                   INT,
  @Revision               INT,
  @PO                     NVARCHAR(25),
  @Store                  INT,
  @ShipTax                INT,
  @ShipAddress            NVARCHAR(255)
AS

  SET NOCOUNT ON;

  DECLARE @NewID          INT,
          @SalesNo        INT,
          @SalesNoShort   NVARCHAR(11),
          @SalesNoLong    NVARCHAR(11)

  BEGIN TRANSACTION --(Sale Header)

  SELECT
    @SalesNo      = NewNumber,
    @SalesNoShort = ShortNo,
    @SalesNoLong  = LongNo
  FROM fnSalesGetNumber(@Date,@Team,@Revision);

  BEGIN TRY
  --Insert copied sales order header into tSales
    INSERT INTO tSale(
      OrderNo,
      OrderShort,
      SalesOrder,
      TeamID,
      SalesRepID,
      JobProjectID,
      OrderDate,
      RTS,
      OrderDue,
      ProjectID,
      ContractID,
      CustomerID,
      CustomerPO,
      TermsID,
      OBO_ID,
      StoreID,
      ShipOptionID,
      FOBID,
      BillTaxID,
      BillAddress,
      ShipTaxID,
      ShipAddress,
      AddressTaxID
    )
      SELECT
        @SalesNo,
        @SalesNoShort,
        @SalesNoLong,
        TeamID,
        SalesRepID,
        JobProjectID,
        GetDate(),
        DateAdd(day, 10,GetDate()),
        DateAdd(day, 45,GetDate()),
        ProjectID,
        ContractID,
        CustomerID,
        @PO,
        TermsID,
        OBO_ID,
        @Store,
        ShipOptionID,
        FOBID,
        BillTaxID,
        BillAddress,
        @ShipTax,
        @ShipAddress,
        AddressTaxID
      FROM tSale
      WHERE ID = @CopyID

    SET @NewID=SCOPE_IDENTITY()

    --SALES ORDER DETAILS
    INSERT INTO tSaleDetail(
      OrderID,
      DueDate,
      JobProjectID,
      GLID,
      OBOID,
      StoreID,
      AddressLine,
      AddressTaxID,
      PartID,
      Description,
      UOMID,
      OrderQTY,
      OrderPrice,
      Line,
      NWO
    )
      SELECT
        @NewID,
        @Date,
        JobProjectID,
        GLID,
        OBOID,
        @Store,
        @ShipAddress,
        @ShipTax,
        PartID,
        Description,
        UOMID,
        OrderQTY,
        OrderPrice,
        Line,
        NWO
      FROM tSaleDetail
      WHERE OrderID = @CopyID

    COMMIT TRANSACTION

    SELECT * FROM tSale WHERE ID = @NewID

  END TRY

  BEGIN CATCH
    DECLARE
      @ErrorMessage   NVARCHAR(4000),
      @ErrorSeverity  INT,
      @ErrorState     INT;
    SELECT
      @ErrorMessage   = ERROR_MESSAGE(),
      @ErrorSeverity  = ERROR_SEVERITY(),
      @ErrorState     = ERROR_STATE();
    RAISERROR (
      @ErrorMessage,
      @ErrorSeverity,
      @ErrorState    
    );
    ROLLBACK TRANSACTION
  END CATCH

GO


thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 07:27 AM
Post#10



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


I must not have followed your instructions, correctly. I changed the procedure per your last post, and in testing it, it creates 3 new sales orders each time. I'm not sure why.

I am using an empty table. I created a sales order, and after it was finished, I copied it and wound up with a total of 4 sales orders (instead of 2). I rechecked my procedure and tried again, and now have 7 orders (should only have 4).

I copied and pasted your last post (instead of changing it) and tried again, with the same results. I don't understand why it is tripling the sales order.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 07:34 AM
Post#11



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


Okay, to be fair, I put my procedure back the way I had it and tested again. It created the sales order 3 times, so I didn't have it working correctly at all. I didn't realize it was creating the order 3 times as I was working with a large table, which I've since removed all the records, so I can see the triple records clearly.

What should I look at to figure out where the triplicates are coming from?
This post has been edited by LilAnnCC1: Nov 11 2019, 07:37 AM

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 07:45 AM
Post#12


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


What happens when you run a simple SELECT:
CODE
SELECT
  *
FROM tSale
WHERE ID = @CopyID;

?
(Subsitute the @CopyID for the relevant value)

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 08:08 AM
Post#13



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


I get 1 record. I tested in SQL and Access and get 1 record when I

'SELECT * FROM tSale WHERE ID=1' (dbo_tSale in Access)

When I test procedure in SQL, I get 1 new record. So server/procedure works correctly in the server. So Access is the problem. This is my VBA procedure in access:

CODE
Private Sub cmdCopy_Click()
On Error GoTo ErrorHandling_Error

'Copy Sales Order
    Dim OldID As Integer        'ID of order that user wants to copy
    Dim strDate As String       'Format to SQL Server
    Dim intTeam As Integer
'    Dim intRevision As Integer  Will always force as a 1, otherwise new order would have a revision and shouldn't
    Dim PO As String            'New PO number
    Dim Store As Integer        'New Store ID
    Dim ShipTax As Integer      'Tax ID for Store
    Dim ShipAddress As String   'Store Address
      
    OldID = Forms!f_Sale!ID
    strDate = "'" & Format(Date, "yyyy-mm-dd") & "'"
    intTeam = Forms!f_Sale!TeamID
    PO = "'" & Me.txtPO & "'"
    Store = Me.cboNewStore
    ShipTax = Me.txtTaxID
    ShipAddress = "'" & Me.txtAddress & "'"
              
    With CurrentDb.QueryDefs("qPassR")
        .SQL = "EXEC spSalesCopy " & OldID & "," & strDate & "," & intTeam & ",1," & PO & "," & Store & "," & ShipTax & "," & ShipAddress
    End With
      
'Open to newly created
    DoCmd.Close acForm, "f_Sale"
    DoCmd.OpenForm "f_Sale", , , , , , DLookup("ID", "qPassR")
    DoCmd.Close acForm, "sf_SaleCopy"


ErrorHandling_Exit:
Exit Sub

ErrorHandling_Error:
'      If Err.Number = Then
'      Else
             MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "cmdCopy"
'      End If
Resume ErrorHandling_Exit

End Sub


When I open the qPassR query, it has 1 record, but the Sale table shows a total of 4 records (Order 1, 2,3,4) and should only be 2.

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 08:16 AM
Post#14



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


This is what the qPass SQL looks like:

EXEC spSalesCopy 1,'2019-11-11',6,1,'TEST 2',29,167,'AnyCompany
STORE: 4
1902 Any Street
City USA 99999-9999
'

Do you think it's because I'm sending the address over?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 08:32 AM
Post#15


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


How many child records are there for ID = 1? (in tSaleDetail)

I can't see why it should be doing this

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 08:37 AM
Post#16


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


Did you change the call to fnSalesGetNumber() as I suggested.

How you had it, it might be creating three new sales numbers (though I still don't know why it would cause three new records)

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 08:42 AM
Post#17



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


I think I figured it out.

In my VBA code, I'm running the exec statement by mistake:

CODE
'Open to newly created
    DoCmd.Close acForm, "f_Sale"
    DoCmd.OpenForm "f_Sale", , , , , , DLookup("ID", "qPassR")
    DoCmd.Close acForm, "sf_SaleCopy"


So, I can't use the lookup to the qPassR query. I think this is what is cause it to run more than it should.

What would be the best way to get the ID of the new record?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 08:57 AM
Post#18


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


Yes, each call to the SP will add a new copy to tSale!

At the end of the SP you SELECT the newly inserted record, so you can use that to get the new ID.
CODE
Private Sub cmdCopy_Click()
On Error GoTo ErrorHandling_Error

'Copy Sales Order
    Dim OldID As Integer        'ID of order that user wants to copy
    Dim strDate As String       'Format to SQL Server
    Dim intTeam As Integer
'    Dim intRevision As Integer  Will always force as a 1, otherwise new order would have a revision and shouldn't
    Dim PO As String            'New PO number
    Dim Store As Integer        'New Store ID
    Dim ShipTax As Integer      'Tax ID for Store
    Dim ShipAddress As String   'Store Address
    Dim NewID As Long           ' <-- I added this
      
    OldID = Forms!f_Sale!ID
    strDate = "'" & Format(Date, "yyyy-mm-dd") & "'"
    intTeam = Forms!f_Sale!TeamID
    PO = "'" & Me.txtPO & "'"
    Store = Me.cboNewStore
    ShipTax = Me.txtTaxID
    ShipAddress = "'" & Me.txtAddress & "'"
              
    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")
          End If
          .Close
        End With
    End With
      
'Open to newly created
    With Forms("f_Sale")
      .Requery
      With .RecordSetClone
        .FindFirst "ID = " & NewID
        If Not .NoMatch Then
          Set Forms("f_Sale").Bookmark = .Bookmark
        End If
      End With
    End With
    DoCmd.Close acForm, "sf_SaleCopy"


ErrorHandling_Exit:
Exit Sub

ErrorHandling_Error:
'      If Err.Number = Then
'      Else
             MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation, "cmdCopy"
'      End If
Resume ErrorHandling_Exit

End Sub

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Nov 11 2019, 09:11 AM
Post#19



Posts: 849
Joined: 31-May 04
From: Wisconsin, USA


I couldn't get the form to open to the new record, so I changed it to:

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")
          End If
          .Close
        End With
    End With
      
'Open to newly created
'    With Forms("f_Sale")
'      .Requery
'      With .RecordsetClone
'        .FindFirst "ID = " & NewID
'        If Not .NoMatch Then
'          Set Forms("f_Sale").Bookmark = .Bookmark
'        End If
'      End With
'    End With

--This is what I went with
    With Forms("f_Sale")
        .Requery
    End With
    DoCmd.OpenForm "f_Sale", , , , , , NewID
    DoCmd.Close acForm, "sf_SaleCopy"


I am a huggy person. Consider yourself bear hugged! I love you!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
cheekybuddha
post Nov 11 2019, 09:19 AM
Post#20


UtterAccess Moderator
Posts: 11,882
Joined: 6-December 03
From: Telegraph Hill


All bear hugs gratefully received!

yayhandclap.gif

Continued success with your project!

d

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 03:50 AM