How to store formatted text?

[ben h]

Or to put it another way, how to format stored text?

I'm going to store UI and Error message languages in flat file or db
table - first field is the variable name as key (e.g.
ERR_No_AddressLine1), then English as second field, Russian as third
field etc.

The messages will be written to form labels, text boxes, and MsgBoxes,
and may be a bit like this (in VBA string format):
"Save failed: The details for step number %number% are incomplete." &
vbNewLine & "Correct the highlighted items and try again."

I'm thick, and I don't know how to handle basic formatting and
substitutions, like carriage returns, variables such as 'Company Name',
'Software Version' and all that. I started to write a string parsing
routine, but I'm sure this has been done a thousand times before and
better by many of you, what do you do?

Any help appreciated!
ben

 

[Damian S]

Hi Ben,

You are discovering the sorrow of having spaces in your field and variable
names... Best idea is to not have them, but if you are stuck with them,
enclosing the field in square brackets will get around it. [Field name with
a space]

Damian.

 

[ben h]

Hi Ben,

You are discovering the sorrow of having spaces in your field and variable
names... Best idea is to not have them, but if you are stuck with them,
enclosing the field in square brackets will get around it. [Field name with
a space]

Damian.

I've possibly not described very well what I was trying to say. An
example will give you a clearer picture.

When a user does something in my Access app, I display a warning or
error message, for example:

MsgBox "You're in a new record. Please enter the Process information, or
move to an existing record.", vbOKOnly + vbInformation, VP_APP_NAME

However I need to be conscious that this needs to be translated to
Russian and Chinese. As well as error messages, there are other strings
I need to translate like form control labels and the like.

So to allow for easy translation I could use a db table.

Problem is that a more complex message where I want to add line breaks
is foxing me. In a VBA module it would be easy to define:

Dim ERR_NewRecord
ERR_NewRecord = "You're in a new record." & vbNewLine & "Please enter
the Process information, or move to an existing record."

However messy, not allowing use of constants. So, perhaps this is better:

Const ERR_NewRecord = "You're in a new record. \nPlease enter the
Process information, or move to an existing record."

In Javascript, that's great, but there's no automatic parsing of strings
in VB, so I'd need a custom string parsing function which is where I
have asked the group for help!

Cheers,
Ben

 

[Rick Brandt]

I've possibly not described very well what I was trying to say. An example
will give you a clearer picture.

When a user does something in my Access app, I display a warning or error
message, for example:

MsgBox "You're in a new record. Please enter the Process information, or move
to an existing record.", vbOKOnly + vbInformation, VP_APP_NAME

However I need to be conscious that this needs to be translated to Russian and
Chinese. As well as error messages, there are other strings I need to
translate like form control labels and the like.

So to allow for easy translation I could use a db table.

Problem is that a more complex message where I want to add line breaks is
foxing me. In a VBA module it would be easy to define:

Dim ERR_NewRecord
ERR_NewRecord = "You're in a new record." & vbNewLine & "Please enter the
Process information, or move to an existing record."

However messy, not allowing use of constants. So, perhaps this is better:

Const ERR_NewRecord = "You're in a new record. \nPlease enter the Process
information, or move to an existing record."

In Javascript, that's great, but there's no automatic parsing of strings in
VB, so I'd need a custom string parsing function which is where I have asked
the group for help!

I think you might be over-thinking the problem. If you are storing your message
text in a table then go ahead and store it WITH line breaks using the <Enter>
key. I do this all the time and in one app did have to deal with multiple
languages where all the text in the app (every label, caption, etc.), was pulled
from a table so it could be language sensitive.

When I need to insert different variable values within the stored text I use
markers like [CUSTOMER_NAME] and use the Replace() function to make the
insertions upon display.

 

[ben h]

I think you might be over-thinking the problem.

This is my boss' main complaint of me!

When I need to insert different variable values within the stored text I use
markers like [CUSTOMER_NAME] and use the Replace() function to make the
insertions upon display.

Thanks, that's pretty useful, and simpler than what I was thinking. Out
of interest do you pull out all language in one hit (and store it in say
a global dictionary object) or make db calls as needed for each form?

Ben

 

[Rick Brandt]

