Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / January 2006

Tip: Looking for answers? Try searching our database.

How to set table descriptions in vba

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ben - 10 Jan 2006 15:57 GMT
I have created a table that has 2 columns (name, description) which list
table names and descriptions.  What i would like to do, is after certain
tables are destroyed and recreated, I would like to add the stored
description to all tables.  

The problem im having is that if a table does not have a description
entered, the code:
tbl.Properties("Description") = IIf(IsNull(rst![Description]), "",
rst![Description]) fails saying tbl.properties("Description") cannot be
found.  however, this code works for tables that do have a description set.

the code that does the looping is:
For Each tbl In db.TableDefs
       rst.Seek "=", tbl.Name
       If Not rst.NoMatch Then tbl.Properties("Description") =
IIf(IsNull(rst![Description]), "", rst![Description])
   Next tbl

how else could i achieve my goal?

Thanks for any help!
ben
Allen Browne - 10 Jan 2006 16:04 GMT
Use error handling to trap the error (3270 from memory).

Example of error recovery reading the Description property:
   http://allenbrowne.com/func-06.html

BTW, Name and Description would not be idea field names. Almost everything
in Access has a Name property, so there is a very good chance if you put
this field into a form that Access will get confused between the Name of the
form and the value in the Name control on the form. (Description is less
problematic, but many things do have a Description property.)

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have created a table that has 2 columns (name, description) which list
> table names and descriptions.  What i would like to do, is after certain
[quoted text clipped - 19 lines]
> Thanks for any help!
> ben
Ben - 10 Jan 2006 16:49 GMT
Thanks, but i guess i wasnt clear on my problem.  

I dont need to trap the error, I still want to set the value of the table
descriptions, but its giving me an error saying the property isnt available
for that table.  Is there a way of ensuring i set the property whether or not
there is a description currently.

Thanks,
Ben

> Use error handling to trap the error (3270 from memory).
>
[quoted text clipped - 30 lines]
> > Thanks for any help!
> > ben
Allen Browne - 10 Jan 2006 16:58 GMT
If it is not available, then you can CreateProperty.

Here's a function you can use that sets the property if it exists, and
creates and sets it if it does not exist.

Example:
   Call SetPropertyDAO(Currentdb.TableDefs("MyTable").Fields("MyField"), _
       "Description", dbText, "This is my description")

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
   'Purpose:   Set a property for an object, creating if necessary.
   'Arguments: obj = the object whose property should be set.
   '           strPropertyName = the name of the property to set.
   '           intType = the type of property (needed for creating)
   '           varValue = the value to set this property to.
   '           strErrMsg = string to append any error message to.

   If HasProperty(obj, strPropertyName) Then
       obj.Properties(strPropertyName) = varValue
   Else
       obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
   End If
   SetPropertyDAO = True

ExitHandler:
   Exit Function

ErrHandler:
   strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
       " not set to " & varValue & ". Error " & Err.Number & " - " & _
       Err.Description & vbCrLf
   Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
   'Purpose:   Return true if the object has the property.
   Dim varDummy As Variant

   On Error Resume Next
   varDummy = obj.Properties(strPropName)
   HasProperty = (Err.Number = 0)
End Function

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks, but i guess i wasnt clear on my problem.
>
[quoted text clipped - 45 lines]
>> > Thanks for any help!
>> > ben
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.