I am currently working on an access inventory system that will track all of our items and their categories (IT materials, office equipment, Office Supplies, communication materials, etc.) by generating automated sequential numbers as new records are updated into the system.
For instance: the “Item ID” attributed to “Office Equipment” is 100, the “Item ID” attributed to “IT materials” is 300. The first chair that got purchased or donated to our organization will be automatically labeled 100-1 in the “Item ID” field when I select Office Equipment in the drop down combo box on the “Item Type” field. A month later we purchased a new couch as part of our furniture stock and as we enter it in the record and select “Office Equipment” in the Drop down combo box of the “Item Type” field, the “Item ID” auto-increments automatically and becomes 100-2 although there are other records (following the same process in between).
I have inserted all the information below since I don't know how to use the VBA data and adapt it to my system. I know this may be a lot to ask…but finalizing this would be very helpful in helping us making sure our resources are distributed and monitored well in order to provide good service to beneficiaries.
I have received some suggestions but I don’t know how to adapt them to my system since I am not fluent in VBA:
I have gotten a suggestion...but can use it because I don't know how to adapt his code to my system.
Please HELP!
Below are all the fields exactly in the same order they are positioned in a table I named "Asset Inventory System". However the only relevant ones for this task are the "Item ID" field for the identifier (example: 200-1, 300-4, 100-6) and the "Item Type" field for the categories of item (example: Office Equipment, Office Supplies, IT Material, etc.) :
Item ID- Item Type- Description- Make & Model- Serial #- Unit- Condition- Location or Personal Use- Department- Cost or Value in HTG/ USD- Currency- Total Cost or Value- Purchase #- Date received- Donor- Loss/ Theft- Comments
Below are the item categories in a drop down box in the "Item Type" field as well as their identifiers (and example of increments) assigned to them.
Office Equipment 100-1,2,3…etc.
Office Supplies 200-1,2,3…etc.
IT Material 300-1,2,3…etc.
Communication Material 400-1,2,3…etc.
Audio & Video Material 500-1,2,3…etc.
Medical Equipment 600-1,2,3…etc.
Medical Supplies 700-1,2,3…etc.
Construction Material 800-1,2,3…etc.
Rolling Stock 900-1,2,3…etc.
I have gotten a few suggestions while searching online...here is the primary table to start with:
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
DROP CONSTRAINT [DF_SequenceControls_NextAvailable]
END
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
DROP TABLE [dbo].[SequenceControls]
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
SET ANSI_PADDING ON
go
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
BEGIN
CREATE TABLE [dbo].[SequenceControls](
[SequenceCtrlID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](30) NOT NULL,
[Category] [varchar](30) NULL,
[NextAvailable] [int] NOT NULL,
CONSTRAINT [PK_SequenceControls] PRIMARY KEY CLUSTERED
(
[SequenceCtrlID] ASC
) ON [PRIMARY]
END
go
SET ANSI_PADDING OFF
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'SequenceCtrlID'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Unique ID for each row' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'SequenceCtrlID'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'TableName'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of the Table in which the Sequence Number is to be controlled.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'TableName'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'Category'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Optional category to be used to allow control of multiple sequences within a table' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'Category'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'NextAvailable'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The next available sequence number within a given Table/Category sequence' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'NextAvailable'
go
IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
ADD CONSTRAINT [DF_SequenceControls_NextAvailable]
DEFAULT ((1))
FOR [NextAvailable]
END
go
and here is the Stored procedure to get the next available number in the sequence:
-- Author: Ralph D. Wilson II
-- Create date: 2012-05-22
-- Description: dbo.usp_TWG_Fetch_Next_Availble_Number
-- This procedure will accept a TableName
-- and a Category and return the lowest
-- NextAvailable value for that sequence.
--
-- If the sequence does not exist within
-- the SequenceControl table, it will initialize
-- the sequence. If there is only one row available
-- for that sequence, it will increment the
-- NextAvailable value and insert another row
-- with the new NextAvailable value.
--
-- MODIFICATION HISTORY:
-- Date Author Comment
-- ---------- ------ -------------------------
-- 2012-05-22 RDWII Initially Coded
-- =============================================
ALTER PROCEDURE dbo.usp_TWG_Fetch_Next_Availble_Number
@TableName Varchar(30) = ''
,@Category VarChar(30) = 'NONE'
,@NextAvailable INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @AvailableNumbers INT;
SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category; IF (@AvailableNumbers = 0) BEGIN -- Since there are no rows whatsoever for this sequence -- we need to initialize the sequence, returning the -- first sequence number, and insert a second -- sequence number INSERT INTO SequenceControls ( TableName ,Category ,InUse ) Values ( @TableName ,@Category ,'Y' ); INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,2 );
SET @NextAvailable = 1; END ELSE BEGIN -- Since there ARE rows whatsoever for this sequence -- we need to try to get the NextAvailable sequence -- number
SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N'; SELECT @NextAvailable = NextAvailable FROM ( SELECT TOP 1 NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N' ) Z ORDER BY NextAvailable; IF @AvailableNumbers = 0 BEGIN -- Oops, all of the sequence numbers are marked as InUse. -- That means that we need return a sequence number one -- greater than the highest one in the sequence and then -- insert the next one after that. SELECT @NextAvailable = MAX(NextAvailable) + 1 FROM SequenceControls WHERE TableName = @TableName AND Category = @Category;
PRINT 'Inserting a new available row for: ' + @TableName + '/' + @Category;
INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,@NextAvailable + 1 );
INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ,InUse ) Values ( @TableName ,@Category ,@NextAvailable ,'Y' );
END ELSE BEGIN -- Okay, we got one. Now, we need to see if there was only one available.
IF @AvailableNumbers = 1 BEGIN -- Yup, only one was available, so we need to flag that one as InUse -- and insert another row with the next higher NextAvailable value. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) SELECT TableName ,Category ,MAX(NextAvailable) + 1 AS NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable GROUP BY TableName ,Category; END BEGIN -- Nope, more than one was available, so we just need to flag -- that one as InUse. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; END END END END Here is the Stored procedure to release a previosuly selected (but not used number)in the sequence.-- Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Release_Sequence_Number-- This procedure releases a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Release_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE SequenceControls SET InUse = 'N' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; ENDStored Procedure to remove (consume) a previously selected sequence number (Note: if an unselected number is specified, nothing happens): Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END
This is all! i would be very very very greatful if someone could help! thank you again!!!
For instance: the “Item ID” attributed to “Office Equipment” is 100, the “Item ID” attributed to “IT materials” is 300. The first chair that got purchased or donated to our organization will be automatically labeled 100-1 in the “Item ID” field when I select Office Equipment in the drop down combo box on the “Item Type” field. A month later we purchased a new couch as part of our furniture stock and as we enter it in the record and select “Office Equipment” in the Drop down combo box of the “Item Type” field, the “Item ID” auto-increments automatically and becomes 100-2 although there are other records (following the same process in between).
I have inserted all the information below since I don't know how to use the VBA data and adapt it to my system. I know this may be a lot to ask…but finalizing this would be very helpful in helping us making sure our resources are distributed and monitored well in order to provide good service to beneficiaries.
I have received some suggestions but I don’t know how to adapt them to my system since I am not fluent in VBA:
I have gotten a suggestion...but can use it because I don't know how to adapt his code to my system.
Please HELP!
Below are all the fields exactly in the same order they are positioned in a table I named "Asset Inventory System". However the only relevant ones for this task are the "Item ID" field for the identifier (example: 200-1, 300-4, 100-6) and the "Item Type" field for the categories of item (example: Office Equipment, Office Supplies, IT Material, etc.) :
Item ID- Item Type- Description- Make & Model- Serial #- Unit- Condition- Location or Personal Use- Department- Cost or Value in HTG/ USD- Currency- Total Cost or Value- Purchase #- Date received- Donor- Loss/ Theft- Comments
Below are the item categories in a drop down box in the "Item Type" field as well as their identifiers (and example of increments) assigned to them.
Office Equipment 100-1,2,3…etc.
Office Supplies 200-1,2,3…etc.
IT Material 300-1,2,3…etc.
Communication Material 400-1,2,3…etc.
Audio & Video Material 500-1,2,3…etc.
Medical Equipment 600-1,2,3…etc.
Medical Supplies 700-1,2,3…etc.
Construction Material 800-1,2,3…etc.
Rolling Stock 900-1,2,3…etc.
I have gotten a few suggestions while searching online...here is the primary table to start with:
IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
DROP CONSTRAINT [DF_SequenceControls_NextAvailable]
END
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
DROP TABLE [dbo].[SequenceControls]
go
/****** Object: Table [dbo].[SequenceControls] Script Date: 05/22/2012 08:51:32 ******/
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
SET ANSI_PADDING ON
go
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SequenceControls]')
AND type in (N'U')
)
BEGIN
CREATE TABLE [dbo].[SequenceControls](
[SequenceCtrlID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](30) NOT NULL,
[Category] [varchar](30) NULL,
[NextAvailable] [int] NOT NULL,
CONSTRAINT [PK_SequenceControls] PRIMARY KEY CLUSTERED
(
[SequenceCtrlID] ASC
) ON [PRIMARY]
END
go
SET ANSI_PADDING OFF
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'SequenceCtrlID'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Unique ID for each row' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'SequenceCtrlID'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'TableName'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Name of the Table in which the Sequence Number is to be controlled.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'TableName'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'Category'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'Optional category to be used to allow control of multiple sequences within a table' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'Category'
go
IF NOT EXISTS ( SELECT *
FROM ::fn_listextendedproperty( N'MS_Description' ,
N'SCHEMA',
N'dbo',
N'TABLE',
N'SequenceControls',
N'COLUMN',
N'NextAvailable'
)
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'The next available sequence number within a given Table/Category sequence' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'SequenceControls',
@level2type=N'COLUMN',
@level2name=N'NextAvailable'
go
IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[DF_SequenceControls_NextAvailable]')
AND type = 'D'
)
BEGIN
ALTER TABLE [dbo].[SequenceControls]
ADD CONSTRAINT [DF_SequenceControls_NextAvailable]
DEFAULT ((1))
FOR [NextAvailable]
END
go
and here is the Stored procedure to get the next available number in the sequence:
-- Author: Ralph D. Wilson II
-- Create date: 2012-05-22
-- Description: dbo.usp_TWG_Fetch_Next_Availble_Number
-- This procedure will accept a TableName
-- and a Category and return the lowest
-- NextAvailable value for that sequence.
--
-- If the sequence does not exist within
-- the SequenceControl table, it will initialize
-- the sequence. If there is only one row available
-- for that sequence, it will increment the
-- NextAvailable value and insert another row
-- with the new NextAvailable value.
--
-- MODIFICATION HISTORY:
-- Date Author Comment
-- ---------- ------ -------------------------
-- 2012-05-22 RDWII Initially Coded
-- =============================================
ALTER PROCEDURE dbo.usp_TWG_Fetch_Next_Availble_Number
@TableName Varchar(30) = ''
,@Category VarChar(30) = 'NONE'
,@NextAvailable INT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @AvailableNumbers INT;
SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category; IF (@AvailableNumbers = 0) BEGIN -- Since there are no rows whatsoever for this sequence -- we need to initialize the sequence, returning the -- first sequence number, and insert a second -- sequence number INSERT INTO SequenceControls ( TableName ,Category ,InUse ) Values ( @TableName ,@Category ,'Y' ); INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,2 );
SET @NextAvailable = 1; END ELSE BEGIN -- Since there ARE rows whatsoever for this sequence -- we need to try to get the NextAvailable sequence -- number
SELECT @AvailableNumbers = COUNT(1) FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N'; SELECT @NextAvailable = NextAvailable FROM ( SELECT TOP 1 NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND InUse = 'N' ) Z ORDER BY NextAvailable; IF @AvailableNumbers = 0 BEGIN -- Oops, all of the sequence numbers are marked as InUse. -- That means that we need return a sequence number one -- greater than the highest one in the sequence and then -- insert the next one after that. SELECT @NextAvailable = MAX(NextAvailable) + 1 FROM SequenceControls WHERE TableName = @TableName AND Category = @Category;
PRINT 'Inserting a new available row for: ' + @TableName + '/' + @Category;
INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) Values ( @TableName ,@Category ,@NextAvailable + 1 );
INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ,InUse ) Values ( @TableName ,@Category ,@NextAvailable ,'Y' );
END ELSE BEGIN -- Okay, we got one. Now, we need to see if there was only one available.
IF @AvailableNumbers = 1 BEGIN -- Yup, only one was available, so we need to flag that one as InUse -- and insert another row with the next higher NextAvailable value. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; INSERT INTO SequenceControls ( TableName ,Category ,NextAvailable ) SELECT TableName ,Category ,MAX(NextAvailable) + 1 AS NextAvailable FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable GROUP BY TableName ,Category; END BEGIN -- Nope, more than one was available, so we just need to flag -- that one as InUse. UPDATE SequenceControls SET InUse = 'Y' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @NextAvailable; END END END END Here is the Stored procedure to release a previosuly selected (but not used number)in the sequence.-- Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Release_Sequence_Number-- This procedure releases a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Release_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; UPDATE SequenceControls SET InUse = 'N' WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; ENDStored Procedure to remove (consume) a previously selected sequence number (Note: if an unselected number is specified, nothing happens): Author: Ralph D. Wilson II-- Create date: 2012-05-22-- Description: dbo.usp_TWG_Consume_Sequence_Number-- This procedure consumes a sequence -- number that was previously marked -- as InUse.---- MODIFICATION HISTORY:-- Date Author Comment-- ---------- ------ --------------------------- 2012-05-22 Initials Initially Coded-- =============================================ALTER PROCEDURE dbo.usp_TWG_Consume_Sequence_Number @TableName Varchar(30) = '' ,@Category VarChar(30) = 'NONE' ,@SequenceNumberToRelease INTASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DELETE FROM SequenceControls WHERE TableName = @TableName AND Category = @Category AND NextAvailable = @SequenceNumberToRelease AND InUse = 'Y'; END
This is all! i would be very very very greatful if someone could help! thank you again!!!