Set description on table fields in code

vendredi 24 avril 2015

I am unable to set a table's Description property in code UNLESS it already exists! I have taken effort to build an an extremely easy-to-follow demonstration.

Create MyTable
Add 3 text fields
fld_1
fld_2
fld_3
Create one record with anything at all in it, or 1 record with nothing at all.

Create tblDescs
Add 2 text fields
MyField
MyDesc

Create 3 records with 2 values each with the 6 values below.
MyField MyDesc
fld_1 desc_1
fld_2 desc_2
fld_3 desc_3

Run this:
Code:

Sub AddFieldDescs_MyTable()
    Dim db As Database
    Dim rs As Recordset, rsDesc As Recordset
    Dim iRecordCount As Long
   
    iRecordCount = 0
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MyTable")
    Set rsDesc = db.OpenRecordset("Descs")
  On Error GoTo err_DoneByeBye
    Do While True  'i.e., forever, until Exit Do or error; don't want to miss last record when EOF was hit
        rsDesc.Index = "MyField"
        rsDesc.Seek "=", rs.Fields(iRecordCount).Name
        rs.Fields(rsDesc("MyField")).Properties("Description") = rsDesc("MyDesc")
        'rs.Update
        iRecordCount = iRecordCount + 1
        If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far": DoEvents
    Loop
    Debug.Print iRecordCount & " records COMPLETED - TODOS - Hasta"
err_DoneByeBye: 'Don't go away mad;
    rsDesc.Close
    Set rsDesc = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

It fails to add a description. Now open the tblDescs in Access in design view, and just type a space with the space bar in the description area by the field. Arrow down to seal it in, close window, save. Run the code again - and it WORKS.

1. Why does the code work if there's an existing description, else not?
2. If that's just the fact of life, how can I "seed" descriptions? I seem to be in an infinite loop. I can't set descriptions unless there already are descriptions!!
Set description on table fields in code

0 commentaires:

Enregistrer un commentaire

Labels