If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Use VBA to set Format Property of a Field
I need to create a field using VBA which will display with 2 decimal places.
I can do it manually using the type "Currency" and the format property "Fixed". I can create the field with the currency type using the VBA CreateField function below. I can't seem to figure out the code needed to set the format property to "Fixed". I understand that the property needs to be created, but I'm missing something in the syntax. Any suggestions would be appreciated. ' dbT and tdf have been declared Dim fld As Field Dim prop As Property Set dbT = DBEngine.Workspaces(0).OpenDatabase(strDataPath) Set tdf = dbT.TableDefs("3 Photo Log") ' Create a New Field Set fld = tdf.CreateField("Issue Order", dbCurrency) ' Add the Format Property Set prop = fld.CreateProperty("Format", dbCurrency, "Fixed") fld.Properties.Append prop ' Append the field to the table tdf.Fields.Append fld -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
#2
|
|||
|
|||
Use VBA to set Format Property of a Field
The Format property is a String, not a Currency.
Try: Set prop = fld.CreateProperty("Format", dbText, "Fixed") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "rwboyden via AccessMonster.com" u12496@uwe wrote in message news:8ee7d9cbca91b@uwe... I need to create a field using VBA which will display with 2 decimal places. I can do it manually using the type "Currency" and the format property "Fixed". I can create the field with the currency type using the VBA CreateField function below. I can't seem to figure out the code needed to set the format property to "Fixed". I understand that the property needs to be created, but I'm missing something in the syntax. Any suggestions would be appreciated. ' dbT and tdf have been declared Dim fld As Field Dim prop As Property Set dbT = DBEngine.Workspaces(0).OpenDatabase(strDataPath) Set tdf = dbT.TableDefs("3 Photo Log") ' Create a New Field Set fld = tdf.CreateField("Issue Order", dbCurrency) ' Add the Format Property Set prop = fld.CreateProperty("Format", dbCurrency, "Fixed") fld.Properties.Append prop ' Append the field to the table tdf.Fields.Append fld -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
#3
|
|||
|
|||
Use VBA to set Format Property of a Field
Douglas J. Steele wrote:
The Format property is a String, not a Currency. Try: Set prop = fld.CreateProperty("Format", dbText, "Fixed") I need to create a field using VBA which will display with 2 decimal places. [quoted text clipped - 24 lines] ' Append the field to the table tdf.Fields.Append fld Very logical ... tried this and got an "invalid operation" error on the "Append.fld" line. The Format property is "read only" ... is there a way around this or is there a set of VBA-assignable specs which would give me the the 2 decimal place format? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
#4
|
|||
|
|||
Use VBA to set Format Property of a Field
If you've got references set to both ADO and DAO, try being explicit in your
declaration" Dim fld As DAO.Field Dim prop As DAO.Property Field and Property are objects in both models, and since you're using DAO in your code, it's critical that they be instantiated as the correct type. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "rwboyden via AccessMonster.com" u12496@uwe wrote in message news:8eeaaa0d07cad@uwe... Douglas J. Steele wrote: The Format property is a String, not a Currency. Try: Set prop = fld.CreateProperty("Format", dbText, "Fixed") I need to create a field using VBA which will display with 2 decimal places. [quoted text clipped - 24 lines] ' Append the field to the table tdf.Fields.Append fld Very logical ... tried this and got an "invalid operation" error on the "Append.fld" line. The Format property is "read only" ... is there a way around this or is there a set of VBA-assignable specs which would give me the the 2 decimal place format? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
#5
|
|||
|
|||
Use VBA to set Format Property of a Field
Douglas J. Steele wrote:
If you've got references set to both ADO and DAO, try being explicit in your declaration" Dim fld As DAO.Field Dim prop As DAO.Property Field and Property are objects in both models, and since you're using DAO in your code, it's critical that they be instantiated as the correct type. The Format property is a String, not a Currency. [quoted text clipped - 13 lines] there a set of VBA-assignable specs which would give me the the 2 decimal place format? Douglas ... thanks for the help! I've changed the instantiation as recommended to include the DAO spec but still get the "Invalid Operation" error ... on the Append prop line (sorry ... not on the Append fld line). Any further thoughts? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
#6
|
|||
|
|||
Use VBA to set Format Property of a Field
rwboyden wrote:
If you've got references set to both ADO and DAO, try being explicit in your declaration" [quoted text clipped - 10 lines] there a set of VBA-assignable specs which would give me the the 2 decimal place format? Douglas ... thanks for the help! I've changed the instantiation as recommended to include the DAO spec but still get the "Invalid Operation" error ... on the Append prop line (sorry ... not on the Append fld line). Any further thoughts? 30 Minutes Later ... I discovered that CreateField("Issue Order", dbSingle) creates a Number field with a Single field size. This allows me to work with values with 2 decimal places. The only restriction is that a whole number (2. 00) will not show the trailing 0's, but that's not a problem in this appication. Many thanks for the help and all the best wishes for the Holiday Season. Bob Boyden -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200812/1 |
Thread Tools | |
Display Modes | |
|
|