Macro for Form: Update & Display Data in Listbox subform

[3Cabbage]

Hello,

I have Openoffice on a Win10 platform. I have looked at previous Forum postings on the subject of creating search forms and making them work, and I understand that in order to do what I want for my project I need to have at least one macro.

It is a long time since I worked with databases (1980’s Oracle Ver 4 & 5), so I need a lot of help with macros.

The nearest to what I want to do seems to be https://forum.openoffice.org/en/forum/v ... +filtering . I tried loading the autors sample database and hunted round that but couldn’t find anything useful to me. I still have no idea how to create the macro or where to put it because the author seems to assume the reader has greater knowledge than I have.

What I want to do is create a database that gives a race commentator extra information about competitors when they finish e.g. Raised £3k for charity at their last race. He will use a laptop with a numeric keypad to input competitor numbers as they cross the finish line. After each number entry I expect the computer to do a query across several tables and output the folllowing information: Race Number/Competitor Name/Race Distance/Competitor Club/Previous History e.g. raised money for charity, or finished 2nd in previous weeks race.

I have created three tables:

Entries:
Field Names: ID, RaceNum, Fname (First Name), Sname (Surname), Teamname, RaceDist, Gender, Age & Cat

History:
Field Names: ID, Fname, Sname, Teamname, History, Gender, Age & Cat

Order_of_Finish:
Field Names: ID <Autofield> & CompNum (initially left blank)

I have created one Query:

Finish_History:
Order_of_Finish.CompNum is Inner Joined to Entries.RaceNum
Entries.Fname is Right Outer Joined to History.Fname
and Entries.Sname is Right Outer Joined to History.Sname

The result is displayed in descending order of Order_of_Finish.ID so that first finisher is in the bottom row of table and most recent finisher is in the top row of the table displayed.

I have tested the above and it all works as expected.

I then used the Form Wizard to select CompNum from Order_of_Finish table, then a subform containing all the fields of Finish_History query
I left CompNum as columnar with label above and Finish_History as a data table. I used no joins between the table and query.

The form lookd fine, but it was when I came to use the form that my problems started.

I opened the form and both fields were empty (as expected)

I moved the cursor to be in the CompNum field and clicked
I entered a valid race number and pressed enter.
For a split second the table populated with the details for the competitor associated with the valid race number, then it cleared to a blank field in both the CompNum field and the data table. In order to display the entry so that I could read it, I had to click on the back arrow symbol at the bottom of the screen.
In order to enter the next valid race number, I then had to click on the forward arrow symbol at the bottom of the screen which showed a blank field in both the CompNum field and the data table.

Again I move the cursor to be in the CompNum field and click.
Then I enter the next valid race number and press enter.
Again for a split second the table populated with the details for both the competitor associated with the valid race number and the previous competitor entered - then it cleared to a blank field in both the CompNum field and the data table. In order to display both entries I had to click on the back arrow symbol at the bottom of the screen.
For every valid race number I have to repeat the last two steps shown in black.

Some races could have as many as 700 competitors crossing the line in less than two hours, so the above is unworkable in the real world.

What I would like is to have a macro so that each time I enter the next valid race number and press enter, the CompNum field will clear to await the next entry and the table will populate and stay visible till the next number is input. When that next number is input then the table should update and again remain visible. That would make the database extremely worthwhile for the comentator using it.

Can anyone tell me please how to create that ideal macro, and more importantly where to put it and how to invoke it?

Incidentally, on my earlier tests I had a refresh button which did some of the work, but that seems to have disappeared now.

Thanks in advance.

[Villeroy]

Cascading listboxex? https://forum.openoffice.org/en/forum/v ... hp?t=46470

[UnklDonald418]

It sounds like a filter table might be helpful. For an example of that see
https://forum.openoffice.org/en/forum/ ... hp?t=42845
[Example 3] at the very bottom has a sample database that demonstrates filter table use. No Macros required.

If you require further assistance, please upload your database with some sample data.
https://forum.openoffice.org/en/forum/v ... =74&t=8289


A word of caution, especially when using laptops with Base. The .odb file is actually a zip archive and when using an Embedded Base database everything (tables, queries, forms, reports plus a copy of the HSQL database engine) is stored there. If an impatient laptop user shuts down the computer while Base is running, the .odb file can become corrupt and the data lost!
When messing with Macros you can crash Base with the same result.
Back up often!

[3Cabbage]

Database Needing Help.odb

(35.2 KiB) Downloaded 280 times

Thanks for the quick replies. I felt rough the past few days and was tested positive for covid yesterday, hence my tardy reply.

I will go through the cascading listboxes solution and filter tables etc. In the meantime I am uploading what I have so far as requested. Please note from a UK Data Protection point of view none of the 54 names are real people, I have doctored data to create the names and locations etc.

[Villeroy]

Your database is not well suited to serve any purpose. You simply copied some cell ranges from spreadsheets into Base.

[UnklDonald418]

Open your form in the Edit/ Design Mode.
Along the bottom and near the left of the screen select the Form Navigator icon
On the Form Navigator dialog right click on SubForm and select Properties
On the Form Properties dialog that pops up select the Data tab.
To the right of Link Master fields select the button with the ellipsis
On the Link Fields dialog you should have columns for your tables "Finish_History" and "Order of Finish". From the drop down lists select "CompNum" for both both tables.
Select OK and the MainForm and SubForm will now be linked.

With SubForm still selected on the Form Navigator and the Form Properties dialog still open
add a Push Button control to the form. It is on the list of controls along the left margin.
When the Push Button is added to the form the Form Properties dialog will change to a Properties: Push button dialog.
Change the Action property to Refresh Form.

