Creating a new form

[Michael Stevens]

Hi everyone,

I'm wanting to create a new form (datasheet view) using 'CreateForm' code,
however the record source is a variable. To complicate things, every record
source (table) has different field names. The part I can't work out is how
to populate the form from the field list as I dont know the field names. I
was thinking of opening up the record source/table first, then defining each
field name and then using a 'CreateControl' to put a text box in the form.

If it helps, I want every field put in the form.

I was thinking of using something like the code below, but I'm picking up
record data than field names. I'm way out of my depth here and hoping
someone can point me in the right direction.

Thanks, Mike

Set frm = CreateForm
frm.RecordSource = TheTable


Set theDB = CurrentDb()
Set rsER = theDB.OpenRecordset(TheTable)
With rsER
For fldNo = FLD_1ST To .Fields.Count
With .Fields(fldNo)
.FieldName
Set ctlText = CreateControl(frm.Name, acTextBox, , "", fldNo, _
intDataX, intDataY)
End With
Next
End With
DoCmd.Save , "Sub Form"

 

[Larry Linson]

Can you explain why you feel it is necessary to create forms "on the fly"
showing every field in different RecordSources? If you will explain what it
is you are trying to accomplish, it is possible that someone can suggest a
simpler alternative.

However, that said, it is certainly possible to create a Form, and use the
name of a Table or Query as the RecordSource, then enumerate the Fields
collection of that Table or Query and create a control for each. Not
trivial, exactly, and not usually needful, but certainly possible.

Please clarify in the newsgroup, not by e-mail.

Larry Linson
Microsoft Access MVP