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 / May 2008

Tip: Looking for answers? Try searching our database.

Variable table/field name in statement

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Stu - 28 May 2008 15:04 GMT
The following statement works fine for me with the  table name TABLE1 and
field name FIELD1 hardcoded:
iType = CurrentDb.TableDefs!TABLE1.Fields!FIELD1.Properties!Type

Is there a way to make the table name and field name variable, something
like the following?  What would go in for the question marks?

sTableName = "MyTable"
sFieldName = "MyField"
itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Douglas J. Steele - 28 May 2008 15:57 GMT
CurrentDb.TableDefs(sTableName).Fields(sFieldName)

Signature

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

> The following statement works fine for me with the  table name TABLE1 and
> field name FIELD1 hardcoded:
[quoted text clipped - 6 lines]
> sFieldName = "MyField"
> itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Stu - 28 May 2008 17:39 GMT
The statement  CurrentDb.TableDefs(sTableName).Fields(sFieldName)  gives me
an "invalid operation" error.  (#3219)

> CurrentDb.TableDefs(sTableName).Fields(sFieldName)
>
[quoted text clipped - 8 lines]
> > sFieldName = "MyField"
> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Douglas J. Steele - 28 May 2008 17:59 GMT
Exactly how did you use that expression? I was simply trying to show you the
syntax.

Going back to your original equation, it would be

itype = CurrentDb.TableDefs(sTableName).Fields(sFieldName).Properties!Type

Signature

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

> The statement  CurrentDb.TableDefs(sTableName).Fields(sFieldName)  gives
> me
[quoted text clipped - 14 lines]
>> > sFieldName = "MyField"
>> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Stu - 29 May 2008 15:23 GMT
Sub test1()
sTableName = "tblAudit"
sFieldName = "audType"
sdesc = CurrentDb.TableDefs(sTableName).Fields(sFieldName)
MsgBox sdesc
End Sub

> Exactly how did you use that expression? I was simply trying to show you the
> syntax.
[quoted text clipped - 21 lines]
> >> > sFieldName = "MyField"
> >> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Douglas J. Steele - 29 May 2008 15:52 GMT
CurrentDb.TableDefs(sTableName).Fields(sFieldName) is a reference to the
Field object. The only way to use it by itself would be:

Dim fldCurr As Field

 Set fldCurr = CurrentDb.TableDefs(sTableName).Fields(sFieldName)

If you want a property of that object, you need to refer to the property.
Assuming you're trying to get the Description property, your code needs to
be:

Sub test1()
Dim sTableName As String
Dim sFieldName As String
Dim sdesc As String

 sTableName = "tblAudit"
 sFieldName = "audType"
 sdesc =
CurrentDb.TableDefs(sTableName).Fields(sFieldName).Properties("Description")
 MsgBox sdesc
End Sub

Note that Description is an odd property: it doesn't actually exist unless a
description has been assigned to the field. If you hadn't actually assigned
a description to the field, you'll get an error 3270 ("Property not found.")
A common way to handle that, then, would be

Sub test1()
On Error GoTo ErrHandler

Dim sTableName As String
Dim sFieldName As String
Dim sdesc As String

 sTableName = "tblAudit"
 sFieldName = "audType"
 sdesc =
CurrentDb.TableDefs(sTableName).Fields(sFieldName).Properties("Description")

ExitHere:
 MsgBox sdesc
 Exit Sub

ErrHandler:
 Select Case Err.Number
   Case 3270
     sdesc = "***No Description Given***"
   Case Else
     MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
Resume ExitHere

End Sub

I'm assuming from your code that you haven't told Access to require that all
variables be declared. To me, that's a huge mistake. Go into the VB Editor
and select Tools | Options from the menu. On the Editor tab, make sure that
the "Require Variable Declaration" check box is selected. That will ensure
that all new modules will have a line "Option Explicit" near the top.
(Unfortunately, you'll have to go into all existing modules and add that
line yourself). While it may seem a pain having to declare each variable, it
can save you hours in the long run trying to track down why your code isn't
doing what it's supposed to when you accidentally mistyped one of the
variable names!

Signature

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

> Sub test1()
> sTableName = "tblAudit"
[quoted text clipped - 32 lines]
>> >> > sFieldName = "MyField"
>> >> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Stu - 29 May 2008 15:58 GMT
Got it.  Case closed.  Thanks

> CurrentDb.TableDefs(sTableName).Fields(sFieldName) is a reference to the
> Field object. The only way to use it by itself would be:
[quoted text clipped - 98 lines]
> >> >> > sFieldName = "MyField"
> >> >> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
Stu - 29 May 2008 15:53 GMT
OOPS, bad cut 'n paste.  It works fine.  Thanks

> Sub test1()
> sTableName = "tblAudit"
[quoted text clipped - 28 lines]
> > >> > sFieldName = "MyField"
> > >> > itype = CurrentDb.TableDefs!???????.Fields!??????.Properties!Type
 
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.