Form updating while scrolling over list box or combo box

[RichO]

The owner of the small company I’m currently at has for the last 20 years kept track of the company’s current projects on a spreadsheet. He recently mentioned that maintaining the spreadsheet with its current ~150 active projects is taking too much time. I took a couple of Access/SQL classes nearly 20 years ago so I thought I would try to set something up that A) is easy to use and B) includes the data presented in a way he is familiar with. This will NOT include financial transactions other than total value of the project and the amount remaining to be invoiced. (that will be handled by the accounting software and transferred manually)

I’ve started a DB in Libre Office that has the following tables:
ProjectList - the main table
Customers - name and address of who we’re selling our product to
PointOfContact - who at our customer is working with us
Employees - our employees with checkboxes for different roles within the production process
Status - where in the production process a particular project is at

Reports are expected to be
Filtered or sorted by project manager
filtered or sorted by customer
Sorted by status

Creating forms is where I’m having some problems. I have a main project form that I set up some action buttons to open a “add/edit customer” form, “add/edit employee” form, and create new project. Those work.
I’m also trying to set up the ProjectList form to have the list box that has the project number (ProjectID) to be able to scroll similar to the Hire(MacroDriven) mentioned here: viewtopic.php?f=13&t=57185 .

The scrolling of the project numbers also needs to filter out jobs whose status is “Completed”. For now, editing the table would be the way re-activate a project if additional field work is needed.
I created frm_ProjectList6 with only the ProjectID in it and the rest of the data as a sub form. The frm_ProjectListOverview was created as a form with no subforms, but shows what the form should look like when(if) everything works. I’ve copied the macro from the Hire example but I keep getting errors. Unedited, the error message reads “BASIC runtime error. Variable not defined.” If I then REM out the Option Explicit, the error message reads “BASIC runtime error. An exception occurred Type: com.sun.star.container. NoSuchElementException Message: . “. REM’ing out the two subform callouts causes the form not work.
The “Macros_Intro_v1” doesn’t cover this and the pdfs “BasicGuide_OOo3.0.0” and “OOME_3_0” are too hefty to easily figure this out for what I originally thought would have been an easy project. Did I not start the form off correctly? Or am I just over my head?

[UnklDonald418]

Did I not start the form off correctly? Or am I just over my head?

Designing relational databases is neither easy nor intuitive. Your problems begin with the design of your tables which look more like spreadsheets than database tables, particularly tbl_ProjectsList.
It is definitely best first to learn to properly design relational database tables and forms before venturing into the macro quagmire.

Using a text field for a Primary Key is rarely a good idea, particularly when names of any kind are involved in part because duplicate names are not uncommon and it also opens the door for data corruption issues that can be really difficult to resolve. Since the database engine must create an integer index for the primary key anyway, a default auto incrementing integer field named ID often works best. For instance in tbl_ProjectsList, each of the fields ProjectMgr, ShopMgr, Programmer, and FiberTech should be Integer fields containing the appropriate ID value from tbl_Employees.

In answer to some of your questions.

The scrolling of the project numbers also needs to filter out jobs whose status is “Completed”

just add a where clause to the listbox query, something like

Code: Select all

WHERE "IsComplete" = FALSE

of course that assumes that none of the records have NULL in "IsComplete"


Macros are often form specific thus macros copied from somewhere else rarely work. They can often be used as a template but must you must fully understand the code so it be edited to match the target form.
For those reasons and others it is usually best to avoid macros whenever possible.
The “BASIC runtime error. Variable not defined.” error message can be avoided by defining the variable oForm before you use it.

Code: Select all

Dim oForm as Object

It is usually considered good form to place all the Dim statements before the first line of code.

When you get a message like
“BASIC runtime error. An exception occurred Type: com.sun.star.container. NoSuchElementException Message: . “
read it carefully. The part after the colon (which is missing from your post) should tell the name of the form field referenced by the macro but doesn't exist on your form.

As far as frm_ProjectsList6 is concerned your listbox query is missing a Boundfield and you don't have a Filter table to store the selection.
Look at this example to see the proper use of a listbox to select/filter records in a table.
[Example #1] Filter/Search with Forms (leveraging SubForms)