Base Macro Listbox as Navigation to open another form

[Ardee]

Environment: Windows 10, LibreOffice Version: 6.4.0.3 (x64) Split Database

I have a query called “Menu List” with fields “Menu Item”,“Menu ID”,“FormName”.
I have a form called “Switchboard Main” with a listbox called “Menu”.

The form’s listbox shows a list of menu items - all items show without scrollbar, the idea is that when an item is selected, a form called “FormName” is opened.
For example, a menu item called Sales opens the Sales input form (the formname field for the menu item field).
A few menu items are just an image separator and have as formname “none” hence these menu items are not to be selectable.
The listbox has a macro on item-status-changed

In MsAccess the macro is

Sub Menu_Click()
Dim FormToOpen As String
FormToOpen = DLookup("[Form Name]", "Menu List", "[Menu Item] = Form.[Menu]")
If FormToOpen = "none" Then
Exit Sub
Else
DoCmd.OpenForm FormToOpen
End If
End Sub

Here my last attempt to find a way in Base but gives a syntax error at SelectedValue and simply does not work at all. (I tried many other ways none worked so no use showing all the failures here)

Sub Menu_Click
Dim oForm As Object
Dim oField As Object
oForm = ThisDatabaseDocument.FormDocuments.getByName("MainForm")
oField = oForm.getByName("FormName")
const FormToOpen = oField.SelectedValue
If FormToOpen = "none" Then
Exit Sub
Else
ThisDatabaseDocument.FormDocuments.getbyname(FormToOpen).open
End Sub

I have searched here for solutions and found some with a value list and a separate button that goes to a form based on the position in the valuelist, that basically does the job.
However I want to skip the button, I figure selecting the record in the listbox should be enough to trigger the macro to open the desired form (which works fine in MSAccess with the macro shown).

Also, I can’t use a valuelist and hardcode the macro, since I don’t know in advance the list of menu items because the menu items in the input menu list query is created elsewhere based on user selections of language and functions required.

Have just started moving to LO from MSAccess and overcame many new ways of doing things, I am not getting past this hurdle, could someone pls help me on my way.

Thank you.

=========================================

For anyone interested here is a demo db working as intended, thanks to the macro help from @Ratslinger.
This shows the usage of a switchboard or menu, to open forms. 2 Styles are included.

Demo-Switchboard.odb

[Ratslinger]

Hello,

You should always specify the error and where it occurs - don’t make people search & search through your code.

Do see missing End If

[Ardee]

G’day Ratslinger

Many thanks for your reaction.
I edited to expand on the scenario and added that the syntax error seems to concern the SelectedValue, parameter – I am embarrassed that you had to point that out and I apologise.
I added the End If (ooops) but that did not get the macro to work (when I compile it says syntax error and still highlights SelectedValue).
I tried another way to get the selected record using Dlookup which is in Access2Basic and seems a smooth way and similar to my old MSAccess method but that was not accepted either, possibly because I could not get the syntax right (I found it confusing because brackets are gone and double quotes are everywhere),
However, maybe I am way off in the method for getting to know the selected record in the macro.

cheers

[Ratslinger]

Hello,

Here is some minimum code(tested):

Option Explicit
Sub Menu_Click(oEvent)
    Dim sFormToOpen As String
    sFormToOpen = oEvent.Source.SelectedItem
    If sFormToOpen = "none" Then
        Exit Sub
    Else
        ThisDatabaseDocument.FormDocuments.getbyname(sFormToOpen).open
    End If
End Sub

oEvent passes information when an event occurs. You can get much from this.

I specify ‘minimum’ because you should add actual checks to verify the form exists.

Edit: Sorry, added Dim in code - my bad habit when testing is to forget these

Edit 2020-03-13:

Simple sample based upon above. Form with list box is SelectionForm

Sample ----- LBopenForms.odb

[Ardee]

Many thanks, I can see how that should work.
I believe this will help me on my way.

No macro error of course (you tested so tks again for that) so that’s a hurdle passed, unfortunately it doesn’t open a form.
I reckon the value passed is not the envisaged formtoopen in the menulist query, so I must be doing something wrong beyond the macro.

