DAO TableDef Field Properties 1

[sabloomer]

Can anyone tell me why the folloing code only works sometimes? I tested it without the Recorset Set loop and it would work sometimes, but not others. In one case I put a MSGBOX infront of and behind the code where I set the description. I ran it and it worked. I changed nothing and I ran it again and got "Runtime 3270 - Property not found" on the line where the dscription is set. Any suggestions?


Public Sub UpdateProperties()

Dim dbs As Database
Dim rst As DAO.Recordset 'Using DAO to Create the needed table
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from tblNewTransaction"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)

Do Until rst.EOF = True
If rst.Fields("Required") = "YES" Then
dbs.TableDefs("Contacts").Fields(rst.Fields("FieldName")).Required = True
End If

dbs.TableDefs("Contacts").Fields(rst.Fields("FieldName")).Properties("Description") = rst.Fields("Description")
rst.MoveNext
Loop

rst.Close
dbs.Close
End Sub

Thank You,

sabloomer

 

[dhookom]

Not all fields have a Description property. If the description hasn't ever been set then it doesn't exist. If you want to set the description property then you may need to create it first.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sabloomer]

dhookom,

That explains why I was getting it to work sometimes, while testing I would hard code an entry into the table properties and then it would work. I would delete the table and start over and it would stop working.

Here is what I have now...

Public Sub UpdateProperties()

Dim dbs As Database
Dim rst As DAO.Recordset 'Using DAO to Create the needed table
Dim sql As String
Dim obj As Object


Set dbs = CurrentDb
sql = "Select * from tblNewTransaction"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)

Do Until rst.EOF = True

Set obj = dbs.TableDefs("Contacts").Fields(rst.Fields("FieldName"))

If IsNull(rst.Fields("Description")) = False Then
obj.Properties.Append obj.CreateProperty("Description", dbText, rst.Fields("Description"))
End If

And it works like a charm. Can you help me take this and apply it to changing the "DisplayControl" property to a combo box? I have tried some different variations, but nothing seems to work. Any suggestions?

Thank You,

sabloomer

 

[dhookom]

Not a good idea. If I understand correctly you are asking me to help you do something that I would never do

http://www.mvps.org/access/lookupfields.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]