Creating Forms - In DataSheet View from another table

[ShaneBrennan]

Hi people

I've been struggling trying to make a database for recording student grades - I know this should be an easy proggie to write - but I've tried to make the program as easy to use as possible - from the user's point of view. Therefore I've had to do a great deal of learning Access VBA - which, lets face it, is not the easiest language in the world!

Anyways after quite a few Email and posts, in various forums I managed to do it!

I've enclosed the skeleton code here, in case it is of use to to anyone else.

I've written the code as a self contained module called CreateDataSheetForm that accepts 3 String parameters:

1) The name of the form you wish to create.
2) The name of the table that's to be used to create the form. This table must contain a field called TheCode - which is used to create a bound TEXTBOX.
3) The name of the table that the new form will use as it's record source.

Again this is just a skeleton of a much larger module I created. It DOES work as it stands, as long as the 1st table, the one used to create the form, exists and contains a field called TheCode.

Here's the code:
------------------------------------------------------------
Sub CreateDataSheetForm(NewName As String, SourceTable As String, RecordSource As String)

' This procedure has been designed to create a form - in DataSheet view from
' information held in a table. The procedure accepts 3 parameters:
'
' 1) NewName: The name of the form to be created. If the form exists then it
' will be deleted first.
' 2) SourceTable: The name of the table supplying the information. The
' Field "TheCode" has the name of the Bound Field.
' 3) recordSource : The name of the table - which this form will display.
'
' As this form is for DataSheet only view, no consideration will be given to the
' layout of the "Form View" is taken into consideration - however with a few tweens
' the procedure could handle that as well.
'
' Step 1) Remove, if it exists, the existing form - identified by NewName
' Step 2) Create a new blank form, and set it to Datasheet view
' Step 3) Open the table - identified by SourceTable and create a textbox for
' each record - Using the data as it's recordSource.

' Variables required to create the form, Controls and rename the form.
Dim frm As Form
Dim ctlText As Control
Dim NewFormsName As String

'Ready SourceTable for Names of Fields
'
Dim db As Database
Dim rst As Recordset
Dim TheBoundField As String

Set db = CurrentDb()
Set rst = db.OpenRecordset(SourceTable)

'---------------------------------------------------------------------------------
' Step 1) - Delete the existing form NewName
'
On Error Resume Next

DoCmd.DeleteObject acForm, NewName

On Error GoTo 0

'---------------------------------------------------------------------------------
' Step 2) - Create a new blank form, Set it's record sourse and set it's defaultview
' to Datasheet.
'
Set frm = CreateForm
NewFormsName = frm.Name
frm.RecordSource = RecordSource
frm.DefaultView = 2

' Create a bound default-size text box in detail section, using the data
' retrieved as the name of the Control Source for the object.

rst.MoveFirst
TheBoundField = rst![TheCode]
rst.MoveNext

' loop through all records in RecordSource - Create a new control for each and
' set it's name and BoundFields
Do Until rst.EOF
Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, , TheBoundField, 0, 0)
ctlText.Name = TheBoundField
TheBoundField = rst![TheCode]
rst.MoveNext
Loop

' Save the form and remame it.

DoCmd.Save acForm, NewFormsName
DoCmd.Close acForm, NewFormsName
DoCmd.Rename NewName, acForm, NewFormsName

End Sub

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

I hope it is of use and thanks to all those who have helped me, esp. DougP, TTTHio and MichaelRed

Shane Brennan - feeling much better now

Shane Brennan
Shane.Brennan@tcat.ac.uk