I can now use your code to setup some simple table and form tests to figure out how to use it properly…
Once I get it working as intended I’ll revert back here hopefully to signal “solved”.

Much appreciate your help

[Ratslinger]

@Ardee,

To possibly help will add sample to answer.

[Ardee]

@Ratslinger, Cool, thank you so much for the sample db.

In my case the menu names/items are not the formnames to be opened.
The menu list query has 3 columns, an id, a menuname, and a formname.
T
So I thought the way could be like this

sNewFormToOpen = "SELECT ""ItemFormName"" FROM ""MenuListTable"" WHERE ""MenuItem"" = '" & sFormToOpen & "'"
But this gives Syntax error:
BASIC runtime error.
An exception occurred 
Type: com.sun.star.container.NoSuchElementException
Message: SELECT "ItemFormName" FROM "MenuListTable" WHERE "MenuItem" = 'Coordinates

Something wrong with the syntax?
Thank you for your help

[Ardee]

I made a test database I could upload if I knew how to.

[Ratslinger]

You can attach a file by editing your question then use the paperclip icon on the toolbar. See → How do I attach a file to my question/answer?

Will need the sample to get any further - not clear as to what is being done.

[Ardee]

Tried upload but no zip, split db exceeds limit, since I’m with split db would have to redo everything in an embedded.
So I uploaded testsimple2.zip (a split db) to my server, hope that’s ok, see http://sunzon.com/download.
It includes your data (renamed a bit) and mine.
Note: Only first 10 menu item have test forms that should open.
Thank you .

[Ardee]

Fyg had to reload the proper one just now

[Ratslinger]

Don’t know what proper one is. Test based on first one (why do people use spaces in file and directory names ???)

Here is working code:

Option Explicit
Sub NewMenu_Click(oEvent)
    sFormToOpen = oEvent.Source.Model.getCurrentValue()
    If sFormToOpen = "none" Then
        Exit Sub
    Else
        ThisDatabaseDocument.FormDocuments.getbyname(sFormToOpen).open
    End If
End Sub

[Ardee]

Thank you.
You mean spaces in form names I suppose, there are none in file and directory names that I can see.
AAB-SelectionForm is your (renamed) switchboard, mine is AAA-Switchboard Main.
(I renamed some items just to regroup by sort)
Yours works fine of course.

On mine with the new macro I get an error:

BASIC runtime error.
Variable not defined


on the line:

sFormToOpen = oEvent.Source.Model.getCurrentValue()


I don’t understand how

sFormToOpen = oEvent.Source.Model.getCurrentValue()

can work, that value cannot be the MenuFormName (col 3) when the listbox only shows MenuItem (col 2) of the table MenuListTable.

Thank you for all your efforts.

[Ratslinger]

Sorry, deleted one too many lines. Add:

Dim sFormToOpen As String

At beginning of sub

[Ratslinger]

It works because it is the bound item in the list box where the selection is:

"SELECT "MenuItem", "ItemFormName" FROM "MenuListTable""

I refer to spaces anywhere - cause of many headaches!

[Ardee]

For sake of clarity;
Look at table “MenuListTable” which is the english version.
It is column 2 “MenuItem” that appears in the listbox of the switchboard form
The form to be opened for a given “MenuItem” appears in column3 “ItemFormName”

Col 2 and Col 3 could be very different, for example see ID 17.
It may confuse that col2 and col3 often look so similar, that is the case for an English user, col2 looks different in other languages, whilst col3 is always the same.
Hope this helps to make clear what the objective is.
Thank you

[Ratslinger]

What is your point? I don’t get the comment at all. This has nothing to do with Col 2 or 3 of the table but what is in the list box SQL.

You will see the sub (with the added Dim statement) works.

[Ardee]

@Ratslinger, Super, yes it works like a charm.
Yes indeed I did think earlier that the bound item in listbox should contribute but lost thinking that way as every method I tried seemed to fail.
I take note abt avoiding spaces, got it.
Thank you so much for your help, I can now move ahead with a reinforced “can do” motivation !
Most grateful,
cheers