Solved Possible to change text formatting through VBA? (1 Viewer)

[sisva18]

Hi I have this query I created called qryCustomer I have different text formats for each table.
I am showing the tables in a form.
Is there anyway I can reference this query and change the text formatting of the titles in VBA?
I want to achieve this so that I can change the language of the different text formats/titles of the tables so that it matches.

 

[theDBguy]

Hi. Welcome to AWF!

Almost anything is possible with VBA. But without seeing what you got, it's hard to say how to do it.

 

[The_Doc_Man]

The key is to find the object's "object model" so you can find the properties you want to adjust. Note that depending on which version of Access you are using, the names of some of the collections might have changed slightly. I'm using an older version.

Queries are kept in a collection called QueryDefs and each query is defined in a single QueryDef.

In a query, there is a collection called "FieldDefs" which has a bunch of "FieldDef" objects (the collection is plural; each fiend has a singular FieldDef).

One of the properties of a FieldDef is the name (of the field). So if you find the definition for the field you can then find its Format property.

What you want is probably QueryDefs( query-name ).FieldDefs( field-name ).Format but this only works for named queries.

For more modern versions of Access, QueryDefs becomes AllQueries.

 

[CJ_London]

To add to docs comments If your form is a datasheet then you need to look for the datasheet properties but they are limited in what you can do on formatting headers and columns

some screenshots of examples of what you have and what you are trying to achieve would be helpful as ‘formatting’ covers a multitude of options - fonts, size, colour, back colour, captions for labels/headers, text boxes etc

 

[sisva18]

Okay I will try to explain it is just hard to show since it was only an idea and it is work related. Meaning that i cant publish the application. So the query created in access looks like this:

I want to be able to change the name of each of these tables right now i do it statically like this:

So name is set in the titel text. However I wish to be able to set this through VBA since i have a module that handles what language is choosen and sets it accordingly. Hope it clarifies a bit

 

[sisva18]

The key is to find the object's "object model" so you can find the properties you want to adjust. Note that depending on which version of Access you are using, the names of some of the collections might have changed slightly. I'm using an older version.

Queries are kept in a collection called QueryDefs and each query is defined in a single QueryDef.

In a query, there is a collection called "FieldDefs" which has a bunch of "FieldDef" objects (the collection is plural; each fiend has a singular FieldDef).

One of the properties of a FieldDef is the name (of the field). So if you find the definition for the field you can then find its Format property.

What you want is probably QueryDefs( query-name ).FieldDefs( field-name ).Format but this only works for named queries.

For more modern versions of Access, QueryDefs becomes AllQueries.

I tried doing something like this yet not working

Function ChangeQueryCaption()   
    Dim db As Dao.Database   
    Dim qryDef As Dao.QueryDef   
    Set db = CurrentDb       
    Set qryDef = db.QueryDefs("qryCustomerFromNumberOrName").FieldsDefs("AccountNum").Format("Account number.", "LuL")         
End Function

 

[CJ_London]

ok - so by format you mean you want to translate 'AccountNum' to be be say 'Name' by using the caption property

In which case, go back to Doc's suggestion in post #3 but use the

QueryDefs( query-name ).FieldDefs( field-name ).Caption

property.

Can't suggest what vba to use since you haven't said where the translation comes from other than perhaps something like

dim fld as field
for each fld in QueryDefs( query-name )
    select case fld.name
        case "AccountNum"
                 fld.caption="Name"
         case "Name"
                 fld.caption=???
         etc
    end select
next fld

think you will then need to save the changes

In all honesty, much easier to do this in the form changing label captions than messing around with the querydef - Users should not be looking at queries anyway

This is a fairly common topic, suggest google something like 'access vba translate captions' or similar

here is an example https://www.accessforums.net/showthread.php?t=84281

 

[Gasman]

Nice way of showing a possible crosspost CJ_London

 

[arnelgp]

do you need to Localized your Labels?
see this demo.

 

[CJ_London]

I hadn't noticed - but should have noticed it is the same OP

 

[The_Doc_Man]

This is syntactically correct but semantically not at all right:

    Set qryDef = db.QueryDefs("qryCustomerFromNumberOrName").FieldsDefs("AccountNum").Format("Account number.", "LuL")

If you want to change the format in the query, it would something like

db.querydefs("qryCustomerFromNumberOrName").FieldDefs("AccountNum").Format = "LuL"

CJ's example shows the method of finding the name via a loop to enumerate the members of the collection. This method that I showed should also work since you know the names and the syntax for collections allows you to use names to identify the items. And his point about "caption" vs. some other property is also appropriate.

 

[sisva18]

This is syntactically correct but semantically not at all right:

    Set qryDef = db.QueryDefs("qryCustomerFromNumberOrName").FieldsDefs("AccountNum").Format("Account number.", "LuL")

If you want to change the format in the query, it would something like

db.querydefs("qryCustomerFromNumberOrName").FieldDefs("AccountNum").Format = "LuL"

CJ's example shows the method of finding the name via a loop to enumerate the members of the collection. This method that I showed should also work since you know the names and the syntax for collections allows you to use names to identify the items. And his point about "caption" vs. some other property is also appropriate.

I tried the line you said I just dont get the FieldDefs in my IDE neither do it autocorrect it. How can it be it throws an error "method or data member not found" and marks .FieldDefs


I have tried this and it prints the fields I want in the immediate window in the for loop. So if i can get the value how can I alter it manually in VBA?

CODE:

Function ChangeQueryCaption()
    Dim qryDef As dao.QueryDef
    Dim db As dao.Database
    
    Set db = CurrentDb
    Set qryDef = db.QueryDefs("qryCustomerFromNumberOrName")
  
    
    Dim i As Integer
    For i = 0 To 4
        Debug.Print qryDef.Fields(i).name
    
    Next i
End Function

so the for loop prints all the titles which I want to manually set... But nothing works ie caption, fielddefs, format etc

 

[CJ_London]

try

Debug.Print qryDef.Fields(i).Properties("Caption")

note you will get an error if the property has not been set.

and to set it

qryDef.Fields(i).Properties("Caption")="somevalue"

 

[sisva18]

try

Debug.Print qryDef.Fields(i).Properties("Caption")

note you will get an error if the property has not been set.

and to set it

qryDef.Fields(i).Properties("Caption")="somevalue"

Thanks man it worked now I can work something out