vb6 middle-tier and front-end code generator

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGen_Geographic]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CodeGen_Geographic]
GO

CREATE TABLE [dbo].[CodeGen_Geographic] (
[GeographicCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO



INSERT INTO CodeGen_Geographic(GeographicCode)
SELECT 'BranchCode'


GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGen_DataTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CodeGen_DataTypes]
GO

CREATE TABLE [dbo].[CodeGen_DataTypes] (
[SqlType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VbType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VbTypeDim] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO





INSERT INTO CodeGen_DataTypes(SqlType, VbType, VbTypeDim)
SELECT 'bigint','DataTypeEnum.adBigInt','Long' UNION
SELECT 'binary','DataTypeEnum.adBinary','' UNION
SELECT 'bit','DataTypeEnum.adBoolean','Boolean' UNION
SELECT 'char','DataTypeEnum.adChar','String' UNION
SELECT 'datetime','DataTypeEnum.adDBTimeStamp','Date' UNION
SELECT 'decimal','DataTypeEnum.adNumeric','Variant '' Decimal' UNION
SELECT 'float','DataTypeEnum.adDouble','Double' UNION
SELECT 'image','DataTypeEnum.adLongVarBinary','' UNION
SELECT 'int','DataTypeEnum.adInteger','Long' UNION
SELECT 'money','DataTypeEnum.adCurrency','Currency' UNION
SELECT 'nchar','DataTypeEnum.adWChar','String' UNION
SELECT 'ntext','DataTypeEnum.adLongVarWChar','String' UNION
SELECT 'numeric','DataTypeEnum.adNumeric','Variant '' Numeric' UNION
SELECT 'nvarchar','DataTypeEnum.adVarWChar','String' UNION
SELECT 'real','DataTypeEnum.adSingle','Single' UNION
SELECT 'smalldatetime','DataTypeEnum.adDBTimeStamp','Date' UNION
SELECT 'smallint','DataTypeEnum.adUnsignedSmallInt','Integer' UNION
SELECT 'smallmoney','DataTypeEnum.adCurrency','' UNION
SELECT 'sql_variant','','' UNION
SELECT 'sysname','','' UNION
SELECT 'text','DataTypeEnum.adLongVarChar','String' UNION
SELECT 'timestamp','DataTypeEnum.adBinary','' UNION
SELECT 'tinyint','DataTypeEnum.adUnsignedTinyInt','Byte' UNION
SELECT 'uniqueidentifier','DataTypeEnum.adGUID','String' UNION
SELECT 'varbinary','DataTypeEnum.adVarBinary','' UNION
SELECT 'varchar','DataTypeEnum.adVarChar','String'



go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenFe_OneTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenFe_OneTableNoAutoId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenFe_TwoTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenFe_TwoTableNoAutoId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenMt_OneTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenMt_OneTableNoAutoId]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CodeGenMt_TwoTableNoAutoId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CodeGenMt_TwoTableNoAutoId]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO












CREATE PROCEDURE CodeGenFe_OneTableNoAutoId
@TableTransactName VARCHAR(100),
@TableName VARCHAR(100)
AS

IF object_id(@TableName) IS NULL BEGIN
PRINT 'Table ' + @TableTransactName + ' does not exists'
RETURN
END





DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)



DECLARE @TryBlockBegin VARCHAR(8000)

SET @TryBlockBegin = @Tab + 'On Error GoTo goCatchError'
+ CHAR(13)
+ @Tab + '''Try{' + CHAR(13) + CHAR(13)

DECLARE @TryBlockEnd VARCHAR(8000)

SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)


DECLARE @CatchBlockBegin VARCHAR(8000)

SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)

+ @Tab + 'Screen.MousePointer = vbDefault'






DECLARE @FinallyBlockBegin VARCHAR(8000)

SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)

DECLARE @FinallyBlockEnd VARCHAR(8000)

SET @FinallyBlockEnd = @Tab + '''}'




DECLARE @PkDeclare VARCHAR(8000)

set @PkDeclare = ''
select
@PkDeclare = @PkDeclare + CHAR(13) + @Tab + 'Dim ' + c.Name + ' As String'
+ CHAR(13) + @Tab + C.Name + ' = txt' + C.Name + '.Value'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




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

print 'Option Explicit'
print 'Option Compare Text'
print 'Option Base 0'

print ''

PRINT '''Passed parameters...'

print 'Public ParentForm As Object'

PRINT '''...passed parameters'

print ''

print 'Private m_IsDataExisting as Boolean'

print ''



PRINT 'Private Sub Form_Load()'

PRINT ''

PRINT @Tab + 'Set Me.Icon = LoadPicture("")'

PRINT ''

PRINT 'End Sub'

PRINT ''



PRINT 'Private Sub Form_Activate()'

PRINT ''

PRINT @Tab + 'ParentForm.MessageToUser "' + @TableTransactName + ' ready."'

PRINT ''

PRINT 'End Sub'


PRINT ''

PRINT ''

DECLARE @c CURSOR

DECLARE @C_ReferencedTableName VARCHAR(100)

SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, ReferencedTableName = rt.name, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = sc.id AND sfk.fkey = sc.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
WHERE sc.id = object_id(@TableName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid


DECLARE @S VARCHAR(8000)


DECLARE @PkFieldsMtParameter VARCHAR(8000)

set @PkFieldsMtParameter = NULL

select
@PkFieldsMtParameter = coalesce(
@PkFieldsMtParameter + ', ' + c.Name
, c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid


DECLARE @PkFieldsOpenParameter VARCHAR(8000)
set @PkFieldsOpenParameter = NULL
select
@PkFieldsOpenParameter = coalesce(
@PkFieldsOpenParameter + ', Rs.Fields("' + c.Name + '").Value'
, 'Rs.Fields("' + c.Name + '").Value')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




DECLARE @PkFieldsFirstKey VARCHAR(8000)
set @PkFieldsFirstKey = NULL
select
TOP 1 @PkFieldsFirstKey = C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid



declare @PkFieldsFeParameter varchar(8000)
set @PkFieldsFeParameter = NULL

select
@PkFieldsFeParameter = coalesce(
@PkFieldsFeParameter + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + c.Name + ' As String')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid







PRINT '''User initiated events...'

print 'Private Sub cmdDelete_Click()'
PRINT @Tab + 'DoDelete'
PRINT 'End Sub'

PRINT 'Private Sub cmdSave_Click()'
PRINT @Tab + 'DoSave'
PRINT 'End Sub'

PRINT 'Private Sub cmdNew_Click()'
PRINT @Tab + 'DoNew'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'

PRINT @TryBlockBegin


PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @TableTransactName + '_Browse(E,"")'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_Dropdown')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_GotoRecord(RS As ADODB.Recordset)'

PRINT @TryBlockBegin

PRINT @Tab + 'DoOpen ' + @PkFieldsOpenParameter

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_GotoRecord')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''


PRINT '''...User initiated events'


DECLARE @C_Name AS VARCHAR(100)
DECLARE @C_XType AS INT
DECLARE @C_VbType VARCHAR(100)
DECLARE @C_Length INT



---------------------
-- generate lookup...

PRINT ''

PRINT '''Lookups...'

OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN



IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinueLookup

END





SET @S = @C_Name

PRINT ''

PRINT 'Private Sub txt' + @C_Name + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'

PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @C_ReferencedTableName + '_Lookup(E,"")'
PRINT @Tab + 'RaiseIfErr E'
PRINT @TryBlockEnd


PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_DropDown')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'


PRINT ''


PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(Rs As ADODB.Recordset)'

PRINT @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'

PRINT 'End Sub'

PRINT ''

goContinueLookup:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END


CLOSE @C


PRINT '''...Lookups'

PRINT ''

-- ..generate lookup
---------------------

PRINT ''

PRINT '''Transactions...'

PRINT ''


SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE sc.id = object_id(@TableName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid





PRINT 'Private Sub DoNew()'


OPEN @C



DECLARE @Value AS VARCHAR(100)

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN



IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

PRINT + @Tab + 'txt' + @C_Name + '.IsEditable = True'

END




SET @S = @C_Name


IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
SET @Value = '0'
END
ELSE IF @C_XType = 104 BEGIN
SET @Value = 'False'
END
ELSE BEGIN
SET @Value = '""'
END


PRINT @Tab + 'txt' + @S + '.Value = ' + @Value




FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END

PRINT @Tab + 'm_IsDataExisting = False'


PRINT @Tab + 'ParentForm.MessageToUser "New ' + @TableName + ' ready."'

PRINT 'End Sub'

PRINT ''

CLOSE @C









PRINT 'Public Sub DoOpen(' + @PkFieldsFeParameter + ')'

PRINT @TryBlockBegin

PRINT @Tab + 'Dim Rs As ADODB.Recordset'

PRINT ''


PRINT @Tab + 'Dim E As String'

PRINT @Tab + 'Services.' + @TableName + '_Open E, "", ' + @PkFieldsMtParameter + ', Rs'
PRINT @Tab + 'RaiseIfErr E'


PRINT @Tab + 'If Rs.RecordCount = 1 Then'

OPEN @C


FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN

IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and c.name = @C_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id

)
BEGIN

PRINT + @Tab + @Tab + 'txt' + @C_Name + '.IsEditable = False'

END


SET @S = @C_Name

PRINT @Tab + @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'



FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END





PRINT @Tab + @Tab + 'm_IsDataExisting = True'


PRINT @Tab + @Tab + 'ParentForm.MessageToUser "Existing ' + @TableName + ' ready."'

CLOSE @C

PRINT @Tab + 'End If'


PRINT @TryBlockEnd

PRINT ''

PRINT @CatchBlockBegin


PRINT dbo.CodeGenFe_CatchBlockEnd('DoOpen')

PRINT ''

PRINT @FinallyBlockBegin

PRINT ''

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''





PRINT 'Private Sub DoDelete()'

PRINT @TryBlockBegin


print @pkdeclare

PRINT ''

declare @PkDisplay varchar(8000)

set @PkDisplay = NULL
select
@PkDisplay = coalesce(
@PkDisplay + ' & " " & ' + c.Name
,c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid

PRINT @Tab + 'Dim E As String'

PRINT @Tab + 'If m_IsDataExisting Then'
PRINT @Tab + @Tab + 'If MsgBox("Are you sure you do want to delete " & ' + @PkDisplay + ', vbQuestion + vbYesNo + vbDefaultButton2, "Deleting Record") = vbYes Then'

PRINT ''

PRINT @Tab + @Tab + @Tab + 'Services.' + @TableTransactName + '_Delete E, "", ' + @PkFieldsMtParameter

PRINT @Tab + @Tab + @Tab + 'RaiseIfErr E'

PRINT @Tab + @Tab + @Tab + 'DoNew'

PRINT ''

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'End If'



PRINT ''

PRINT @TryBlockEnd

PRINT ''


PRINT @CatchBlockBegin


PRINT dbo.CodeGenFe_CatchBlockEnd('DoDelete')

PRINT ''


PRINT @FinallyBlockBegin


PRINT @FinallyBlockEnd


PRINT 'End Sub'


PRINT ''

PRINT 'Private Function IsInputValid() As Boolean'


print @Tab + 'IsInputValid = False'


OPEN @C


FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN



-- numbers
IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
print ''
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT ''
END
-- date
ELSE IF @C_XType IN (61, 58) BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And IsNull(txt' + @C_Name + '.Value) Then'
PRINT @Tab + @Tab + 'ParentForm.MessageToUser "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
ELSE BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And txt' + @C_Name + '.Value = "" Then'
PRINT @Tab + @Tab + 'ParentForm.MessageToUser "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END





FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C


PRINT @Tab + '''Put other validations here...'


PRINT @Tab + '''...Put other validations here'

PRINT @Tab + 'IsInputValid = True'


PRINT 'End Function'


PRINT ''

PRINT 'Private Sub DoSave()'



PRINT @Tab + 'If Not IsInputValid() Then'

PRINT @Tab + @Tab + 'Exit Sub'

PRINT @Tab + 'End If'





PRINT @PkDeclare

PRINT ''


PRINT @TryBlockBegin

PRINT @Tab + 'Dim IsExists As Boolean'
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'IsExists = Services.' + @TableName + '_IsExisting(E, "", ' + @PkFieldsMtParameter + ')'
PRINT @Tab + 'RaiseIfErr E'

PRINT ''

PRINT @Tab + 'If m_IsDataExisting Then '

PRINT @Tab + @Tab + 'If Not IsExists Then'

PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " is already deleted before you have even saved", vbInformation'

PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'Else'

PRINT @Tab + @Tab + 'If IsExists Then'

PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " already in the database", vbInformation'

PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'End If'


------------------------
PRINT ''

PRINT @Tab + 'Dim RsSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsSrc = New ADODB.Recordset'



OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinueType

END






SET @S = @C_Name



IF @C_XType IN (127,106,62,56,60,108,59,52,122,48) BEGIN
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE BEGIN
PRINT @Tab + 'RsSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType + ', ' + CONVERT(VARCHAR,@C_Length)
END





goContinueType:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C

PRINT @Tab + 'RsSrc.Open'

PRINT ''



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





PRINT @Tab + 'RsSrc.AddNew'

OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinue

END






SET @S = @C_Name

PRINT @Tab + 'RsSrc.Fields("' + @S + '").Value = txt' + @S + '.Value'


goContinue:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C

PRINT @Tab + 'RsSrc.Update'

PRINT ''

PRINT @Tab + 'ParentForm.MessageToUser "' + @TableName + ' saving..."'

PRINT @Tab + 'Services.' + @TableName + '_Save E, "", m_IsDataExisting, ' + @PkFieldsMtParameter + ', RsSrc'
PRINT @Tab + 'RaiseIfErr E'

PRINT ''






DECLARE @PkEditable VARCHAR(8000)
set @PkEditable = ''
select
@PkEditable =
@PkEditable + @Tab + 'txt' + c.Name + '.IsEditable = False' + CHAR(13)
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid


PRINT @PkEditable

PRINT @Tab + 'm_IsDataExisting = True'

PRINT @Tab + 'ParentForm.MessageToUser "' + @TableName + ' saved."'
PRINT @Tab + 'MsgBox "' + @TableName + ' saved.", vbInformation'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT @Tab + 'ParentForm.MessageToUser ""'

PRINT dbo.CodeGenFe_CatchBlockEnd('DoSave')

PRINT @FinallyBlockBegin


PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''

PRINT '''...Transactions'

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

DEALLOCATE @C























GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO





















CREATE PROCEDURE CodeGenFe_TwoTableNoAutoId
@TransactName VARCHAR(100),
@TableHeaderName VARCHAR(100),
@TableDetailName VARCHAR(100)
AS

IF object_id(@TableHeaderName) IS NULL BEGIN
PRINT 'Table ' + @TableHeaderName + ' does not exists'
RETURN
END

IF object_id(@TableDetailName) IS NULL BEGIN
PRINT 'Table ' + @TableDetailName + ' does not exists'
RETURN
END





DECLARE @Tab VARCHAR(4)
SET @Tab = SPACE(4)



DECLARE @TryBlockBegin VARCHAR(8000)

SET @TryBlockBegin = @Tab + 'On Error GoTo goCatchError'
+ CHAR(13)
+ @Tab + '''Try{' + CHAR(13) + CHAR(13)

DECLARE @TryBlockEnd VARCHAR(8000)

SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)


DECLARE @CatchBlockBegin VARCHAR(8000)

SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)

+ @Tab + 'Screen.MousePointer = vbDefault'






DECLARE @FinallyBlockBegin VARCHAR(8000)

SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)

DECLARE @FinallyBlockEnd VARCHAR(8000)

SET @FinallyBlockEnd = @Tab + '''}'




DECLARE @PkDeclare VARCHAR(8000)

set @PkDeclare = ''
select
@PkDeclare = @PkDeclare + CHAR(13) + @Tab + 'Dim ' + c.Name + ' As String'
+ CHAR(13) + @Tab + C.Name + ' = txt' + C.Name + '.Value'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id


and c.name NOT IN (SELECT GeographicCode FROM CodeGen_Geographic)

and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




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

print 'Option Explicit'
print 'Option Compare Text'
print 'Option Base 0'

PRINT ''
PRINT '''Passed parameters...'

print 'Public ParentForm As Object'

PRINT '''...passed parameters'

print ''


PRINT 'Private Const m_grdGotFocusColor = &HFF0000'
PRINT 'Private Const m_grdLostFocusColor = 0'

print ''


print 'Private m_IsDataExisting as Boolean'
PRINT 'Private m_RsDeleted As ADODB.Recordset'

print ''



PRINT 'Private Sub Form_Load()'

PRINT ''

PRINT @Tab + 'Set Me.Icon = LoadPicture("")'

PRINT ''

PRINT 'End Sub'

PRINT ''



PRINT 'Private Sub Form_Activate()'

PRINT ''

PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' ready."'

PRINT ''

PRINT 'End Sub'


PRINT ''

PRINT ''

DECLARE @c CURSOR

DECLARE @C_ReferencedTableName VARCHAR(100)

SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, ReferencedTableName = rt.name, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = sc.id AND sfk.fkey = sc.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
WHERE sc.id = object_id(@TableHeaderName)
AND SC.iscomputed = 0
AND CHARINDEX('_', SC.Name) = 0
ORDER BY SC.colid


DECLARE @S VARCHAR(8000)


DECLARE @PkFieldsMtParameter VARCHAR(8000)

set @PkFieldsMtParameter = NULL

select
@PkFieldsMtParameter = coalesce(
@PkFieldsMtParameter + ', ' + c.Name
, c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid


DECLARE @PkFieldsOpenParameter VARCHAR(8000)
set @PkFieldsOpenParameter = NULL
select
@PkFieldsOpenParameter = coalesce(
@PkFieldsOpenParameter + ', Rs.Fields("' + c.Name + '").Value'
, 'Rs.Fields("' + c.Name + '").Value')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




DECLARE @PkFieldsFirstKey VARCHAR(8000)
set @PkFieldsFirstKey = NULL
select
TOP 1 @PkFieldsFirstKey = C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid



declare @PkFieldsFeParameter varchar(8000)
set @PkFieldsFeParameter = NULL

select
@PkFieldsFeParameter = coalesce(
@PkFieldsFeParameter + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + c.Name + ' As String')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid







PRINT '''User initiated events...'

print 'Private Sub cmdDelete_Click()'
PRINT @Tab + 'DoDelete'
PRINT 'End Sub'

PRINT 'Private Sub cmdSave_Click()'
PRINT @Tab + 'DoSave'
PRINT 'End Sub'

PRINT 'Private Sub cmdNew_Click()'
PRINT @Tab + 'DoNew'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'

PRINT @TryBlockBegin


PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @TransactName + '_Browse(E,"")'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_Dropdown')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub txt' + @PkFieldsFirstKey + '_GotoRecord(RS As ADODB.Recordset)'

PRINT @TryBlockBegin

PRINT @Tab + 'DoOpen ' + @PkFieldsOpenParameter

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @PkFieldsFirstKey + '_GotoRecord')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''



PRINT 'Private Sub cmdClone_Click()'
PRINT @Tab + 'DoClone'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub DoClone()'
PRINT @Tab + 'm_IsDataExisting = False'


declare @PkClonePrimaryKeys varchar(8000)
set @PkClonePrimaryKeys = ''
select
@PkClonePrimaryKeys =
@PkClonePrimaryKeys + CHAR(13) +
CHAR(13) + @Tab + 'txt' + c.Name + '.Value = ""' +
CHAR(13) + @Tab + 'txt' + c.Name + '.IsEditable = True'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid

PRINT @PkClonePrimaryKeys

PRINT ''

PRINT @Tab + 'SetGridIsChangedStatus grd, True'

PRINT 'End Sub'



PRINT '''...User initiated events'


DECLARE @C_Name AS VARCHAR(100)
DECLARE @C_XType AS INT
DECLARE @C_VbType VARCHAR(100)
DECLARE @C_Length INT



---------------------
-- generate lookup...

PRINT ''

PRINT '''Lookups...'

OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN



IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinueLookup

END





SET @S = @C_Name

PRINT ''

PRINT 'Private Sub txt' + @C_Name + '_DropDown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'

PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set Rs = Services.' + @C_ReferencedTableName + '_Lookup(E,"")'
PRINT @Tab + 'RaiseIfErr E'
PRINT @TryBlockEnd


PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_DropDown')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'


PRINT ''


PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(Rs As ADODB.Recordset)'

PRINT @Tab + 'txt' + @C_Name + '.Value = Rs.Fields("' + @C_Name + '").Value'

PRINT 'End Sub'

PRINT ''

goContinueLookup:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END


CLOSE @C


PRINT '''...Lookups'

PRINT ''

-- ..generate lookup
---------------------

PRINT ''

PRINT '''Transactions...'

PRINT ''


SET @C = CURSOR FOR
SELECT SC.Name, SC.XType, dt.VbType, sc.length
FROM syscolumns AS SC
INNER JOIN systypes AS ST ON sc.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE sc.id = object_id(@TableHeaderName)
AND SC.iscomputed = 0
AND
(
PATINDEX('%_QTY', SC.Name) = 0
AND PATINDEX('%_COST', SC.Name) = 0
AND PATINDEX('%_PRICE', SC.Name) = 0
)
ORDER BY SC.colid





PRINT 'Private Sub DoNew()'


OPEN @C



DECLARE @Value AS VARCHAR(100)

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN



IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

PRINT + @Tab + 'txt' + @C_Name + '.IsEditable = True'

END




SET @S = @C_Name


IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
SET @Value = '0'
END
ELSE IF @C_XType = 104 BEGIN
SET @Value = 'False'
END
ELSE BEGIN
SET @Value = '""'
END


PRINT @Tab + 'txt' + @S + '.Value = ' + @Value




FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END

PRINT @Tab + 'grd.RemoveAll'

PRINT @Tab + 'm_IsDataExisting = False'


PRINT @Tab + 'ParentForm.ShowMessage "New ' + @TransactName + ' ready."'

PRINT 'End Sub'

PRINT ''

CLOSE @C









PRINT 'Public Sub DoOpen(' + @PkFieldsFeParameter + ')'

PRINT @TryBlockBegin

PRINT @Tab + 'Dim RsHeader As ADODB.Recordset'
PRINT @Tab + 'Dim RsDetail AS ADODB.Recordset'

PRINT ''


PRINT @Tab + 'Dim E As String'

PRINT @Tab + 'Services.' + @TransactName + '_Open E, "", ' + @PkFieldsMtParameter + ', RsHeader'
PRINT @Tab + 'RaiseIfErr E'


PRINT @Tab + 'If RsHeader.RecordCount = 1 Then'

OPEN @C


FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN

IF EXISTS(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and c.name = @C_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id

)
BEGIN

PRINT + @Tab + @Tab + 'txt' + @C_Name + '.IsEditable = False'

END


SET @S = @C_Name

PRINT @Tab + @Tab + 'txt' + @C_Name + '.Value = RsHeader.Fields("' + @C_Name + '").Value'



FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END



PRINT @Tab + @Tab + 'MycFunc4Ctrls.ScatterRecordsetToGrid grd, RsDetail, False'

PRINT @Tab + @Tab + 'm_IsDataExisting = True'




PRINT @Tab + @Tab + 'ParentForm.ShowMessage "Existing ' + @TransactName + ' ready."'

CLOSE @C

PRINT @Tab + 'End If'


PRINT @TryBlockEnd

PRINT ''

PRINT @CatchBlockBegin


PRINT dbo.CodeGenFe_CatchBlockEnd('DoOpen')

PRINT ''

PRINT @FinallyBlockBegin

PRINT ''

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''





PRINT 'Private Sub DoDelete()'

PRINT @TryBlockBegin


print @pkdeclare

PRINT ''

declare @PkDisplay varchar(8000)

set @PkDisplay = NULL
select
@PkDisplay = coalesce(
@PkDisplay + ' & " " & ' + c.Name
,c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid

PRINT @Tab + 'Dim E As String'

PRINT @Tab + 'If m_IsDataExisting Then'
PRINT @Tab + @Tab + 'If MsgBox("Are you sure you do want to delete " & ' + @PkDisplay + ', vbQuestion + vbYesNo + vbDefaultButton2, "Deleting Record") = vbYes Then'

PRINT ''

PRINT @Tab + @Tab + @Tab + 'Services.' + @TransactName + '_Delete E, "", ' + @PkFieldsMtParameter

PRINT @Tab + @Tab + @Tab + 'RaiseIfErr E'

PRINT @Tab + @Tab + @Tab + 'DoNew'

PRINT ''

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'End If'



PRINT ''

PRINT @TryBlockEnd

PRINT ''


PRINT @CatchBlockBegin


PRINT dbo.CodeGenFe_CatchBlockEnd('DoDelete')

PRINT ''


PRINT @FinallyBlockBegin


PRINT @FinallyBlockEnd


PRINT 'End Sub'


PRINT ''

PRINT 'Private Function IsInputValid() As Boolean'


print @Tab + 'IsInputValid = False'


OPEN @C


FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN



-- numbers
IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
print ''
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT ''
END
-- date
ELSE IF @C_XType IN (61, 58) BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And IsNull(txt' + @C_Name + '.Value) Then'
PRINT @Tab + @Tab + 'ParentForm.ShowMessage "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END
ELSE BEGIN
PRINT @Tab + 'If Not txt' + @C_Name + '.ReadOnly And txt' + @C_Name + '.Value = "" Then'
PRINT @Tab + @Tab + 'ParentForm.ShowMessage "' + @C_Name + ' can''t be left blank"'
PRINT @Tab + @Tab + 'txt' + @C_Name + '.SetFocus'
PRINT @Tab + @Tab + 'Exit Function'
PRINT @Tab + 'End If'
END





FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C


PRINT @Tab + '''Put other validations here...'


PRINT @Tab + '''...Put other validations here'

PRINT @Tab + 'IsInputValid = True'


PRINT 'End Function'


PRINT ''

PRINT 'Private Sub DoSave()'



PRINT @Tab + 'If Not IsInputValid() Then'

PRINT @Tab + @Tab + 'Exit Sub'

PRINT @Tab + 'End If'





PRINT @PkDeclare

PRINT ''


PRINT @TryBlockBegin

PRINT @Tab + 'Dim IsExists As Boolean'
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'IsExists = Services.' + @TransactName + '_IsExisting(E, "", ' + @PkFieldsMtParameter + ')'
PRINT @Tab + 'RaiseIfErr E'

PRINT ''

PRINT @Tab + 'If m_IsDataExisting Then '

PRINT @Tab + @Tab + 'If Not IsExists Then'

PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " is already deleted before you have even saved", vbInformation'

PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'Else'

PRINT @Tab + @Tab + 'If IsExists Then'

PRINT @Tab + @Tab + @Tab + 'MsgBox ' + @PkDisplay + ' & " already in the database", vbInformation'

PRINT @Tab + @Tab + @Tab + 'GoTo goFinally'

PRINT @Tab + @Tab + 'End If'

PRINT @Tab + 'End If'


------------------------
PRINT ''

PRINT @Tab + 'Dim RsHeaderSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsHeaderSrc = New ADODB.Recordset'



OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinueType

END






SET @S = @C_Name



IF @C_XType IN (127,106,62,56,60,108,59,52,122,148) BEGIN
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE IF @C_XType = 104 BEGIN -- boolean
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType
END
ELSE BEGIN
PRINT @Tab + 'RsHeaderSrc.Fields.Append "' + @C_Name + '", ' + @C_VbType + ', ' + CONVERT(VARCHAR,@C_Length)
END





goContinueType:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C

PRINT @Tab + 'RsHeaderSrc.Open'

PRINT ''



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





PRINT @Tab + 'RsHeaderSrc.AddNew'

OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinue

END






SET @S = @C_Name

PRINT @Tab + 'RsHeaderSrc.Fields("' + @S + '").Value = txt' + @S + '.Value'


goContinue:
FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_Length
END


CLOSE @C

PRINT @Tab + 'RsHeaderSrc.Update'

PRINT ''


PRINT @Tab + 'Dim RsDetailSrc As ADODB.Recordset'
PRINT @Tab + 'Set RsDetailSrc = MycFunc4Ctrls.GatherGridToRecordset(grd)'




PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' saving..."'



PRINT @Tab + 'Services.' + @TransactName + '_Save E, "", m_IsDataExisting, ' + @PkFieldsMtParameter + ', RsHeaderSrc, RsDetailSrc, m_rsDeleted'
PRINT @Tab + 'RaiseIfErr E'

PRINT ''






DECLARE @PkEditable VARCHAR(8000)
set @PkEditable = ''
select
@PkEditable =
@PkEditable + @Tab + 'txt' + c.Name + '.IsEditable = False' + CHAR(13)
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid


PRINT @PkEditable

PRINT @Tab + 'm_IsDataExisting = True'

PRINT @Tab + 'ParentForm.ShowMessage "' + @TransactName + ' saved."'
PRINT @Tab + 'MsgBox "' + @TransactName + ' saved.", vbInformation'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT @Tab + 'ParentForm.ShowMessage ""'

PRINT dbo.CodeGenFe_CatchBlockEnd('DoSave')

PRINT @FinallyBlockBegin


PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''

PRINT '''...Transactions'

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


DEALLOCATE @C


PRINT ''

PRINT ''' Details...'

PRINT ''

PRINT 'Private Sub ComputeAllRows()'

PRINT ''




DECLARE @DetailAggregates AS VARCHAR(8000)
SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + 'Dim Sum' + c.Name + ' As ' + dt.VbTypeDim + CHAR(13)
+ @Tab + 'Sum' + c.Name + ' = 0'
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0
ORDER BY colid



PRINT @DetailAggregates

PRINT ''

PRINT @Tab + 'Dim R As Long'
PRINT @Tab + 'For R = 0 to grd.Rows - 1'

PRINT @Tab + @Tab + 'Dim Bm'

PRINT @Tab + @Tab + 'Bm = grd.AddItemBookmark(R)'

SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + @Tab + 'Sum' + c.Name + ' = Sum' + C.Name + ' + grd.Columns("' + C.Name + '").CellValue(BM)'
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0


PRINT @DetailAggregates

PRINT @Tab + 'Next'



SET @DetailAggregates = ''
SELECT @DetailAggregates =
@DetailAggregates + CHAR(13)
+ @Tab + 'txtSum' + c.Name + '.Value = Sum' + C.Name
FROM sysobjects as o
INNER JOIN syscolumns as c ON c.id = o.id
INNER JOIN systypes AS ST ON c.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
WHERE o.id = object_id(@TableDetailName)
AND c.iscomputed = 0
AND st.xtype IN (127,106,62,56,60,108,59,52,122,48)
AND CHARINDEX('_', c.Name) = 0

PRINT @DetailAggregates

PRINT ''


PRINT ''

PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub ComputeRow()'

PRINT @Tab + 'grd.Columns("IsChanged").Value = True'

PRINT @Tab + ''' example: grd.Columns("Amount").Value = grd.Columns("Qty").Value * grd.Columns("UnitPrice").ValuE'

PRINT @Tab + 'grd.Update'

PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub CreateRsDeleteStructure()'

PRINT @Tab + 'If Not (m_rsDeleted Is Nothing) Then'
PRINT @Tab + @Tab + 'm_rsDeleted.Close'
PRINT @Tab + @Tab + 'Set m_rsDeleted = Nothing'
PRINT @Tab + 'End If'


PRINT @Tab + 'Set m_rsDeleted = New ADODB.Recordset'
PRINT @Tab + 'm_rsDeleted.CursorLocation = adUseClient'
PRINT @Tab + 'm_rsDeleted.Fields.Append "RecId", adGUID'
PRINT @Tab + 'm_rsDeleted.Open'


PRINT 'End Sub'

PRINT ''


PRINT 'Private Sub grd_KeyDown(KeyCode As Integer, Shift As Integer)'

PRINT ''
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If KeyCode = vbKeyReturn Then'
PRINT @Tab + @Tab + 'If grd.Col + 1 >= grd.Columns.Count Then'
PRINT @Tab + @Tab + @Tab + ''' wrap to next row and first column'

PRINT ''

PRINT @Tab + @Tab + @Tab + 'grd.Bookmark = grd.GetBookmark(1)'
PRINT @Tab + @Tab + @Tab + 'Dim C As SSDataWidgets_B_OLEDB.Column'
PRINT @Tab + @Tab + @Tab + 'Dim I As Integer'
PRINT @Tab + @Tab + @Tab + 'For I = 0 To grd.Columns.Count - 1'
PRINT @Tab + @Tab + @Tab + @Tab + 'If grd.Columns(I).Visible And Not grd.Columns(I).Locked Then'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'grd.Col = I'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'Exit For'
PRINT @Tab + @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'Next'

PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + '''go to next column'
PRINT @Tab + @Tab + @Tab + 'grd.Col = grd.Col + 1'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'

PRINT ''


PRINT 'Private Sub grd_KeyUp(KeyCode As Integer, Shift As Integer)'


PRINT @Tab + 'If KeyCode = vbKeyInsert Or KeyCode = vbKeyF7 Then'

PRINT @Tab + @Tab + 'grd.Update'
PRINT @Tab + @Tab + 'grd.AddItem NewGuid'
PRINT @Tab + @Tab + 'grd.MoveLast'


DECLARE @Lookups VARCHAR(8000)

set @Lookups = ''
select
TOP 1 @Lookups = 'txt' + c.name + '.DoClickButton grd'
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableDetailName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableDetailName, i.indid, 1) or
c.name = index_col (@TableDetailName, i.indid, 2) or
c.name = index_col (@TableDetailName, i.indid, 3) or
c.name = index_col (@TableDetailName, i.indid, 4) or
c.name = index_col (@TableDetailName, i.indid, 5) or
c.name = index_col (@TableDetailName, i.indid, 6) or
c.name = index_col (@TableDetailName, i.indid, 7) or
c.name = index_col (@TableDetailName, i.indid, 8) or
c.name = index_col (@TableDetailName, i.indid, 9) or
c.name = index_col (@TableDetailName, i.indid, 10) or
c.name = index_col (@TableDetailName, i.indid, 11) or
c.name = index_col (@TableDetailName, i.indid, 12) or
c.name = index_col (@TableDetailName, i.indid, 13) or
c.name = index_col (@TableDetailName, i.indid, 14) or
c.name = index_col (@TableDetailName, i.indid, 15) or
c.name = index_col (@TableDetailName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id


AND C.Name NOT IN
(

select
C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
) --and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)

order by c.colid



PRINT @Tab + @Tab + @Lookups

PRINT @Tab + 'ElseIf KeyCode = vbKeyDelete And Shift = vbAltMask Then'

PRINT @Tab + @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + @Tab + 'Dim PrevCol As Integer'
PRINT @Tab + @Tab + 'PrevCol = grd.Col'
PRINT @Tab + @Tab + '''...grdEnhancement'


PRINT ''

PRINT @Tab + @Tab + 'Dim BM'
PRINT @Tab + @Tab + 'If grd.SelBookmarks.Count > 0 Then'
PRINT @Tab + @Tab + @Tab + 'For Each BM In grd.SelBookmarks'
PRINT @Tab + @Tab + @Tab + @Tab + 'If grd.Columns("RecID").CellValue(BM) <> "" Then'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Fields("RecId").Value = grd.Columns("RecID").CellValue(BM)'
PRINT @Tab + @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Update'
PRINT @Tab + @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'Next BM'

PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'If grd.Columns("RecID").CellValue(BM) <> "" Then'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Fields("RecId").Value = grd.Columns("RecID").CellValue(grd.Bookmark)'
PRINT @Tab + @Tab + @Tab + @Tab + 'm_rsDeleted.Update'
PRINT @Tab + @Tab + @Tab + 'End If'
PRINT @Tab + @Tab + @Tab + 'grd.SelBookmarks.Add grd.AddItemRowIndex(grd.Bookmark)'
PRINT @Tab + @Tab + 'End If'

PRINT @Tab + @Tab + 'grd.DeleteSelected'

PRINT ''

PRINT @Tab + @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + @Tab + 'grd.Col = PrevCol'
PRINT @Tab + @Tab + '''...grdEnhancement'

PRINT @Tab + 'End If'

PRINT ''

PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If KeyCode = vbKeyReturn Then'
PRINT @Tab + @Tab + 'grd.Update'
PRINT @Tab + 'End If'
PRINT @Tab + '''...grdEnhancement'



PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub grd_RowColChange(ByVal LastRow As Variant, ByVal LastCol As Integer)'
PRINT ''
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'If grd.Col < 0 And LastCol >= 0 Then'
PRINT @Tab + @Tab + 'grd.Col = LastCol'
PRINT @Tab + 'End If'
PRINT @Tab + 'grd.ActiveCell.SelLength = Len(grd.ActiveCell.Text)'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub grd_UpdateError(ByVal ColIndex As Integer, Text As String, ErrCode As Integer, ErrString As String, Cancel As Integer)'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'Cancel = 1'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub grd_GotFocus()'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.ForeColor = m_grdGotFocusColor'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'

PRINT ''

PRINT 'Private Sub grd_LostFocus()'
PRINT @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.ForeColor = m_grdGotFocusColor'
PRINT @Tab + '''...grdEnhancement'
PRINT 'End Sub'

PRINT ''


PRINT 'Private Sub grd_AfterDelete(RtnDispErrMsg As Integer)'
PRINT @Tab + 'If grd.AddItemRowIndex(grd.Bookmark) >= grd.Rows Then'
PRINT @Tab + @Tab + 'grd.Bookmark = grd.AddItemBookmark(grd.Rows - 1)'
PRINT @Tab + 'End If'
PRINT 'End Sub'

PRINT ''

PRINT '''grid Lookups...'

-- pick all primary keys found in details, but whose field is not in header
SET @C = CURSOR FOR
SELECT C.Name, C.XType, dt.VbType, ReferencedTableName = rt.name, c.length
FROM syscolumns AS C
INNER JOIN systypes AS ST ON C.xtype = st.xtype
INNER JOIN CodeGen_DataTypes AS dt ON dt.sqltype = st.name
INNER JOIN sysforeignkeys AS sfk ON sfk.fkeyid = c.id AND sfk.fkey = c.colid
INNER JOIN sysobjects AS rt ON rt.id = sfk.rkeyid
INNER JOIN sysindexes AS i ON i.id = c.id
WHERE c.id = object_id(@TableDetailName)
AND C.iscomputed = 0
AND CHARINDEX('_', c.Name) = 0

AND C.Name NOT IN (SELECT GeographicCode FROM CodeGen_Geographic)

--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id


AND C.Name NOT IN
(

select
C.Name
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
) --and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id


)

ORDER BY c.colid

OPEN @C

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
WHILE @@FETCH_STATUS = 0 BEGIN


-- PRINT @C_ReferencedTableName + ':' + @C_Name

PRINT 'Private Sub txt' + @C_Name + '_Dropdown(UseDefault As Boolean, RS As ADODB.Recordset, RecordsetPositioner As String)'

PRINT @TryBlockBegin
PRINT @Tab + 'Dim E As String'
PRINT @Tab + 'Set RS = Services.' + @C_ReferencedTableName + '_Lookup(E, "")'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_Dropdown')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'

PRINT ''



PRINT 'Private Sub txt' + @C_Name + '_GotoRecord(RS As ADODB.Recordset)'

PRINT @TryBlockBegin

PRINT @Tab + 'grd.Columns("' + @C_Name + '").Value = RS.Fields("' + @C_Name + '").Value'
PRINT @Tab + 'grd.Update'

PRINT ''

print @Tab + '''grdEnhancement... don''t delete this comment'
PRINT @Tab + 'grd.Col = grd.Columns("' + @C_Name + '").Position + 1'
PRINT @Tab + '''...grdEnhancement'


PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenFe_CatchBlockEnd('txt' + @C_Name + '_GotoRecord')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Sub'


PRINT ''

FETCH NEXT FROM @C INTO @C_Name, @C_XType, @C_VbType, @C_ReferencedTableName, @C_length
END



CLOSE @C

DEALLOCATE @C


PRINT '''...grid Lookups'


PRINT ''

PRINT '''...Details'










GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
























CREATE PROCEDURE CodeGenMt_OneTableNoAutoId
@TransactName VARCHAR(100),
@TableName VARCHAR(100)
AS


IF object_id(@TableName) IS NULL BEGIN
PRINT 'Table ' + @TableName + ' does not exists'
RETURN
END




PRINT '''' + @TableName + ' add/edit/delete...'

PRINT ''

DECLARE @Tab VARCHAR(4)

SET @Tab = SPACE(4)



DECLARE @TryBlockBegin VARCHAR(8000)

SET @TryBlockBegin = @Tab + '''Try{' + CHAR(13) + @Tab + 'On Error GoTo goCatchError' + CHAR(13)

DECLARE @TryBlockEnd VARCHAR(8000)

SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)


DECLARE @CatchBlockBegin VARCHAR(8000)

SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)

+ @Tab + 'Screen.MousePointer = vbDefault'






DECLARE @FinallyBlockBegin VARCHAR(8000)

SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)

DECLARE @FinallyBlockEnd VARCHAR(8000)

SET @FinallyBlockEnd = @Tab + '''}'



