Grrrr.. This one is really annoying me! Here's the problem
I have a central data control bound to a contral table within a database (Employees2.mdb and SF2003 as the table). I have another data control that is used to access other tables in the same database. Basically, as soon as you create a new record in the SF2003 table, the CreateNewTable Function is called (shown below) that creates a new table (surprise surprise!) called FirstNameMiddleNameSurname (e.g. JoeSamBloggs). This is all well and good, but when I try adding a record to that table, it says "Item not in this collection". It is, Cos I put it there! Any Ideas why it cant find the fields in the table that i created only moments before?
Public Function EnterLeaveDates(FromDate As Date, FromTime As String, ToDate As Date, ToTime As String, LeaveEntryNumber As Integer)
MainPage.LeaveControl.RecordSource = MainPage.EmployeeControl.Recordset!Firstname & MainPage.EmployeeControl.Recordset!MiddleName & MainPage.EmployeeControl.Recordset!Surname
MainPage.LeaveControl.Database.OpenRecordset MainPage.EmployeeControl.Recordset!Firstname & MainPage.EmployeeControl.Recordset!MiddleName & MainPage.EmployeeControl.Recordset!Surname
MainPage.LeaveControl.Recordset!LeaveFromDate = FromDate 'NB - error occurs here!
MainPage.LeaveControl.Recordset!leavefromtime = FromTime
MainPage.LeaveControl.Recordset!leavetodate = ToDate
MainPage.LeaveControl.Recordset!leavetotime = ToTime
MainPage.LeaveControl.RecordSource = Testtable
Sub CreateNewLeaveTable(TableName As String)
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Employees2.mdb")
' Create a table with three fields and a unique
' index made up of all three fields.
dbs.Execute "CREATE TABLE " & TableName _
& "(LeaveFromDate DATE, LeaveFromTime CHAR, " _
& "LeaveToDate DATE, " & "LeaveToTime CHAR, " _
& "SickFromDate DATE, " & "SickFromTime CHAR, " _
& "SickToDate DATE, " & "SickToTime CHAR, " _
& "Reason Char, " & "DoctorsNote CHAR);"
I would appreciate any suggestions!
Also, is there anyway you can list the fields in a table (i.e. list what it thinks the fields are)