I think you might be over-thinking the problem.

This is my boss' main complaint of me!

When I need to insert different variable values within the stored text I use
markers like [CUSTOMER_NAME] and use the Replace() function to make the
insertions upon display.

Thanks, that's pretty useful, and simpler than what I was thinking. Out of
interest do you pull out all language in one hit (and store it in say a global
dictionary object) or make db calls as needed for each form?

All phrases are stored in a table with an ID number and a language field. A
function returns a particular phrase when supplied an id and languages as
arguments. Upon opening the app the language is chosen and stored in a global
variable. All text is then pulled into each form and report when opened by
using calls of the function.

 

[JK]

Ben,

I think that is question is how *you* determine the language.

Are you using the Windows settings such as:
Application.LanguageSettings.LanguageID(msoLanguageIDInstall)
(or any other parameter of LanguageID) from the Windows settings)

Or (as I do)

Keeping the language of the user in another place and if so how?

Regards/JK

I think you might be over-thinking the problem.

This is my boss' main complaint of me!

When I need to insert different variable values within the stored text I
use markers like [CUSTOMER_NAME] and use the Replace() function to make
the insertions upon display.

Thanks, that's pretty useful, and simpler than what I was thinking. Out of
interest do you pull out all language in one hit (and store it in say a
global dictionary object) or make db calls as needed for each form?

Ben

 

[JK]

Hi Rick,

I have been following this thread and as I often I work with multiple
languages, including RTL, I'm keen to learn about your view.

From what I understand, you keep every phrase against a Language and lookup
for it by PhraseID or such like, meaning that the each phrase in each
language is a record.

I store a phrases under *one* ID with different field names:

(Using Ben's languages)

tblLables
Label_ID (key)
Generic_Name (text) a key word for the expression
Custom_Name_EN
Custom_Name_RU
Custom _Name_CN
.....
some more

I pull it into the form or a report by a public function which DLookUp at
tblLables and:

LookupField="[Custom_Name_" & LangCode & "]" (will produce
"[Custom_Name_EN]"
for English)

=Dlookup(LookupField,"Labels","[Generic_Name]= ' " & whatever & " ' ")


Appreciate your comments

Regards
Jacob

I think you might be over-thinking the problem.

This is my boss' main complaint of me!

When I need to insert different variable values within the stored text I
use markers like [CUSTOMER_NAME] and use the Replace() function to make
the insertions upon display.

Thanks, that's pretty useful, and simpler than what I was thinking. Out
of interest do you pull out all language in one hit (and store it in say
a global dictionary object) or make db calls as needed for each form?

All phrases are stored in a table with an ID number and a language field.
A function returns a particular phrase when supplied an id and languages
as arguments. Upon opening the app the language is chosen and stored in a
global variable. All text is then pulled into each form and report when
opened by using calls of the function.

 

[JK]

Oops typo,

I think that the question is how do *you* determine the language.

Ben,

I think that is question is how *you* determine the language.

Are you using the Windows settings such as:
Application.LanguageSettings.LanguageID(msoLanguageIDInstall)
(or any other parameter of LanguageID) from the Windows settings)

Or (as I do)

Keeping the language of the user in another place and if so how?

Regards/JK

I think you might be over-thinking the problem.

This is my boss' main complaint of me!

When I need to insert different variable values within the stored text I
use markers like [CUSTOMER_NAME] and use the Replace() function to make
the insertions upon display.

Thanks, that's pretty useful, and simpler than what I was thinking. Out
of interest do you pull out all language in one hit (and store it in say
a global dictionary object) or make db calls as needed for each form?

Ben

 

[Rick Brandt]

Hi Rick,

I have been following this thread and as I often I work with multiple
languages, including RTL, I'm keen to learn about your view.

From what I understand, you keep every phrase against a Language and lookup
for it by PhraseID or such like, meaning that the each phrase in each language
is a record.

I store a phrases under *one* ID with different field names:

(Using Ben's languages)

tblLables
Label_ID (key)
Generic_Name (text) a key word for the expression
Custom_Name_EN
Custom_Name_RU
Custom _Name_CN
....
some more

I pull it into the form or a report by a public function which DLookUp at
tblLables and:

LookupField="[Custom_Name_" & LangCode & "]" (will produce "[Custom_Name_EN]"
for English)

=Dlookup(LookupField,"Labels","[Generic_Name]= ' " & whatever & " ' ")

I don't use a field per language because that would require design changes every
time a new language was required. I use a custom function that utilizes a
database object that is instantiated at startup which is a lot more efficient
than lots of DLookup() calls.

A typical function call would look like...

GetText(123)

That would retrieve the text with ID 123 for whatever the current selected
language is. The user chooses the desired language from a list at startup.

Currently I support three languages; English, German, and French so there would
be three records in my text table with ID 123. I also include an additional
yes/no field that specifies whether the phrase should use Arial Narrow instead
of the default Arial font. That is required because often the same phrase in
German or French requires more characters that won't fit in the space allotted.

The app has a translation form that displays one selectable language at the top
and another selectable language at the bottom. Whenever we need to add a new
language we can let the translator use that form to provide entries for all the
phrases.

I should point out that as Access apps go this is a pretty small one. I believe
that the total number of phrases is only a couple hundred or so. Doing this is
in a complex app would be a lot more work and trouble.

 

[JK]

Many thanks Rick,

I see your point about design changes, however I have never encountered
anyone who wants more then 3 languages in addition to English (English is
always there, regardless), although the other 2 or 3 languages can be any.

User can change language in his/her record in the a users form only, by
picking a language from list (combo) which has *only* the "allowed"
languages. Thereafter all the forms/Reports will speak that language.

In my tblLabels I also have fields "ObjectName" and "ControlName"
(default="~Global"), this allows specifying a phrase for the whole DB or for
a specific object (and Control, if need be) to provide for a phrase to fit a
label in a particular form or report.

So far I have worked with Korean, Russian and Hebrew. Hebrew is the one that
gives grief (being RTL) as I need the Field label to be to right of the
field and the whole form and subforms to be RTL orientation. Rather then
have a separate RTL, form I took the easy way out and place the fields in
the centre of the screen with each with 2 labels to the right and left of
the filed, not a perfect solution but did the job.

Again thank you for your replay, it is always interesting to see how the
neighbours live, I try your way next time I have to use multiple language.

Best regards
Jacob

Hi Rick,

I have been following this thread and as I often I work with multiple
languages, including RTL, I'm keen to learn about your view.

From what I understand, you keep every phrase against a Language and
lookup for it by PhraseID or such like, meaning that the each phrase in
each language is a record.

I store a phrases under *one* ID with different field names:

(Using Ben's languages)

tblLables
Label_ID (key)
Generic_Name (text) a key word for the expression
Custom_Name_EN
Custom_Name_RU
Custom _Name_CN
....
some more

I pull it into the form or a report by a public function which DLookUp at
tblLables and:

LookupField="[Custom_Name_" & LangCode & "]" (will produce
"[Custom_Name_EN]"
for English)

=Dlookup(LookupField,"Labels","[Generic_Name]= ' " & whatever & " ' ")

I don't use a field per language because that would require design changes
every time a new language was required. I use a custom function that
utilizes a database object that is instantiated at startup which is a lot
more efficient than lots of DLookup() calls.

A typical function call would look like...

GetText(123)

That would retrieve the text with ID 123 for whatever the current selected
language is. The user chooses the desired language from a list at
startup.

Currently I support three languages; English, German, and French so there
would be three records in my text table with ID 123. I also include an
additional yes/no field that specifies whether the phrase should use Arial
Narrow instead of the default Arial font. That is required because often
the same phrase in German or French requires more characters that won't
fit in the space allotted.

The app has a translation form that displays one selectable language at
the top and another selectable language at the bottom. Whenever we need
to add a new language we can let the translator use that form to provide
entries for all the phrases.

I should point out that as Access apps go this is a pretty small one. I
believe that the total number of phrases is only a couple hundred or so.
Doing this is in a complex app would be a lot more work and trouble.

 

[ben h]

Oops typo,

I think that the question is how do *you* determine the language.

I *will* be asking on startup, and store a per-user setting in the
registry. Plus I guess an options menu of sorts to change at later date.


Ben