DECLARE @PkFields VARCHAR(8000)



SET @PkFields = NULL
select
@PkFields = coalesce(
@PkFields + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + C.Name + ' As String' )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




declare @PkCondition varchar(8000)

set @PkCondition = NULL


select
@PkCondition = coalesce(
@PkCondition + ' AND ' + c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"'
,c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid





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

DECLARE @S VARCHAR(8000)


SET @S =
'Public Sub ' + @TransactName + '_Save(ByRef ReturnError As String, '
+ 'ByVal AccessKey As String, '
+ 'ByVal IsExisting As Boolean, '


SET @S = @S + @PkFields + ', ByVal RsSrc AS ADODB.Recordset)'


PRINT @S





/*

' Ver 1
If IsOldRecord Then
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
Else
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE 1=0", adOpenStatic, adLockOptimistic)
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If



'Ver 2
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)

If IsOldRecord Then
If RsItem.RecordCount = 0 Then
Err.Raise 777,"", "Record already deleted by other user before you have even saved this record"
End If
Else
If RsItem.RecordCount > 0 Then
err.raise 777, "", "Item code already exist, please change itemcode"
Else
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
End If




*/





PRINT @TryBlockBegin


PRINT @Tab + 'm_Database.BeginTrans'
PRINT ''
PRINT @Tab + 'Dim RsDst AS ADODB.Recordset'
PRINT ''


DECLARE @FieldComma VARCHAR(8000)

SET @FieldComma = NULL
SELECT @FieldComma = COALESCE(@FieldComma + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
ORDER BY colid


SET @s = @Tab + 'Set RsDst = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableName + ' WHERE ' + @PkCondition + ', adOpenStatic, adLockOptimistic)'


print @s


print ''

PRINT @Tab + 'If IsExisting Then'
PRINT @Tab + @Tab + 'If RsDst.RecordCount = 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TransactName + ' already deleted by other user before you have even saved"'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If RsDst.RecordCount > 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TransactName + ' already exist"'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'RsDst.AddNew'

SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + 'RsDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + 'RsDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid

PRINT @S


PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'


PRINT ''


DECLARE @c CURSOR

SET @C = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
ORDER BY colid

OPEN @C


DECLARE @C_Name AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@tablename)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@tablename, i.indid, c1.colid)
and (c.name = index_col (@tablename, i.indid, 1) or
c.name = index_col (@tablename, i.indid, 2) or
c.name = index_col (@tablename, i.indid, 3) or
c.name = index_col (@tablename, i.indid, 4) or
c.name = index_col (@tablename, i.indid, 5) or
c.name = index_col (@tablename, i.indid, 6) or
c.name = index_col (@tablename, i.indid, 7) or
c.name = index_col (@tablename, i.indid, 8) or
c.name = index_col (@tablename, i.indid, 9) or
c.name = index_col (@tablename, i.indid, 10) or
c.name = index_col (@tablename, i.indid, 11) or
c.name = index_col (@tablename, i.indid, 12) or
c.name = index_col (@tablename, i.indid, 13) or
c.name = index_col (@tablename, i.indid, 14) or
c.name = index_col (@tablename, i.indid, 15) or
c.name = index_col (@tablename, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinue

END






SET @S = @C_Name

PRINT @Tab + 'RsDst.Fields("' + @S + '").Value = RsSrc.Fields("' + @S + '").Value'


goContinue:
FETCH NEXT FROM @C INTO @C_Name
END


CLOSE @C

DEALLOCATE @C

PRINT @Tab + 'RsDst.Update'


PRINT ''

PRINT @Tab + 'm_Database.CommitTrans'

PRINT @TryBlockEnd


PRINT @CatchBlockBegin



PRINT @Tab + 'm_Database.RollbackTrans'




PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Save')


PRINT @FinallyBlockBegin




PRINT @FinallyBlockEnd

PRINT 'End Sub'


PRINT ''




-------------------------------
PRINT 'Public Function ' + @TransactName + '_IsExisting(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ') As Boolean'


PRINT @TryBlockBegin

SET @S = @Tab + @TransactName + '_IsExisting = m_Database.NewRsOpen("SELECT COUNT(*) As Cnt FROM ' + @TableName + ' WHERE ' + @PkCondition + ').Fields("Cnt").Value = 1'
PRINT @S


PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_IsExisting')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Function'


--------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TransactName + '_Open(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ', ByRef RsReturn As ADODB.Recordset)'