Save the form and near the top along the left margin select Design Mode On/Off to enter the live mode.
Type a value into CompNum and press the Push Button to display the matching record(s).

With this form design, the table "Order_of_Finish" is functioning as a filter table. Each time you type a number into CompNum it is stored as the only record in that table.

Tables "Entries" and "History" duplicate the fields "Fname", "Sname" and "Teamname" leaving your database open to data inconsistencies. For an introduction to database Normalization see Chapter 2 of this tutorial. https://wiki.documentfoundation.org/ima ... torial.pdf

I hope you are past the worst and feeling better.

[3Cabbage]

A word of caution, especially when using laptops with Base. The .odb file is actually a zip archive and when using an Embedded Base database everything (tables, queries, forms, reports plus a copy of the HSQL database engine) is stored there. If an impatient laptop user shuts down the computer while Base is running, the .odb file can become corrupt and the data lost! When messing with Macros you can crash Base with the same result.
Back up often!

Thanks for advice, I always do back up frequently. If an impatient laptop user (commentator) shuts down the computer while Base is running it is unfortunate, but not a problem – the database is only intended to give real-time info to the commentator so he doesn’t have to shuffle paper notes, and all race timing is done by seperate electronic timing systems with backup systems. In any case he is unlikely to close it down before last competitor crosses the line.

by Villeroy » Your database is not well suited to serve any purpose. You simply copied some cell ranges from spreadsheets into Base.

Correct! I need to show someone that a database can give him a major "Value Add" to a service he is performing, Before I can get him to buy in to investing his time I need to do a very quick "rough and ready" working "Proof of Concept" using the spreadsheet data he already has, then I can get his agreement to develop things further. I estimate this will save him so much search time that he can add a lot more useful information into his commentary (average 2 to 3 secs for each of the hundreds of competitors).

Tables "Entries" and "History" duplicate the fields "Fname", "Sname" and "Teamname" leaving your database open to data inconsistencies. For an introduction to database Normalization see Chapter 2 of this tutorial.

As soon as I can get the form working how I want then I will spend time creating an actual Working database where I will normalise the data, and do things like concatenating Fname and Sname into Cname (competitor name). Until the form works, there is no point wasting my time on that (I initially favour pragmatism over idealism).

I tried doing what you recommended and as you say With this form design, the table "Order_of_Finish" is functioning as a filter table. Each time you type a number into CompNum it is stored as the only record in that table. I had already tried linking "CompNum" for both both tables prior to contacting you and realised it was no good for what I wanted and taken it out again.

Without the link, apart from not refreshing, it works perfectly. As I type in a competitor number, then the info for that number appears on the top line of the table before the table disappears, then as I type in the next competitor number the original info drops a line as the new info becomes the top line of the table before the table disappears and this keeps repeating till the original info drops off the bottom of the table (but you can still scroll to it if required). It means if say 3 or 4 competitors finish in quick succession the commentator can see info about all of them without haviing to scroll back. What I need is for the display to stay on the screen without having to mess about scrolling about when time is of the essence.

I am still looking for an elegant solution, but until then I have discovered that I can use a "quick and dirty" trick to keep my "non-technical" commentator happy and reduce his keystrokes.

I have discovered whilst testing the old method, every time I entered a number into CompNum it stepped forward to a null field and the table would immediately go blank. If the Order_of_Finish table CompNum had any value whatsoever in it then the Form Table would continue to display on the screen

Now instead of staring with Order_of_Finish: Field Names: ID <Autofield> & CompNum (initially left blank), I pre-prepare by filling the CompNum column with the text "Next Num" with however many entries there are (whether that be 150 or 750). When I then open forms I have to highlight the whole word Next Num against field number 1. Once I type in the first competitor number, the table will update, the yellow CompNum box will automatically highlight the second Next Num without me having to do anything further, and the table will stay on the screen. Each time the next competitor number is entered the same will happen, so effectively giving me the desired result of just typing competitor numbers as they approach the line (this database is not used for actual timing).

As I say, Not Elegant! but it will work until I can get a proper solution. I’m not sure if I should mark it off as solved on the forum board, because really it isn’t a properly thought out solution that anyone else would want to use.

Anyway, thank you Villeroy and UnklDonald418 for all your time and patience, and also for the link to the database tutorial (which I will read in more detail when I get the time). It has reminded me why I used to like working on various databases back in the last century before being promoted to other duties and also why I used to get frustrated at the system shortcomings from time to time.

kind regards

[UnklDonald418]

]I think I understand what you are trying to do, so try these changes

First of all, you don't want your query to be on a SubForm, it also needs to be on the MainForm level.
Open the Form Navigator
select and drag SubForm to the very top where Forms is displayed.
Switching to live mode the table should now display the results of your query.
You will still need the Refresh Form button.
To avoid confusion you might want to change the Name to something other than SubForm.

Back on the Form Navigator
Select MainForm to open the Form Properties dialog
On the Data tab change Add Data Only to Yes.
You will probably want another Push Button attached to MainForm, this one with the Action set to Next Record.

Switching to live mode if you type a number in CompNum it gets entered into the "Order_of_Finish" table.
Press the Refresh Form button and it will be added to the list of displayed records (assuming it is a valid number)
Press the Next Record button and the CompNum control will be cleared, ready for the next entry.

You might want to change the CompNum control on your form to a ListBox, so that bogus numbers can't be entered.
You still have the potential for duplicate entries, but changing the table constraints could eliminate that issue.

Edit: Instead of the Action: Next Record use New Record