Access2base Macro Help

Discuss the database features
Post Reply
nixonshaun
Posts: 5
Joined: Sat Feb 25, 2017 6:26 am

Access2base Macro Help

Post by nixonshaun »

Hi everyone,
I am new to Base and am trying to re-create a db i made in Access 2007 many years ago. My young son has some interest in using this for entertainment.
This is a fairly simple db.
8 Tables in total - 6 of which only have two fields a integer ID field and a text field.
They are from the Marvel (TM) hero power grid.
So there is a table called:
Intelligence -- it has two fields ID and a text field containing 7 entries: slow, normal, learned, gifted, genius, super genius, omniscient
Speed -- same has two fields numeric ID and 7 entries from slow to beyond speed of light
Strength -- same
Fighting ability -- same
energy projection -- same
Durability -- same

there are two other tables one called Heros and one called Villains. each has a numeric ID, a Name and a photo. They also each have a field (numeric that is blank-initially).
these fields are Hintelli Hspeed Hstrength Hability Henergy Hdurabillty and on the Villain table Vintelli Vspeed Vstrength Vability Venergy Vdurability.
on the Hero Form you can select a drop down (list box) scroll through the 7 entires and pick one. E.g. in list box associated with "Intelligence" you could select "Learned" and it would take the numeric ID number and insert it in the Hero table as a 3 (3 Learned).
Once you work through each of the 6 list boxes on the Hero Form you will add a numeric number from 1-7 for each blank field in the Hero table.
Villain table is the same.

I would like to create a form where you can pick a hero and a villain. the form will take the resutls from a query which talles up the six numeric fields on Hero table and six numeric fields (intelligence speed strength ability energy durability) on Villain table and gives a total. If the Hero total is larger than the Villain total the Hero wins.
In access the macro looked like this:
Dim strContent As String
strContent = Me.HeroName ... I honestly cant remember what this line meant or where it came from...i think HeroName was the field name of the Hero
IF [Htotal] > [Vtotal] Then
MsgBox "The following contestant WINS: "& strcontent &""
Else: MsgBox "The following contestant LOSES: " & strContent &""
End IF
End Sub

I cant seem to replicate this in Base but am open to suggestions
on the form where you can pit a hero vs a villain i would like 4 fields on the main form and 4 on the subform.
the Hero will be the main form with Hero ID photo, Name and total points assigned
Villain will be on the subform and have the same fields.
i need a simple macro that will look at the total points assigned to hero and the total assigned to villain and MsgBox who the winner is.

thanks for all the help, (in advance)
shaun
Linux Peppermint OS 6 netbook / Linux Mint Laptop
Open Office 4.1.3
Libre Office 5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Access2base Macro Help

Post by UnklDonald418 »

On the surface MSAccess and Base appear similar, but under the hood they are quite different. Your MSAccess macros won't work here because the OO API differs significantly from Access.
Macro coding requires intimate knowledge of your tables and form. You are more likely receive help with your problem if you upload a sample database because those providing help here usually aren't inclined to recreate your tables so they can test a possible solution.

If you need help with uploading your sample database see:
viewtopic.php?f=74&t=8289

If you are interested in learning to write oO macros you need Andrew Pitonyak's book “OpenOffice.org Macros Explained” which can be downloaded from
http://www.pitonyak.org/oo.php
The book has details on writing oO macros in general, but it doesn't include Base. He has another document available there that is aimed specifically at Base macros.

That said, I'm not entirely convinced you even need a macro.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: Access2base Macro Help

Post by Steve R. »

nixonshaun wrote:i need a simple macro that will look at the total points assigned to hero and the total assigned to villain and MsgBox who the winner is.
As UnklDonald418 has pointed out, there is a significant difference between Base and MS Access. Unlike MS Access, Base appears to be SQL orientated, meaning that macros (the equivalent of MS Visual Basic) are not used frequently. While a macro may be developable, philosophically the Base approach would point towards using SQL.

Take a look at the SQL SUM function and the SQL COUNT query.
Ubuntu 16.04 and Windows 10
nixonshaun
Posts: 5
Joined: Sat Feb 25, 2017 6:26 am

Re: Access2base Macro Help

Post by nixonshaun »

Thanks UnklDonald and Steve.
I must admit the learning curve is steep, I am not all that familiar with SQL.
I will try upload my db in the next day or two, it is currently too large, even without data.
I also have to design the last form - I may have to ask for advice.

I took a look at sql sum and count but I used "column"+"column" in a query instead.
Linux Peppermint OS 6 netbook / Linux Mint Laptop
Open Office 4.1.3
Libre Office 5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Access2base Macro Help

Post by UnklDonald418 »

is currently too large, even without data.
With your database window open, go to TOOLS ->SQL and enter/execute the following commands

