jcolby at colbyconsulting.com
jcolby at colbyconsulting.com
Mon Jul 21 10:59:39 CDT 2003
>Thus only one record in the payroll number table is ever true, and always the last number created. For a given employee. Sorry about that. John W. Colby www.colbyconsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of jcolby at colbyconsulting.com Sent: Monday, July 21, 2003 11:49 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] [dba-SQLServer]Copying a record VB6 & SQL Server 7.0 If the payroll number is all that differs, I would just create a child record for the payroll number with an Active flag and a foreign key that points back to the employee id in the employee table. When the payroll number record is created, the default value for the Active flag is true so that record is active. when a person leaves the company, set the active flag to false. When they come back, create a NEW record in the payroll number table, with the active flag true. Thus only one record in the payroll number table is ever true, and always the last number created. Now, for processing purposes, join the payroll number table to the employee table and filter on Active = true. Thus only a single "record" will be pulled, the employee record for that employee and the ACTIVE payroll number record. Use that query as the base for all processing of active employees. John W. Colby www.colbyconsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of paul.hartland at fsmail.net Sent: Monday, July 21, 2003 11:00 AM To: dba-sqlserver at databaseadvisors.com; accessd at databaseadvisors.com; dba-vb at databaseadvisors.com Subject: [AccessD] [dba-SQLServer]Copying a record VB6 & SQL Server 7.0 To all, we have a high turnover of staff, mainly because most are casual workers and don't want full-time employment. When a member of staff leaves we issue them with a finish date. However if they decide they want to come back we need to re-employ them with a new payroll number, I have a re-employ button that asks the user for the new payroll number...but here is where I need help : I then need to create an identical personnel record (66 fields to the one shown on screen) but with the new payroll number. My front-end is Visual Basic 6 and my back-end is SQL Server 7.0. I thought the easiest way would be to create an insert stored procedure with parameters and then execute it from VB, so I created the SP and ran it using Query Analyser (Worked Fine), I also added it to my dataenvironment as a command. However when I type DEGenSQL.sp_Personnel_ReEmploy I get no option to enter any of the parameters and if I try RUN/Start With Full Compile I get the following error message : Compile Error : Subscript Out Of Range This makes me think there are too many parameters required. Has anyone else come across this, or does anyone know of a better way to go about this. Paul Hartland __________________________________________________________________________ Join Freeserve http://www.freeserve.com/time/ Winner of the 2003 Internet Service Providers' Association awards for Best Unmetered ISP and Best Consumer Application. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com