Using DLookup to access a database

[RoryOk13]

I'm using Access and Word 2010. My client wants me to create a multilingual Ribbon Bar which, at the click of one of the controls, it changes the labels, tips, etc to the selected language.

As you might already know, the XML behing the Ribbon UI has a series of controls with an ID. There are also fields for labels, etc. So the theory is that, as the language choice is changed, the XML calls back a procedure in Word VBA which in turn, reads an Access database for the correct label based on the ID of the control in the Ribbon XML (phew!)

I've created the database with five fields: One has the names of the controls in the XML whilst the other four have the appropriate translated labels for each language required.

I'm creating the building blocks little by little just to get the theory correct and have the following procedure in a word module:

Sub UsingDAOWithWord()
    Dim dbRibbonData As DAO.Database
    Dim myString As Variant

    Set dbRibbonData = OpenDatabase _
        (Name:="D:\MyDATA\MyClient\RibbonX Project\" _
        & "RibbonData.accdb")
[B][COLOR=Red]myString = Access.Application.DLookup("English", "Language_Labels", "Field_Code = 'rxmnuLanguages'")[/COLOR][/B]

MsgBox ("The result is: " & myString)

    dbRibbonData.Close
End Sub

It compiles OK but when I run it I get "Run time error 2950 - Reserved Error" on the line highlighted in red above.

Searching high and low in every forum I could find does not give me a result in spite of all the good examples of coding I have applied from helpful members.

I believe I have all the correct References set up in Tools>References in the VBE.

In theory, what I'm trying to achieve seems very simple, but it sure is giving me a headache in this instance!

Can anyone offer any suggestions as to the correct steps, please?

 

[DavidAtWork]

I would imagine the problem is because there's no reference to your database object dbRibbonData in your myString = ... line
Your code seems to assume the DLookup will automatically work against dbRibbonData

David

 

[RoryOk13]

Thanks David.

Have tried your suggestion and made various permutations in referring to my database object but with no success. Now it doesn't even compile!

Any idea how the coding would actually look if you were doing it, please?

 

[DavidAtWork]

I think you need to set a connection to the database from your Word vba and then use a SQL statement to return the required value, something like this:

Dim dbConnectStr As String
Dim conn As ADODB.Connection
Dim dbPath As String
Dim strSQL As String
Dim rs As ADODB.Recordset

dbPath = "D:\MyDATA\MyClient\RibbonX Project\" _
& "RibbonData.accdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

strSQL = "Select English FROM Language_Labels where Field_Code = 'rxmnuLanguages'"

Set conn = New ADODB.Connection

conn.ConnectionString = dbConnectStr
conn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, conn
If rs.RecordCount > 0 Then
MsgBox rs.Fields("english").Value
End If

David

 

[Ari]

Hi

The link below is an excellent example available.

http://www.ribbon01.com/downloads.asp

It used the tag "Tab" to store the values ​​in the languages​​. Example of a button:

<button
id = "btSuppliers"
imageMso = "DistributionListSelectMembers"
getLabel="fncGetLabel"
onAction = "mcrRibbon.mFornecedores"
tag="Fornecedores;Suppliers;Proveedores"
/>

See how fncGetLabel is super simple.

Public Sub fncGetLabel(control As IRibbonControl, ByRef label)
Dim k
k = Split(control.Tag, ";")
label = Nz(k(CurrentDb.Properties!idioma), "")
End Sub

Function split () is used to extract the value of the "TAG"

Sucess!

 

[RoryOk13]

Thanks Ari for your reply and for the great use of the Tag command to store multiple choices. Great!

One other thing: I was very impressed with the offering of 'ribbon01.com' with their MontaRibbons Wizard. Brilliant tool. I wish there was something like that for Word and Excel Ribbons. Or is there and I've just not found them yet?

 

[Ari]

Hi

check the website of Avenius.

http://www.accessribbon.de/en/


Not sure if it meets the Excel and Word