Using VBA to set Column in Access Table to Standard 4

[howardr]

Hello,

I am trying to use VBA to change the Format of an existing table in Access 2003 to Standard and the Decimal Places to 6.

I need to do this from the VBA code, as it is part of the code I need to run (if that makes any sense).

Can someone please help??

Thank you in advance. Your help is greatly appreciated!

 

[TheAceMan1]

How are ya howardr . . . . .

Be more specific about exactly what you want to change . . .

See Ya! . . . . . .

 

[howardr]

Hi AceMan!

Thanks so much for responding! I'm doing great. How are you? I hope I can explain my my question so it makes sense.

I have a table created by a Make-Table Query. When you open the table in Design View, in the General Tab under Field Properties, in the Format line, it is blank for the fields I need to change to "Standard" format. Also, the Decimal field a line or two under the Format line under the Field Properties, is set to Auto. I need to set the Decimal to "6", so I can get a 6 decimal read out to the right of the decimal point. I know how to do this from Design View for the table, but I have no clue how to do that from the VBA code I am writing to automate what was once a very time intensive process.

If you could help me out with this, I would appreciate it greatly.

Thanks a million!

 

[PHV]

Instead of a MakeTable query why not using an Append query to an already well defined (and emptied) table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

howardr . . . . .

Sorry to get back so late (Took Mom out!)

OK . . . here we go:

In a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following code:

[blue]Public Sub SetTblProps(TableName As String, TableFields As String)
   Dim db As DAO.Database, tdf As DAO.TableDef, Fld As Field
   Dim prp As Property, prpNew As Property, Ary, x As Integer
   
   Set db = CurrentDb()
   Set tdf = db.TableDefs("tblNamNum")
   Ary = Split(TableFields, ";")
   
   On Error GoTo MissingProperty
   
   For Each Fld In tdf.Fields
      For x = LBound(Ary) To UBound(Ary)
         If Fld.Name = Ary(x) Then
            Fld.Properties("Format") = "Standard"
            Fld.Properties("DecimalPlaces") = 6
            Exit For
         End If
      Next
   Next
   
   Set tdf = Nothing
   Set db = Nothing
   
   Exit Sub

MissingProperty: [green]'Only Format in this case![/green]
   If Err.Number = 3270 Then
      Set prpNew = Fld.CreateProperty("Format", dbText, "Standard")
      Fld.Properties.Append prpNew
      Set prpNew = Nothing
      Resume Next
   End If
   
End Sub[/blue]

To call the routine:

[blue]Call SetTblProps("[purple][b]TableName[/b][/purple]", "[purple][b]semicolon sepseated fieldlist[/b][/purple]")

Example:

Call SetTblProps("[purple][b]tblVechicleCount[/b][/purple]", "[purple][b]Cars;RVs;Trucks;Busses[/b][/purple]")[/blue]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[howardr]

PHV,

Thanks for the post. I thought of trying something like that, too. I was just trying to find a way to do it from my code.

Thanks for your post!

 

[howardr]

AceMan,

No need to apologize. Mom comes first . Thanks for the code. It looks just like what I was looking for! I will give it a spin tomorrow, and let you know.

Thank you again for helping me out!

 

[howardr]

Hi AceMan,

I'm trying your code now, and I am getting an error when I try to run it. I have copied your code in the module and I am trying to call the module code, but when it gets to Call SetTblProps("MyTableName", "Column1;Column2;Column3;") I get a Compile Error: Argument not Optional and Call SetTblProps is highlighted.

Please let me know what you think could be the cause. I'm not sure.

Thanks again so much for your help. This is the second time you have given me code and I really appreciate it!

 

[PHV]

Replace this:
Call SetTblProps("MyTableName", "Column1;Column2;Column3;")
By this:
Call SetTblProps("MyTableName", "Column1;Column2;Column3")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[howardr]

Thanks PH!

I appreciate your help. I am now getting another "Compile Error: Method or data member not found"
and ".CreateProperty" is highlighted. This is in the last part of the Module code commented 'Only Format in this case!

Please let me know if you have any suggestions on what to do.

Thank you so much,

RH

 

[RoyVidar]

Probably wrong declarations, try altering the declaration of the following variables:

[tt]dim Fld As dao.Field
dim prp As dao.Property
dim prpNew As dao.Property[/tt]

Roy-Vidar

 

[howardr]

Hi Roy-Vidar,

Thank you for your suggestion. I changed the declarations as you stated. It did help, but now I am getting the "Complile Error: Method or data member not found" and ".Fields" is highlighted. This is right under the "On Error GOTO Missing Property" statement. The line of code reads - For each Fld In tdf.Fields.

Thanks again for your help.

RH

 

[flyover789]

in the code window, goto Tools->References and make sure, that the Microsoft DAO 3.* reference is marked.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[howardr]

Thanks Martin!

I checked Tools > References and the Microsoft DAO 3.6 Object Library is selected.

I'm not sure why this isn't working yet, but I think it is close.

Thanks again for your help,

RH

 

[PHV]

Anyway I'd replace this:
Set tdf = db.TableDefs("tblNamNum")
By this (ie the paramater):
Set tdf = db.TableDefs(TableName)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

howardr . . . . .

Wow . . . . proper is:

[blue]Set tdf = db.TableDefs([purple][b]TableName[/b][/purple])[/blue]

Your execution line in the Immediate Windowshould be:

[blue]SetTblProps("Sensitivity Report w_Calc A&B tbl", "semicolon seperated field list)[/blue]

Don't forget the semicolon seperated field list!

See Ya! . . . . . .