Create a Temp Table to Be Used in Subform

[Mike Thomas]

I am trying to create a temporary table to use in a datasheet subform, the
subform is bound to the table, and would like to do it in one of the methods
of either the parent form or the subform.

The 'open' methods in both forms seem too late. I receive the error that
the table tt_RctItem cannot be found when the form is loading.

To create the table, I am calling a procedure, Creatett_RctItem,

Public Sub Creatett_RctItem()

Dim tbl As TableDef
Dim fld As Field
On Error Resume Next
CurrentDb.TableDefs.Delete "tt_RctItem"
On Error GoTo 0
Set tbl = CurrentDb.CreateTableDef("tt_RctItem")

Set fld = tbl.CreateField("PartKey", dbLong)
fld.OrdinalPosition = 1
tbl.Fields.Append fld

Set fld = tbl.CreateField("RecdQuantity", dbLong)
fld.OrdinalPosition = 2
tbl.Fields.Append fld
CurrentDb.TableDefs.Append tbl
End Sub

Is there a way to call this proc in either the parent form or the subform's
code, and do it early enough that the subform can load, bound to this new
and empty table?


Many thanks
Mike Thomas

 

[Guest]

What is wrong with leaving the form unbound, then "binding" it (setting the
recordsource property) in the form's open procedure?

 

[Mike Thomas]

Many thanks - your suggestion worked well.

Mike Thomas

 

[Guest]

After I thought about this solution, I realized when I have used this in the
past, I would have to set each controls rowsource property manually (at least
after un-binding the form), and in order to help facilitate any changes to
the form, I would make the rowsource binding an automatic process. In
addition to setting the forms datasource in the formopen event, I would
iterate through the controls and set the rowsource according to the control's
"tag" property. The code looks something like this ... (if I remember
correctly, this only works with textboxes - also, you need to use a counting
method instead of for each, because you have to get a reference to the
control)

dim xCount as integer

for xCount = 0 to me.controls.count -1
- if me.control(xCount).type = textbox then
- - me.control(xCount).rowsource = me.control(xCount).tag
- end if
next xCount

The code could possibly be modified to include comboboxes, but I think it
would be too complicated to do it for group controls, but then again anything
is possible.