PRINT @TryBlockBegin

PRINT ''

SET @s = @Tab + 'Set RsReturn = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableName + ' WHERE ' + @PkCondition + ')'

print @s


PRINT @TryBlockEnd

PRINT @CatchBlockBegin


PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Open')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Sub'

-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TransactName + '_Browse(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'

PRINT @TryBlockBegin


SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
AND colid BETWEEN 1 AND 3
ORDER BY colid


SET @s = @Tab + 'Set ' + @TransactName + '_Browse = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableName + '")'


PRINT ''

print @s


PRINT ''

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Browse')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Function'


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





-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TransactName + '_Lookup(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'

PRINT @TryBlockBegin


SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableName) AND
iscomputed = 0
AND
(
PATINDEX('%_QTY', Name) = 0
AND PATINDEX('%_COST', Name) = 0
AND PATINDEX('%_PRICE', Name) = 0)
AND colid BETWEEN 1 AND 3
ORDER BY colid


SET @s = @Tab + 'Set ' + @TransactName + '_Lookup = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableName + '")'


PRINT ''

print @s


PRINT ''

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Lookup')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Function'


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



PRINT ''

PRINT 'Public Sub ' + @TransactName + '_Delete(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ')'

PRINT @TryBlockBegin

PRINT @Tab + 'm_Database.BeginTrans'
print ''

SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableName + ' WHERE ' + @PkCondition + ')'

PRINT @s

PRINT @Tab + 'm_Database.CommitTrans'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT @Tab + 'm_Database.RollbackTrans'

PRINT dbo.CodeGenMt_CatchBlockEnd (@TransactName + '_Delete')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd




PRINT 'End Sub'

PRINT ''

PRINT '''...' + @TransactName + ' add/edit/delete'













GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



























CREATE PROCEDURE CodeGenMt_TwoTableNoAutoId
@TableTransactName VARCHAR(100),
@TableHeaderName VARCHAR(100),
@TableDetailName VARCHAR(100)
AS


IF object_id(@TableHeaderName) IS NULL BEGIN
PRINT 'Table ' + @TableHeaderName + ' does not exists'
RETURN
END


IF object_id(@TableDetailName) IS NULL BEGIN
PRINT 'Table ' + @TableDetailName + ' does not exists'
RETURN
END



PRINT '''' + @TableTransactName + ' add/edit/delete...'

PRINT ''

DECLARE @Tab VARCHAR(4)

SET @Tab = SPACE(4)



DECLARE @TryBlockBegin VARCHAR(8000)

SET @TryBlockBegin = @Tab + '''Try{' + CHAR(13) + @Tab + 'On Error GoTo goCatchError' + CHAR(13)

DECLARE @TryBlockEnd VARCHAR(8000)

SET @TryBlockEnd = CHAR(13) + @Tab + 'GoTo goFinally' + CHAR(13) + @Tab + '''}' + CHAR(13)