Code: Select all

CHECKPOINT DEFRAG; 
SHUTDOWN COMPACT;
After that successfully executes, close and save your database. The obd file will likely be much smaller.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
nixonshaun
Posts: 5
Joined: Sat Feb 25, 2017 6:26 am

Re: Access2base Macro Help

Post by nixonshaun »

Thanks UnklDonald,
i tried that and it did not seem to make a difference, then i realized i had a image field for .bmp's.
i took those out and it is around 185 kb at present.
maybe still a little large to attach. i will try.
Linux Peppermint OS 6 netbook / Linux Mint Laptop
Open Office 4.1.3
Libre Office 5
nixonshaun
Posts: 5
Joined: Sat Feb 25, 2017 6:26 am

Re: Access2base Macro Help

Post by nixonshaun »

UnklDonald,
I have a link to Media Fire where you can download the db. Its not pretty and still very much a raw concept but it is working in a simple fashion.
http://www.mediafire.com/file/baaap3b3w ... DB2017.odb

there is a switchboard with links to the 3 forms.
there is a form titled BattleModeFormQuery -- it is made up from a query and is split in two parts 1) Main form and 2) subform.
Maine form has HeroID Hero Name and Hero Total points (numeric) as well as VillainID Villain Name and Villain total points -- the points are derived by adding together the following fields in a query (intelligence, speed, strength, fighting ability, energy projection and durability) total = Htotal for hero and Vtotal for Villain.
Subform has has Hero ID hero total points Villain ID and Villain total points plus a if then (case) statement in the query which analyzes the Htotal and Vtotal and places whichever one is bigger in a field called newtotal.
I would like a push button with a msg box that would either look at if Htotal > Vtotal and msgbox who won or something similar.
you can click on the main form in either Hname or Vname and scroll through the various added heros and villains i think there are three each.

i eventually would like the form to have list boxes where it filters and you pick a hero and villain from each list box insted of scrolling through and then hit the push button to see who wins.

thanks for the help, it really is entertainment and has no actual real world value but my son will get hours of pleasure out of it.
shaun
Linux Peppermint OS 6 netbook / Linux Mint Laptop
Open Office 4.1.3
Libre Office 5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Access2base Macro Help

Post by UnklDonald418 »

i had a image field for .bmp's
There is a tutorial on that at
viewtopic.php?f=83&t=44124&p=272724&hil ... ly#p272724
Pay particular attention to the section titled "Storing image files externally in a dedicated subfolder(s):"
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Access2base Macro Help

Post by eremmel »

Reading your requirements, made me thought: I.s.o. a macro telling the result of the 'battle' add an sub-sub-form that receives the IDs of selected Hero an Villian. With those IDs you can construct a query that calculates the outcome and you can show who won/lost.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
nixonshaun
Posts: 5
Joined: Sat Feb 25, 2017 6:26 am

Re: Access2base Macro Help

Post by nixonshaun »

Thanks UnklDonald and Eremmel.
I have started working on externally stored images in designated folder.

The idea of another subform is intriguing but I still would not know what the macro would look like
Linux Peppermint OS 6 netbook / Linux Mint Laptop
Open Office 4.1.3
Libre Office 5
User avatar
Steve R.
Posts: 163
Joined: Mon Sep 21, 2009 12:06 am
Location: Morehead City, North Carolina

Re: Access2base Macro Help

Post by Steve R. »

So far, I have not seen the issue of splitting the database into a front-end/back-end. That would be a recommended approach, if you have not already implemented it
Here is a link for getting started: [Tutorial] Avoiding data loss with built in HSQLDB. I believe there is a more specific tutorial, but I have not yet located it.

Concerning images.
1. Create an image subdirectory under the directory where your Base file is located.
2. Only store the name of the image itself in the Base field for that image.
3. Create a global variable that provides the path to your image directory. If possible, use a system call to establish that, I did not know how to do that in Base.

Code: Select all

strPrefix="/home/steve/sfmagcovers/"
The reason for the code above is that it makes you images "portable". You can move your database and images to other directories and you will only need to revise the "prefix" to still access your images.

The code below shows the image on the form.

Code: Select all

   oDoc=ThisComponent
	oDrawpage=oDoc.drawpage
	oForm01=oDrawpage.forms.getByIndex(0)	
	oImage=oForm01.getbyname("ImageControl1")
	oField01=oForm01.getByName("txtImageLocation")	  
	strSuffix=oField01.getCurrentValue()	
	strFullPathName=strPrefix & strSuffix
	oImage.imageurl=ConvertToURL(strFullPathName)
Ubuntu 16.04 and Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Access2base Macro Help

Post by chrisb »

