SQL Server/Access Front End Need to create a checkout process on primary key (product_number)

First of all: While this is not a hard task, it is a complicate one. Cause what happens when a user forgets to check-in again?

But the solution is pretty simple: Use a store procedure for this. Something like

CREATE PROCEDURE CheckoutItem ( @ProductNumber INT, @UserName SYSNAME OUTPUT )
AS
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

	-- Constants.
    DECLARE @GENERIC_ERROR INT = -1; 
    DECLARE @NO_ERROR INT = 0;    

    BEGIN TRY 
        INSERT INTO Checkout ( ProductNumber ) VALUES ( @ProductNumber );
        RETURN @NO_ERROR;
    END TRY
    BEGIN CATCH
        SELECT @UserName = UserName 
        FROM Checkout
        WHERE ProductNumber = @ProductNumber;
        RETURN @GENERIC_ERROR;
    END CATCH;

Select allOpen in new window

with

CREATE TABLE Checkout
    (
        ProductNumber INT NOT NULL PRIMARY KEY ,
        UserName sysname
            DEFAULT ( SUSER_SNAME())
    );

Select allOpen in new window

and run this as pass-through query in Access to check-out your product.

Can we include a message to the user trying to check out this product_name record that is already checked out in this stored procedure?