Is it Possible to Pass a "Text" Parameter to a Method Via a Variable (without Quotes)

[xweyer]

Recently I created some code that used DAO to read table/field information contained in a table I'd constructed for that purpose in order to append a number of new fields to their respective tables elsewhere in the database.

For example the source table for the table/field information works something like this...

[pre]TableNm TableFld FldType FldSize[/pre]
[pre]------- -------- ------- -------[/pre]
[pre]tItem Color dbText 50[/pre]
[pre]tItem Quantity dbLong 4[/pre]

The code then loops though the records in this table and loads them into variables.

[pre]Dim strTblNm As String 'aka TableNm[/pre]
[pre]Dim strFldNm As String 'aka TableFld[/pre]
[pre]Dim strFldType As String 'aka FldType[/pre]
[pre]Dim lngSize As Long 'aka FldSize[/pre]

My original idea was to then feed these values to the statement

tdf.Fields.Append tdf.CreateField(strFldNm, strFldType, lngSize)

but the problem I ran into is that resulting statement became...

tdf.Fields.Append tdf.CreateField("Color", "dbText", 50)

while what I needed was...

tdf.Fields.Append tdf.CreateField("Color", dbText, 50)
(no quotes around dbText)

I was able to work around the problem by changing the FldType to a long integer and substituting the integer value for each DAO Field Type (e.g. dbTexT = 10) and the code now runs pefectly. But my question is if there is any way that I could have gotten the original idea to work (pass dbText to the statement without the quotes) by dimensioning the variable differently or some other means?

 

[MajP]

Just store the value for the constant. Dbtext is 10

http://bytecomb.com/microsoft-access-field-data-type-reference/

 

[MajP]

Sorry did not read the entire post. Maybe try the eval function I have not tried it.

 

[MajP]

Also you could just roll your own function. You can add all kinds of constants to it. If the eval function does not work, I do not know if there is a constant value from string native function.

Public Function ConstantFromString(strConstant as string) as long
  select case StrConstant
    case "dbText", "otherConstant", "moreConstants"
      constantFromString = 10
    case "dbBinary", "otherConst=9"
     constantFromString = 9
    case ......
  end select
end function

tdf.Fields.Append tdf.CreateField(strFldNm, constantFromString(strFldType), lngSize)

 

[strongm]

>I do not know if there is a constant value from string native function

There isn't, but it is pretty easy (although a bit of a cheat ...) to roll a more generic solution than a giant switch statement. Create a new code module (we'll call it "Utility"). Then cut and paste in the following code, and you are done. You can get the value for any public variable (actually any variable that's in scope of the code in the Utility module), not just constants.

[blue]Option Compare Database
Option Explicit

Public Function GetPublicByName(strGlobal As String) As Variant

    With VBE.ActiveVBProject.VBComponents("Utility").CodeModule
        .ReplaceLine .ProcBodyLine("GetPublic", vbext_pk_Proc) + 1, "    GetPublic = " & strGlobal
    End With
    
    GetPublicByName = GetPublic()
End Function

[green]' Do not modify this function by hand unless you understand how this code works[/green]
Private Function GetPublic() As Variant
    GetPublic = dummystartervar
End Function[/blue]