DECLARE @CatchBlockBegin VARCHAR(8000)

SET @CatchBlockBegin= @Tab + '''CatchError{' + CHAR(13) + 'goCatchError:' + CHAR(13) + CHAR(13) + char(13)

+ @Tab + 'Screen.MousePointer = vbDefault'






DECLARE @FinallyBlockBegin VARCHAR(8000)

SET @FinallyBlockBegin = @Tab + '''Finally{' + CHAR(13) + 'goFinally:' + CHAR(13) + CHAR(13)

DECLARE @FinallyBlockEnd VARCHAR(8000)

SET @FinallyBlockEnd = @Tab + '''}'



DECLARE @PkFields VARCHAR(8000)



SET @PkFields = NULL
select
@PkFields = coalesce(
@PkFields + ', ByVal ' + c.Name + ' As String'
,'ByVal ' + C.Name + ' As String' )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid




declare @PkCondition varchar(8000)

set @PkCondition = NULL


select
@PkCondition = coalesce(
@PkCondition + ' AND ' + c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"'
,c.Name + ' = ''" & Enquote(' + c.Name + ') & "''"')
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid





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

DECLARE @S VARCHAR(8000)


SET @S =
'Public Sub ' + @TableTransactName + '_Save(ByRef ReturnError As String, '
+ 'ByVal AccessKey As String, '
+ 'ByVal IsExisting As Boolean, '

SET @S = @S + @PkFields
+ ', ByVal RsHeaderSrc AS ADODB.Recordset ' +
+ ', ByVal RsDetailSrc As ADODB.Recordset ' +
+ ', ByVal RsDetailDel As ADODB.Recordset)'


PRINT @S





/*

' Ver 1
If IsOldRecord Then
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)
Else
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE 1=0", adOpenStatic, adLockOptimistic)
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If



'Ver 2
Set RsItem = m_Database.NewRsOpen("SELECT * FROM Item WHERE ItemCode='" & Enquote(Rs.Fields("ItemCode").Value) & "'", adOpenStatic, adLockOptimistic)

If IsOldRecord Then
If RsItem.RecordCount = 0 Then
Err.Raise 777,"", "Record already deleted by other user before you have even saved this record"
End If
Else
If RsItem.RecordCount > 0 Then
err.raise 777, "", "Item code already exist, please change itemcode"
Else
RsItem.AddNew
RsItem.Fields("ItemCode").Value = Rs.Fields("ItemCode").Value
End If
End If




*/





PRINT @TryBlockBegin


PRINT @Tab + 'm_Database.BeginTrans'
PRINT ''
PRINT @Tab + 'Dim RsHeaderDst AS ADODB.Recordset'
PRINT ''


DECLARE @FieldComma VARCHAR(8000)

SET @FieldComma = NULL
SELECT @FieldComma = COALESCE(@FieldComma + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid




DECLARE @FieldCommaDetail VARCHAR(8000)

SET @FieldCommaDetail = NULL
SELECT @FieldCommaDetail = COALESCE(@FieldCommaDetail + ',' + Name, Name) FROM syscolumns as detailcol WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)

AND NOT EXISTS
(
select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and c.name = detailcol.name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id

)
ORDER BY colid




SET @s = @Tab + 'Set RsHeaderDst = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ', adOpenStatic, adLockOptimistic)'


print @s


print ''

PRINT @Tab + 'If IsExisting Then'
PRINT @Tab + @Tab + 'If RsHeaderDst.RecordCount = 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TableTransactName + ' already deleted by other user before you have even saved"'
PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'Else'
PRINT @Tab + @Tab + 'If RsHeaderDst.RecordCount > 0 Then'
PRINT @Tab + @Tab + @Tab + 'Err.Raise 777, "", "' + @TableTransactName + ' already exist"'
PRINT @Tab + @Tab + 'Else'
PRINT @Tab + @Tab + @Tab + 'RsHeaderDst.AddNew'

SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + 'RsHeaderDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + 'RsHeaderDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid

PRINT @S


PRINT @Tab + @Tab + 'End If'
PRINT @Tab + 'End If'


PRINT ''


DECLARE @c CURSOR

SET @C = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid

OPEN @C


DECLARE @C_Name AS VARCHAR(100)
FETCH NEXT FROM @C INTO @C_Name
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.NAME = @C_name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN

GOTO goContinue

END






SET @S = @C_Name

PRINT @Tab + 'RsHeaderDst.Fields("' + @S + '").Value = RsHeaderSrc.Fields("' + @S + '").Value'


goContinue:
FETCH NEXT FROM @C INTO @C_Name
END


CLOSE @C

DEALLOCATE @C

PRINT @Tab + 'RsHeaderDst.Update'



--------------
-- detail here...



PRINT ''
PRINT ''

PRINT @Tab + 'If RsDetailDel.RecordCount > 0 Then'
PRINT @Tab + @Tab + 'RsDetailDel.MoveFirst'
PRINT @Tab + @Tab + 'Do Until RsDetailDel.EOF'
PRINT @Tab + @Tab + @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableDetailName + ' WHERE RecID = ''" & Enquote(RsDetailDel.Fields("RecID").Value) & "''")'
PRINT @Tab + @Tab + @Tab + 'RsDetailDel.MoveNext'
PRINT @Tab + @Tab + 'Loop'
PRINT @Tab + 'End If'


PRINT ''

PRINT @Tab + 'Dim RsDetailDst AS ADODB.Recordset'

PRINT ''

DECLARE @CDetail CURSOR

SET @CDetail = CURSOR FOR SELECT Name FROM syscolumns WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid

OPEN @CDetail


DECLARE @CD_Name AS VARCHAR(100)

SET @FieldCommaDetail = NULL
SELECT @FieldCommaDetail = COALESCE(@FieldCommaDetail + ',' + Name, Name) FROM syscolumns as detailcol WHERE id = object_id(@TableDetailName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
ORDER BY colid




PRINT @Tab + 'If RsDetailSrc.RecordCount > 0 Then'
PRINT @Tab + @Tab + 'RsDetailSrc.MoveFirst'
PRINT @Tab + @Tab + 'Do Until RsDetailSrc.EOF'
PRINT @Tab + @Tab + @Tab + 'Set RsDetailDst = m_Database.NewRsOpen("SELECT ' + @FieldCommaDetail + ' FROM ' + @TableDetailName + ' WHERE RecID=''" & Enquote(RsDetailSrc.Fields("RecId").Value) & "''", adOpenStatic, adLockOptimistic)'
PRINT @Tab + @Tab + @Tab + 'If RsDetailDst.EOF Then'
PRINT @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.AddNew'
PRINT @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("RecID").Value = RsDetailSrc.Fields("RecID").Value'


SET @S = NULL
select
@S = coalesce(
@S + CHAR(13) + @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + c.Name + '").Value = ' + c.Name
, @Tab + @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + c.Name + '").Value = ' + c.Name )
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id
order by c.colid


PRINT @S


PRINT @Tab + @Tab + @Tab + 'End If'

PRINT ''



FETCH NEXT FROM @CDetail INTO @CD_Name
WHILE @@FETCH_STATUS = 0 BEGIN


IF EXISTS(


select
*
from
sysindexes i, syscolumns c, sysobjects o --, syscolumns c1
where
o.id = object_id(@TableHeaderName)
AND C.name = @CD_Name
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@TableHeaderName, i.indid, c1.colid)
and (c.name = index_col (@TableHeaderName, i.indid, 1) or
c.name = index_col (@TableHeaderName, i.indid, 2) or
c.name = index_col (@TableHeaderName, i.indid, 3) or
c.name = index_col (@TableHeaderName, i.indid, 4) or
c.name = index_col (@TableHeaderName, i.indid, 5) or
c.name = index_col (@TableHeaderName, i.indid, 6) or
c.name = index_col (@TableHeaderName, i.indid, 7) or
c.name = index_col (@TableHeaderName, i.indid, 8) or
c.name = index_col (@TableHeaderName, i.indid, 9) or
c.name = index_col (@TableHeaderName, i.indid, 10) or
c.name = index_col (@TableHeaderName, i.indid, 11) or
c.name = index_col (@TableHeaderName, i.indid, 12) or
c.name = index_col (@TableHeaderName, i.indid, 13) or
c.name = index_col (@TableHeaderName, i.indid, 14) or
c.name = index_col (@TableHeaderName, i.indid, 15) or
c.name = index_col (@TableHeaderName, i.indid, 16)
)
--and c1.colid <= i.keycnt /* create rows from 1 to keycnt */
--and c1.id = @table_id



)
BEGIN
GOTO goContinueSaveDetail
END


IF (@CD_Name = 'RecId') BEGIN
GOTO goContinueSaveDetail
END


PRINT @Tab + @Tab + @Tab + 'RsDetailDst.Fields("' + @CD_Name + '").Value = RsDetailSrc.Fields("' + @CD_Name + '").Value'


goContinueSaveDetail:
FETCH NEXT FROM @CDetail INTO @CD_Name
END



PRINT @Tab + @Tab + @Tab + 'RsDetailDst.Update'
PRINT @Tab + @Tab + @Tab + 'RsDetailSrc.MoveNext'
PRINT @Tab + @Tab + 'Loop'
PRINT @Tab + 'End If'

CLOSE @CDetail


-- ...detail here
---------------------

PRINT ''

PRINT @Tab + 'm_Database.CommitTrans'

PRINT @TryBlockEnd


PRINT @CatchBlockBegin



PRINT @Tab + 'm_Database.RollbackTrans'




PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Save')


PRINT @FinallyBlockBegin




PRINT @FinallyBlockEnd

PRINT 'End Sub'


PRINT ''




-------------------------------
PRINT 'Public Function ' + @TableTransactName + '_IsExisting(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ') As Boolean'


PRINT @TryBlockBegin

SET @S = @Tab + @TableTransactName + '_IsExisting = m_Database.NewRsOpen("SELECT COUNT(*) As Cnt FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ').Fields("Cnt").Value = 1'
PRINT @S


PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_IsExisting')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd

PRINT 'End Function'


--------------------------------------------
PRINT ''
PRINT 'Public Sub ' + @TableTransactName + '_Open(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ', ByRef RsHeaderReturn As ADODB.Recordset, ByRef RsDetailReturn As ADODB.Recordset)'


PRINT @TryBlockBegin

PRINT ''

SET @s = @Tab + 'Set RsHeaderReturn = m_Database.NewRsOpen("SELECT ' + @FieldComma + ' FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ')'

print @s


SET @S = @Tab + 'Set RsDetailReturn = m_Database.NewRsOpen("SELECT ' + @FieldCommaDetail + ' FROM ' + @TableDetailName + ' WHERE ' + @PkCondition + ')'

print @s

PRINT @TryBlockEnd

PRINT @CatchBlockBegin


PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Open')


PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Sub'

-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TableTransactName + '_Browse(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'

PRINT @TryBlockBegin


SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
AND colid BETWEEN 1 AND 3
ORDER BY colid


SET @s = @Tab + 'Set ' + @TableTransactName + '_Browse = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableHeaderName + '")'


PRINT ''

print @s


PRINT ''

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Browse')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Function'


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





-------------------------------------------------------------------------
PRINT ''
PRINT 'Public Function ' + @TableTransactName + '_Lookup(ByRef ReturnError As String, ByVal AccessKey As String) As ADODB.Recordset'

PRINT @TryBlockBegin


SET @S = NULL
SELECT @S = COALESCE(@S + ',' + Name, Name) FROM syscolumns WHERE id = object_id(@TableHeaderName) AND
iscomputed = 0
AND
(
CHARINDEX('_', Name) = 0
)
AND colid BETWEEN 1 AND 3
ORDER BY colid


SET @s = @Tab + 'Set ' + @TableTransactName + '_Lookup = m_Database.NewRsOpen("SELECT ' + @s + ' FROM ' + @TableHeaderName + '")'


PRINT ''

print @s


PRINT ''

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Lookup')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd


PRINT 'End Function'


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



PRINT ''

PRINT 'Public Sub ' + @TableTransactName + '_Delete(ByRef ReturnError As String, ByVal AccessKey As String, ' + @PkFields + ')'

PRINT @TryBlockBegin

PRINT @Tab + 'm_Database.BeginTrans'
print ''

SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableDetailName + ' WHERE ' + @PkCondition + ')'

PRINT @s

SET @s = @Tab + 'Call m_Database.NewRsOpen("DELETE FROM ' + @TableHeaderName + ' WHERE ' + @PkCondition + ')'

PRINT @s

PRINT @Tab + 'm_Database.CommitTrans'

PRINT @TryBlockEnd

PRINT @CatchBlockBegin

PRINT @Tab + 'm_Database.RollbackTrans'

PRINT dbo.CodeGenMt_CatchBlockEnd (@TableTransactName + '_Delete')

PRINT @FinallyBlockBegin

PRINT @FinallyBlockEnd




PRINT 'End Sub'

PRINT ''

PRINT '''...' + @TableHeaderName + ' add/edit/delete'
















GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO