Perhaps the best explanation is to show you some SQL statements that I used to create a custom table attached to the payroll employee screen (a sample is sometimes worth a thousand words).
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xEmployeeExt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Dropping table: xEmployeeExt'
DROP TABLE [dbo].[xEmployeeExt]
END
Go
CREATE TABLE xEmployeeExt (
EmpId CHARACTER (10) NULL DEFAULT (' '),
CellNbr CHARACTER (20) NULL DEFAULT (' '),
Email CHARACTER (50) NULL DEFAULT (' '),
EmergencyContact CHARACTER (50) NULL DEFAULT (' '),
ContactNbr CHARACTER (30) NULL DEFAULT (' '),
TerminationReason CHARACTER (50) NULL DEFAULT (' '),
Rehire CHARACTER (30) NULL DEFAULT (' '),
tstamp timestamp NOT NULL
)
GO
IF EXISTS (SELECT * FROM sysindexes WHERE name = 'xEmployeeExt0')
BEGIN
PRINT 'Dropping index: xEmployeeExt0'
DROP INDEX xEmployeeExt.xEmployeeExt0
END
Go
PRINT 'Creating index xEmployeeExt0'
Go
Create Index xEmployeeExt0 on xEmployeeExt
([EmpID])
Go
PRINT 'Index: xEmployeeExt0 created successfully'
Go
You will see that I created an index on the xEmployeeExt table. The 0 index must refer to a unique field or fields that uniquely identifies one record in the table.
As for you compile error on the form_load event, without the text of the code you added, I cannot tell what is causing the error. However, here is the code I added to the payroll employee screen for the new custom table xEmployeeExt.
Private Sub cEmpID_Chk(ChkStrg As String, retval As Integer)
Dim GetxEmployeeExt As Integer
Dim SqlStr As String
If Trim(ChkStrg) <> "" Then
SqlStr = "SELECT * FROM xEmployeeExt WHERE EmpID = " + SParm(Trim(ChkStrg))
GetxEmployeeExt = SqlFetch1(Csr_xEmployeeExt, SqlStr, bxEmployeeExt, LenB(bxEmployeeExt))
If GetxEmployeeExt = NOTFOUND Then
FoundXtra = False
bxEmployeeExt = nxEmployeeExt
Else
FoundExt = True
End If
End If
Call DispFields("form1", "")
End Sub
Private Sub Update1_OnDelete(level As Integer, retval As Integer)
Dim Empid As String * 10
Dim SqlStr As String
If level = LEVEL0 Then
Empid = GetObjectValue("cempid")
SqlStr = "Delete from xEmployeeExt where Empid = " & SParm(Empid)
Call TranBeg(True)
Call sql(Csr_xEmployeeExt, SqlStr)
Call TranEnd
Call SqlFree(Csr_xEmployeeExt)
End If
End Sub
Private Sub Update1_OnUpdate(level As Integer, insertflg As Integer, retval As Integer)
If level = Finished Then
Call TranBeg(True)
If FoundExt Then 'update existing
Call SUpdate1(Csr_xEmployeeExt, "xEmployeeExt", bxEmployeeExt, LenB(bxEmployeeExt))
Else
bxEmployeeExt.Empid = GetObjectValue("cEmpId")
Call SInsert1(Csr_xEmployeeExt, "xEmployeeExt", bxEmployeeExt, LenB(bxEmployeeExt))
End If
Call TranEnd
End If
End Sub
Private Sub Form1_Display()
End Sub
'$include: "xEmployeeExt.dh"
Private Sub Form1_Load()
Call VBA_SetAddr("bxEmployeeExt", bxEmployeeExt, nxEmployeeExt, LenB(bxEmployeeExt))
Call SqlCursorEx(Csr_xEmployeeExt, NOLEVEL + SqlUpdate, "Csr_xEmployeeExt", "xEmployeeExt", "xEmployeeExt")
End Sub
