# Base Macro Listbox as Navigation to open another form

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


Dim FormToOpen As String
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)


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.

C:\fakepath\Demo-Switchboard.odb

edit retag close merge delete

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

( 2020-03-12 17:36:52 +0200 )edit

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

( 2020-03-13 03:17:36 +0200 )edit

Sort by » oldest newest most voted

Hello,

Here is some minimum code(tested):

Option Explicit
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

more

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".

( 2020-03-13 14:53:51 +0200 )edit

( 2020-03-13 17:21:51 +0200 )edit

@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


Something wrong with the syntax? Thank you for your help

( 2020-03-13 23:43:17 +0200 )edit

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

( 2020-03-13 23:45:42 +0200 )edit

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.

( 2020-03-13 23:48:01 +0200 )edit

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 .

( 2020-03-14 00:42:16 +0200 )edit

( 2020-03-14 00:58:42 +0200 )edit

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
sFormToOpen = oEvent.Source.Model.getCurrentValue()
If sFormToOpen = "none" Then
Exit Sub
Else
ThisDatabaseDocument.FormDocuments.getbyname(sFormToOpen).open
End If
End Sub

( 2020-03-14 01:28:01 +0200 )edit

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.

( 2020-03-14 02:37:56 +0200 )edit

Sorry, deleted one too many lines. Add:

Dim sFormToOpen As String


At beginning of sub

( 2020-03-14 02:42:45 +0200 )edit