nixonshaun, i have uploaded a sample database to mediafire.
it is your database with the inclusion of a new form called 'Do Battle'.
as well as adding a couple of macros i have also rewritten your original macros to enable them to work with base.
please download the file & let me know what you think.

http://www.mediafire.com/file/xp4ag9r2p2g8i8r/Hero.zip
 Edit: 2017-03-02 NO LONGER AVAILABLE 
Last edited by chrisb on Thu Mar 02, 2017 7:04 pm, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access2base Macro Help

Post by Villeroy »

Code: Select all

SELECT "Table Name".*, 'file:///path/' || "FileName" AS "PicURL" FROM "Table Name"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Access2base Macro Help

Post by chrisb »

thank you Villeroy i guess you're telling me that the images are not being located.
to be perfectly honest this is the first time i've dealt with images in a database.
i did move the containing folder to various different directories to test but obviously i missed something.
i downloaded your example database 'picture_links.zip' from here >> viewtopic.php?p=153834#p153834
i found it most helpful & have applied its principles to the authors database.

nixonshaun this is what i've done:-
created a table called 'tPicFileName' where the 'file path' & 'filenames' of all images are stored.
i inserted the data manually using the file properties window in combination with copy & paste. there is no form, sorry but i just don't have the time.
the 'file path' & 'filename' values are now used as bound fields by the list boxes.
i created two folders 'ImagesHeroes' & 'ImagesVillains' to separate the good guys from the bad.
added constraints to the tables 'Hero.HPicFileName' & 'Villain.VPicFileName' in order to prevent the duplication of images.
hopefully the images are correctly linked & load properly but if not then please let me know.

i've removed the original file from mediafire & uploaded the new one.
http://www.mediafire.com/file/yxa0ufukw ... vamped.zip
 Edit: 2017-03-02 19:06. Downloaded the above file onto another computer & it seems to work well. 
 Edit: 2017-03-04 07:04 Deleted download link 
Last edited by chrisb on Sat Mar 04, 2017 7:06 am, edited 2 times in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access2base Macro Help

Post by Villeroy »

Yep. Macro code is not needed to make picture files accessible and visible in forms. A little bit of SQL can do the job. You may also use a configuration table to store the path (like one of those "filter tables" we use so often). The picture paths can be relative, by the way. If you store pics and odb in the same directory, you can simply use the file names without path. If the pics are in a subdirectory, you can concatenate the subdir name with the file name.

A simple shell script can also help to collect all pics of a directory.
Under LInux

Code: Select all

ls -1 -t *.jpg > pics.txt
writes a text file with all file names into a text file, latest files on top. The text file can be used as a HSQL text table then.

A little Python snippet can also fill a form's list box with file names: http://stackoverflow.com/questions/1684 ... hon#168424
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Access2base Macro Help

Post by chrisb »

Villeroy said
Under LInux

Code: Select all Expand view

Code: Select all

ls -1 -t *.jpg > pics.txt
writes a text file with all file names into a text file, latest files on top. The text file can be used as a HSQL text table then.
the above proved to be very usefull & the same can easily be achieved when using windows, here's a link>> https://www.howtogeek.com/98064/how-to- ... to-a-file/
i typed the code below into the command prompt

Code: Select all

dir /s /b > print.txt
my intention when i first responded to this topic was to offer possible solutions to 2 questions.
1. provide a macro to launch the form called 'Switchboard' when the database first loads.
2. provide a macro that will look at the total points assigned to hero and the total assigned to villain and MsgBox who the winner is.
i supplied code in answer to both issues.

having not previously worked with images i messed up on my first attempt, the images were not found on other peoples PCs. many thanks to Villeroy for pointing this out.
when working with linked images we apparently require a dedicated table in which to store the path & filename of each image.
after looking at an example database posted by Villeroy (who else) i updated my example, added the file locations to a dedicated table & changed list box bound fields to use these filepaths.
example 2 was born.
having now had the time to take all things into consideration i believe that using the file locations as bound fields was not a good move so have added an ID field (integer) to the file path table & reverted to using ID fields in the list boxes.
the authors original database has 2 forms used to enter heroes & villains which i have updated in order to display the image of the selected combatant.
the authors original database has 1 form 'BattleModefromQuery' which compares a sum total of the strengths of all combatants in a hero v villain scenario (in effect a cartesian join). i have updated this form to show the respective images & added a text box with the message ## Wins. no macros used here.
so example 3 is born. this is my final effort & can be downloaded from here >> https://www.mediafire.com/?f767a42271ybaf6
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access2base Macro Help

Post by Villeroy »

chrisb wrote:so example 3 is born. this is my final effort & can be downloaded from here >> https://www.mediafire.com/?f767a42271ybaf6
I love it :bravo:
... for its funny characters, because the heroes win (mostly) and because it demonstrates that you do not need much macro code if you got the underlying structures right.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply