Hi Folks I’m totally new here. So I’m very sorry if I abuse any of your protocols. I was very conversant with “programming” in MS access some twenty odd years ago and built a fairly comprehensive Database program for tracking all the activities, inputs and movements involved in running my farm. Now being feed up with Microsoft and Windows I have migrated to Linux and so would like to recreate my Access database in Libra Office so as not to need a dual boot set up and still be reliant on Microsoft.
My Question is twofold a: IS it possible to manipulate the data in tables and groups of tables from a macro in a button? Something like working with “Recordsets” in Access coding. And b: If so how? and could someone post a small example for me to follow and learn from? I am posting a subset
of my LOBase file as it is so far. In short in this scenario I want to bring in fields from tblBeasts, tblHerd, and tblPaddocks on frmMoveBeasts and alter the field PaddockID in tblHerd to represent a herd or herds being moved from an existing Paddock to a new one. The selection of records to alter being based on the Boolean field “Selector” in tblHerd being true.
Any assistance in this especially an example would be very much appreciated as I have spent four days now trolling the internet and forums trying to find out how to do this and not succeeding. If This can be solved I will be home and hosed. Many thanks Les.
FarmTrack2.odb (3.9 KB)
FarmTrack2-1.odb (33.5 KB)
Did you try to read the documentation provided by TDF? I have read only the german variant of the guide for Base, but there should be enough to get you started in the english one too.
https://documentation.libreoffice.org/en/english-documentation/
Your macro will not be IN the button. You have a library of subroutines, then you create a button on a form and in the properties of the button you tell which subroutine to call on wich event.
Wanderer Thanks for your reply. Yes shortly after posting the original question I finally found the documentation. I think I got confused between it and the getting started guide so when I came across it the other day I assumed that is what it was and I had already seen that. It has been a great help and I am understanding the code to a large extent.
Yes I see that the macro is not IN the button just my bad terminology sorry. In the second version of the database that I posted (FarmTrack2-1) if the macros went with it you should be able to see what I have achieved so far in sub Routine MoveBeasts however I’m still not getting something right In the SQL section I have a very long SQL statement that I imported from the query qryMoveHerd but the compiler does not like the SELECT or perhaps even the entire SQL statement. Can you point me in the right direction? I suspect I am still thinking to much in MS Visual Basic mode.
I Have just realised that I have got some of that code wrong variable iPad should be looking for the Primary key of tblPaddock not the button. My 63 year old brain is a bit slow these days. None the less the problem in the SQL section still exists. I’m Missing something somewhere.
One hint for the code: If you try to set " inside a string of macro code you have to do it his way:
"SELECT ""tblBeasts"".""Name1"", …
Isn’t this button
iPad = oSubForm.getBYName("Push Button 1")
the button, which should execute the macro?
What do you want to change here?
UPDATE "tblHerd"."PaddockID" = iPad
iPad is the object for the model of a form control - no value.
Seems you want to update one field in the form by the button - right?
Then: Why do you add such a long SQL-String? It is a select-string, has nothing to do with UPDATE.
Hi RobertG
I had intended that iPad was going to be the variable containing the value that corresponds to the Key field “ID” of tblpaddocks, that would be populated from the list box on frmMoveBeasts, which is being used to choose the new paddock. I had erroneously linked it to the Button by mistake. the SQL string was to select all the entries in tblHerd that have a Boolean field (Selector) with a true value. These would be the tblHerd records that need to be updated to to the value of the new tblPaddocks ID field. I’m just trying to get this working first so I have some idea how the macros must be written (syntax etc) then I will be adding further code that involves changes in tblBeasts, Hence the long SQL select string. My aim for this first step is to load frmMoveBeasts, select the Herd with the tickbox, select the new paddock in the list or combo box, hit the button and have the record/records in tblHerd.PaddockID updated to reflect the new paddock they are in. That is how my old Access DB works.
By The Way Merry Christmas.