Need help With Macro

English
#1

To Whom It May Concern,

This is a plea for help, I need a LibreOffice BASE Surgeon.

I am a 65 year old retired lead senior ORACLE and IBM-DB2 database administrator who worked for a local power company in our area for over 15 years.

Since retiring, I have endured several health setbacks which include: heart attack, adult onset TYPE-1 diabetes, and several side effects resulting from aforementioned setbacks.

Obviously and fortunately none of these health issues have been fatal, but require constant monitoring in order to keep them from being so.

This is not an easy task and requires precise control of blood pressure and blood sugars, which are managed using 3 different heart medications, 2 different types of Insulin, and calculating carbohydrate and calorie intake of all foods.

After all the above, the food intake, insulin intake, blood pressure readings and blood sugar readings need to be recorded 4 times a day to make sure nothing is out of range, as well medicare requires monthly reports in order for them to continue covering the cost of the supplies I need.

Because of this and being an ex “DBA” I spent the last month trying to design a database and front-end using LibreOffice BASE in order to reduce the time it takes to track all of this information.

Unfortunately after many iterations, I have had to settle with a design which is 80% working and 20% clunky at best.

I believe the issue can be resolved through the use of a macro, but after many hours of reading the macro documentation I have been unable to gain even the basic understanding on how to write such a macro.

Could someone please take a look at a stripped down version of the overall design (Please Find Attached) and let me know if I do need a macro, and if so would be willing to write a one to fix the problem.

I am willing to pay for your efforts as I know your time is valuable.

Sincerely,
nLightEnd1
Health_Tracking_DB.odb (274.9 KB)

#2

Don’t understand which problem should be solved. So only a hint: Chapter “Macros” in Base Guide may help to understand, how macros will work in Base. See LO documentation (English).

#3

is the problem concerning: inserting a row and an error related to a many-to-many relationship in the database structure? You can search the forum for this problem. I know there is a good example of a database, including a form, that solves this, by Villeroy? but I can’t find it at the moment.

#4

My appologies,

It seems I wasn’t very clear about what my problem is. What I need is a table which holds a static list of food_items.

I also need to be able to log the date, time and number of servings IE: consumption information for a specific food item.

This cannot be done in the food_items table as the date, time and number of servings is not specific to a food item.

I would create a many-to-many relationship to tie the consumption information to the food list information, but due to the limitation of a list control on a subform only allowing updates to a single table, doesen’t allow me to use the relationship.

The only way I can see to re-solve this dilema is to have a single table which combines the consumtion information and food item information into single table, in this case the food_intake table.

I was then hoping to select an item in the Food List control, run a SQL select / insert statement using a macro to insert the selected item, into the food intake table. This would allow me to enter the consumption information in a Food Intake list control embeded in a subform.

The biggest problem with this idea is, after many hours of research, am not grasping how to write such a macro, and was truly hoping to purchase a macro from someone on your team who has the knowlege and experience using LibreOffice Base.

#5

There are many example in Base Guide. I will try to explain:

SUB ListBoxChanged(oEvent AS OBJECT)
   oListBox = oEvent.Source.Model
   oForm = oListBox.Parent
   oConnection = oForm.activeConnection()
   oSQL_Statement = oConnection.createStatement()
   …
END SUB

Don’t know if you need the value, which should be saved, or the content, which has been shown by the listbox.
The value, which should be save in the database, you will get with inID = oListBox.CurrentValue
Then you want to save this in any other table:
stSql = "INSERT INTO ""table1"" (""fieldname"") VALUES ('"+inID+"')"
This should be saved:
oSQL_Statement.executeUpdate(stSql)
And now all together in the procedure:

SUB ListBoxChanged(oEvent AS OBJECT)
   oListBox = oEvent.Source.Model
   oForm = oListBox.Parent
   oConnection = oForm.activeConnection()
   oSQL_Statement = oConnection.createStatement()
   inID = oListBox.CurrentValue
   stSql = "INSERT INTO ""table1"" (""fieldname"") VALUES ('"+inID+"')"
   oSQL_Statement.executeUpdate(stSql)
END SUB

Haven’t defined the variables explicit. All with ‘o’ will be OBJECT, ‘in’ I will use for INTEGER, ‘st’ I will use for STRING.

You could add this procedure to the list box: Control Properties → Events → Item status changed

Hope this will be a hint to what you need.

#6

Robert,
Again thanks for your reply.

Close but but not exactly what I am looking for, here is an example I’ve been testing

Sub Insert2FoodIntake
Dim Context 
Dim DB 
Dim Conn 
Dim Stmt 
Dim Result
Dim strSQL As String

Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("MACRO_TEST_DB")  
Conn=DB.getConnection("","")
Stmt=Conn.createStatement()
strSQL="INSERT INTO "&" FOOD_INTAKE" & _
     "( " & "Serving Size" & _
     ", Manufacture" & _
     ", Food Item" & _
     ", Carbs" & _
     ", Calories" & ")" &_
"(" & "SELECT " & _
   "Serving Size" &_ 
   ", Manufacture" &_
   ", Food Item" &_
   ", Carbs" &_
   ", Calories" &_
" FROM FOOD_LIST" &_
" WHERE FL-Id = selected row.Id );"
 
Stmt.executeUpdate(strSQL) 
 
Conn.close() 

End Sub

To visulize what I’m trying to do, imagine the food list is a menu at a restaurant.
I want to select an Item from the menu lets say tbl1 is the food_list (menu), here
all items are already defined.

Once a row, or rows are selected in the control, use an execute action event assigned
to a button in the form.

I’ve been looking at examples but still am having trouble with getting the value of the selected
row or rows, for use in the SQL statement, and in the case of multiple rows how to loop through the array to run mutiple SQL statements.

#7

Don’t know where you will get this from. It is a value of a field in the form. It’s like I have written with oListBox.CurrentValue. And this variable couldn’t be set directly as SQL-code, so it might be

" WHERE ""FL-Id"" = '" + oListBox.CurrentValue + "')"
#8

@nLightEnd1,
.
the question that I ask myself is why?
.
your intentions flagrantly violate the most basic rules of normalisation.
.
a list box item would need to be selected before a macro could act on the chosen value.
.
surely you would prefer to view carbs, calories et cetera before and not after making a selection.
.
manufacturer, serving size, carbs and calories can easily be shown via the list.
.
the stripped down attachment provides a practical and valid alternative.
Health.odb (21.8 KB)

1 Like
#9

LOL,
You are almost correct, my intentions would violate the most basic rules of normalisation, but flagrantly is an under statement.

That being said I like what you’ve come up with, but will need to review the reports to make sure I can get the calculations, and consumption report the way I need it.

Thanks for the head start.

I’ll let you know how it works out.

#10

Dear Mr. or Miss. cpb,
I spent this weekend working with with your proposed solution to my problem and must say it truly is a masterful piece of work.

I did need to make one change to the consumption query and control in order to show consumption per meal, as I need this information to calculate the amount of insulin I need to offset the amount of carbs consumed, but you wouldn’t have known that.

Unfortunately, it was at this point I realized that the single drop-down list of food items is not a good long term solution, unless it can be searched or filtered.

here’s why …

In the morning I could have a cup of coffee with cream and bowl of cereal with a sliced banana on top.
For lunch I could have a ham sandwich (2 slices of bread 4 ounces of ham mayo and mustard), a bowl of soup, and a Coke. Then for dinner a steak, baked potato some broccoli and cauliflower, this is a total of 15 items and is just one day of meals.

Let’s assume 2 or three items from above would be consumed daily, and 13 items from above probably not. As well, some meals such as spaghetti and meat balls would require over 15 items on its own

Based on that theory I will have a minimum of 395 items in the drop down in a month, and 4745 items in the drop down in a year.

Any thoughts?

#11

@nLightEnd1,
.
it’s fairly easy to navigate the list.
.
once you tab onto the list box, drop the list using mouse or (Alt+down arrow).
hit C and the first item beginning with C will be selected.
hit C then quickly hit H and the first item beginning with CH will be selected.
hit H repeatedly to cycle through all items beginning with H.
you can also use Home, End, PgUp, PgDn, up arrow and down arrow.
.
I built a database similar to yours some years ago.
the list was based on complete meals as shown on an actual fixed menu.
each item was prepended with a category, Breakfast, Dinner, Desert, Fruit etc.
an item would be listed as:
‘Dinner - Roast Beef, Roast Potatoes and Cabbage’
or
‘Breakfast - "Bacon, Sausage, Egg and Chips’
so in the example it’s hit B for breakfast items or hit D for dinner items.
this method is quick and kind on the user (no extra clicks and every item visible).
.
you may require a very large number of categories therefore categorising your food items is probably not practical.

1 Like
#12

Congratulations. This database looks very well done.

Most of your subforms are not linked to the main form.

  • Subform-INSULIN_INTAKE is linked to the common FM-id.
  • Subform-INSULIN_INTAKE is NOT linked to the common FM-id.
  • Subform-FOOD_INTAKE is NOT linked to the common FM-id.
  • The notes do not refer to anything.
  • Subform-FOODLIST does not have any common field with the main form because it is not directly related to the master form of familiy member.
    Many–to–many relations in forms are tricky. This is the way to go:
  1. Open the forms navigator
  2. Delete the Subform-FOODLIST
  3. Add Subform-FI-FL as a child of Subform-FOOD_INTAKE linked by their common Fl-id
  4. Add a table control to Subform-FI-FL (cancel any wizard popping up!)
  5. Add a listbox to that table control
    4.1 Linked to: FL-id
    4.2. Source type: SQL
    4.3. Source: SELECT “Name”, “FL-id” FROM “FOOD_LIST” ORDER BY “Name”
    or store the SQL as a query and use that with source type “Query”
    4.4. Bound field: 1 (which is the second one “FL-id”)

The table grid will show a column of listboxes showing the names of the current food intake’s food list members while representing the corresponding “FL-id” value.

P.S. Done:
Health_Tracking_DB2.odb (274.9 KB)
I created the query based on the second foodlist, renamed the subform, assigned it to the fi-fl table, dragged it to subform-food_intake so it becomes a child form of that one, removed all columns, added the listbox linked to the query. If you need the food list details, add another subform to subform-FI_FL

#13

Dear Mr. or Miss. cpb,
Outstanding !!!

Will definately start catagorizing my food items in order to make searching easier.

As I said earlier, a masterful piece of work.

Please let me know how I can compensate you for your time.

Sincerely,
nLightEnd1

#14

I thought that I would let the dust settle before replying.
both the original poster(OP) and contributors can choose whether or not to post a response.
what matters most to me is the attitude of the OP.
when I perceive the OP as genuinely appreciative then I feel rewarded.
when I perceive the OP as unappreciative then I feel regret and will never again reply to any of the OPs threads.
the author of any chosen solution is irrelevant, all contributions add to the debate.
.
obviously you fall into the appreciative category and therefore the short length of time I dedicated to this thread was time well spent, thank you.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.