Trap a message box.

[PhilOfWalton]

I have built a Db that translates other Databases into any language you choose. Still very much at the testing stage, but one German Db with 24 forms and 24 reports as well as my own Db written in English with 9 forms and 3 reports took 64 minutes to translate into English, German, French and Spanish. (A total of 607 words * 4 languages - 2400 odd words)

Translating from both German and English to Greek was slower taking 23 minutes

In addition I translate any message box messages found in the code of forms, reports & modules

Not Every word will translate if it is incorrectly spelled in the original language, and I ended up with the 607 original words + 2080 translated words

Thereafter, form & reports pause for about a second before loading in the correct language.

Anyway, that's background stuff.

My problem now is I can show the translated message box by changing the code from

Msgbox "My Message", VbInformation, "Message Box Title"
 

to

DbT_MessageBox "My Message", VbInformation, "Message Box Title"
 

In addition I can predefine the message using

DbT_Msg = "Are you a happy bunny?"
If DbT_MessageBox(DbT_Msg, vbYesNo + VbQuestion) = vbNo then ...
 

I purposely didn't use DbT_MsgBox because, if there is no solution to the problem, it is a comparatively simple matter to scan the code and change Msgbox to DbT_MessageBox

My question is, and I think it very unlikely, is there a way of "trapping" the standard Msgbox routine so that I can substitute the translated message before it is displayed?

I really want to avoid altering the original Client's Db if possible

Phil

[NeoPa]

Yes. Define the same procedure as Public in a standard module.

[Rabbit]

Wouldn't trapping the standard routine require altering the client's database? You would have to insert some sort of module that's constantly running to trap the MsgBox routine.

Irregardless, I want to say it's probably technically possible by tapping into the Window's APIs. But I don't know how that would be done off the top of my head.

I thought you were already modifying the database by translating strings into another language? Shouldn't the strings in the MsgBoxes also been translated as part of the process?

One way to do what you want without changing existing modules and without having to replace all function calls with your Dbt_MsgBox function is to overload the existing MsgBox function.

Function MsgBox(Prompt, Optional Buttons, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult
    MsgBox = VBA.MsgBox("overwritten", Buttons, Title, HelpFile, Context)
End Function

[PhilOfWalton]

Thanks Rabbit, Phase 1 works like a charm.

Slight change to the code so it may be useful for others

 
Function Msgbox(Prompt As String, Optional Buttons, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult
    ' Wrapper for msgbox
 
    Dim TranslatedMsg As String
    Dim LanguageID As Long
 
    LanguageID = TempVars!RequiredLanguageID
 
    TranslatedMsg = TranslateMsgBox(Prompt, LanguageID)
 
    Msgbox = VBA.Msgbox(TranslateMsg, IIf(Not IsMissing(Buttons), Buttons, 0), _
    IIf(Not IsMissing(Title), Title, ""), _
    IIf(Not IsMissing(HelpFile), HelpFile, ""), _
    IIf(Not IsMissing(Context), Context, 0))
 
End Function
 

The Tempvars gives the language to translate to and the TranslatMsgBox gives the translated message.

Again, very gratefut thanks

Phil

[PhilOfWalton]

Ah! I spoke too soon.

I have a main database and a library database.

The above routine works perfectly in the library database, but, not unexpectedly, if I use Msgbox in the main database, it uses the standard Msgbox function, rather than invoking the new Msgbox functin in the library database.

I doubt whether it's possible to force the main database to use the CodeDb Msgbox.

I hope I'm wrong

Phil

[NeoPa]

You could change it manually on a database by setting the order of the references such that the library is higher up than the VBA library (which provides the original MsgBox() function) but not sure you could do that to a database using code. I expect there is a way if you search for it.

[Rabbit]

I'm a little confused, how exactly is the translation database interacting with the main database?

[PhilOfWalton]

@ Neopa

'Fraid that doesn't work because, as far as I can see the first 2 references are Visual Basic for Applications and Access Object ... Library. They seem immobile so the best I can do is put my Library Database (DbTranslator) at number 3. Certainly can't do it using the up & down buttons in the "Available References" window.

@Rabbit
The idea is to take any Client's database and by adding the above Db as a library database and adding an Autoexec, after translating the Client's db into whatever languages he chooses (can have multiple languages) and he just changes the output language to French, German, Greek etc to see the forms, reports & MsgBox's in the required language.

Initially the translation rate is about 20 words per minute. The Client's Db that I am playing round with at the moment has 24 forms and 24 reports. That together with the DbTranslator consisting of 9 forms & 3 reports takes about 30 minutes per language to translate. Once done, there is a slight delay of perhaps a couple of seconds to load the translated form or report in the required language.

Your routine traps the Msgbox in the DbTranslator database and displays the message in the correct language, but messages in the Client's Db aren't trapped.

Again I appreciate your input

Phil

[Rabbit]

How are you translating the client's database without modifying it?