Re: Adding Description to Table in vba

Fixed that, then it failed at

Set prpNew = tdfCurr.CreateDescription("Description", dbText, Description)

Changed it to:
Set prpNew = tdfCurr.CreateProperty("Description", dbText, Description) and
it worked fine.
Thank God for you folks, I had no idea that dbText and its cousins even
existed.
Claudette

Set prpNew = tdfCurr.CreateProperty("Description", db, Description)
"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:uJYFja8WIHA.5716@xxxxxxxxxxxxxxxxxxxxxxx
Sorry, my fault. You don't need the Set keyword there. It should just be

tdfCurr.Properties("Description") = Description


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Claudette Hennessy" <chennessy@xxxxxxxxxxx> wrote in message
news:uSpGKD8WIHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
Thank you Douglas. this helps me understand.

I changed the SQL statement to include a date in the table name..
strYear = Year(Date)
strtablename = "tblSpringUpdate" & strYear

SQL = "SELECT qryDealerMailMerge.ShowID, ...
qryDealerMailMerge.NYS_Tax_ID INTO " & strtablename & " FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

this produces a table named tblSpringUpdate 2008 .

DoCmd.RunSQL SQL
Up to here it works fine.


Call SetTableDescription(strtablename, "Contains Spring Dealer data")

I created a sub with your code (Again thanks)

Sub SetTableDescription(TableName As String, Description As String)
On Error GoTo SetTableDescription

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim prpNew As DAO.Property

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
Set tdfCurr.Properties("Description") = Description !It fails here with
a compile error, invalid use of property.

End_SetTableDescription:
Set prpNew = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

Err_SetTableDescription:
Select Case Err.Number
Case 3270 ' Property Not Found
Set prpNew = tdfCurr.CreateDescription( _
"Description", dbText, Description)
tdfCurr.Properties.Append prpNew
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_SetTableDescription

End Sub

Any ideas?

Claudette

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Claudette Hennessy" <chennessy@xxxxxxxxxxx> wrote in message
news:uOrybkiWIHA.4808@xxxxxxxxxxxxxxxxxxxxxxx
Not sure which forum this belongs in: I am creating a table in order
to pass the contents to Word in a mail merge. I do this because the
data to be merged depends on a selection from a form. The query is:

SQL = "SELECT qryDealerMailMerge.ShowID, qryDealerMailMerge.ShowName,
qryDealerMailMerge.ShowYear, qryDealerMailMerge.Bldg,
qryDealerMailMerge.BoothNum, qryDealerMailMerge.ShopFirstName,
qryDealerMailMerge.ShopName, qryDealerMailMerge.ShopTown,
qryDealerMailMerge.ShopState, qryDealerMailMerge.ShopPhone,
qryDealerMailMerge.DealerFirstName, qryDealerMailMerge.DealerLastName,
qryDealerMailMerge.Address, qryDealerMailMerge.HomeTown,
qryDealerMailMerge.HomeState, qryDealerMailMerge.HomeZip,
qryDealerMailMerge.NYS_Tax_ID INTO tblSpringUpdate FROM
qryDealerMailMerge WHERE
(((qryDealerMailMerge.ShowID)=[cboChooseShowID]))"

I want to (1) create a tablename with a date attached, for instance
"tblSpringUpdate2008" or (2) create a description along with the table
with date information. I know the description info is in

CurrentDb.TableDefs(ObjectName).Properties("Description")

but have not been successful.....
Thank you in advance,
Claudette
PS If I knew how to pass data to Word from a query with a where
clause I guess I wouldn't have to do this?











.