How to Populate the First Record with Field Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have: a table with data

I want: to populate the first record with the field names.

Any suggestions?
 
This may be possible in limited circurmstances, but there's no general
solution.

First, all the fields must be of type Text (and of sufficient length) or
Memo, because field names are strings. If that is the case, there's no
difficulty using recordset operations along the lines of
rs.AddNew
For Each F in rs.Fields
F.Value = F.Name
Next
rsUpdate
to create a record in which each field contains its own name.

But the only way to make this the "first" record is to use a query that
sorts the records into an order in which this record comes before any
others - which means sorting it on a field whose name sorts before any
possible contents. So you'd have to work within certain restrictions on
the field names and/or the data the fields may contain.

One work-round would be to add a field to the table and exempt it from
the rule that its name must be contained in the first record. If you
make this a number field, put 0 in the first record and 1 in all the
others, you can sort on this field to get the record with names first.

Another is to use a similar trick in a union query. E.g. this, which
assumes your table has two fields named Field1 and Field2:

SELECT TOP 1 "Field1" AS Field1, "Field2" As Field2, 0 As Dummy
FROM MyTable
UNION
SELECT Field1, Field2, 1 As Dummy
FROM MyTable
ORDER BY Dummy
;
 
One more thing. It may not be possible in a text field if the text field is a
Foriegn Key field (or even the Primary Key field or one that is indexed unique)
if the field name could be a value that is assigned to a field in the normal
course of data entry.
 
Worse: if the field is a foreign key whose values are constrained by RI,
it won't be possible unless the field name *is* a value that has already
been assigned to the corresponding field in the related table. Not to
mention validation rules...

I should have said "there's no difficulty using recordset operations
PROVIDED the table doesn't include any of the constraints that would be
used in a well-designed relational database..." <g>
 
Back
Top