Let’s get images working on a LibreOffice (Open Office) base database form, in this tutorial I show you how to get this done without having to be a coding ninja.
The idea for this tutorial came from a project that I’m hacking together, you see I’m aware that the navigation on this site could be better, so I’m creating a LibreOffice database that ties the content on my youtube channel with these blog posts, so that I know what’s where, and can continually improve this site.
Why Link Images Dynamically?
The main reason I want to link images dynamically is that it makes sense, you store your images on your hard drive in a folder that’s accessible to all your other programs.
This gives you the advantage of not having duplicate copies of images in your database and elsewhere.
Embedding images in your database causes your database tables to bloat, which can impact performance and be a major pain because you’re duplicating images unnecessarily.
Get the Database used in the tutorial video above.
Also get updates when new blog or video tutorials are released.
Here’s what we’re going to do
We’re going to create a simple database in LibreOffice using basic code that displays an image or photo for each record.
These images will be dynamically displayed (that’s to say they are not embedded in the database, but rather they’re in a separate folder) and linked at runtime in the “VBA” (actually LibreOffice / Open Office basic code).
This database will also handle the situation where no image is available for the given record without crashing the code.
If you’d like to follow along with this database
Download instructions for the database with images will be emailed to you by filling out the newsletter box below, if you’re already on the newsletter don’t worry, just enter your name and email again and you’ll be immediately forwarded to the download page.
These two videos cover what you need to know to do this stuff.
The video above is the one that goes with this article, however, if this is too advanced, you could go through these first.
How to Build a Form and Subform
Here you build a Form and Subform relationship without using any code, e.g. in the form click "Europe" and the subform gives you all the countries in "Europe".
Getting Started Coding a Form
This get’s you started with some simple coding, you will then be ready to tackle the video at the top of this page.
Let’s Build the Database Table
Firstly you’ll need a database if you need to know how to create one the "Create A LibreOffice Database" article will get you going, it’s really short!
Create a simple test table
Click on Tables within the database window then "Create Table In Design View".
We then need to add some fields (columns) to the table, first we need an ID column, the example here uses "bp_ID" (businessprogrammer id), we choose a Field Type of Integer and set it’s AutoValue property to Yes.
This means the ID value will autoincrement as records are added to the table without you having to program anything to get this done.
This is the best way to do things, save the programming for when it’s absolutely necessary.
Now we’ll just add the other fields, the "Description" field will give us some information about the photo (In reality this could be a table with contact information, the options are limitless) and for the "Field Type" let’s choose "Text [VARCHAR]".
FileName will contain the filename of the image and we’ll set the "Field Type" to "Text [VARCHAR]" also.
Click the save button and give the table a name, then close the design view version.
Put some data in the table
Double click on your newly created table, you’ll enter values into the "Description" and "FileName" fields, the "bp_ID" field will look after itself.
The download that’s available with this tutorial has the database, image files and all the code to view.
If you want to do it yourself manually, you would add the images folder into the same folder as the database, so I created a folder called imageDatabase and saved the database there, the "imageDatabase" folder has a sub folder called "images" where all the "png" files are stored.
This means that if I want to copy the database to another PC or Mac (or Linux machine) all I’ve got to do is copy the "imageDatabase" folder.
Add the data in the image above to your table, or use your own data, note the FileNames have to be actual filenames that exist in your images folder.
With your database table loaded with some information, now it time to …
Build The Form
Create The Form Document
Click on the Forms image in the Database pane below, then click on "Create Form In Design View".
Add a Datasource To the Form
Next before we add any controls to the form, we need to create a "datasource" for the controls to link to.
To do this you will need the "Form Navigator" window displayed, this is on the "Form Design" toolbar (blue square nearest to the right of the image below).
Presumably for some unknown historical reasons this "datasource" is called a "Form", so go ahead and add a new form (datasource) to the form.
Now that you’ve added a new form (datasource) to the form, the properties window needs to be displayed so that you can… set it’s properties.
But first let’s save the form, so we don’t lose any work.
Click the save Icon, and name the form "frmImages".
Set Datasource Properties
You’ll find the properties button on the same toolbar as the "Form Navigator" button. Just hover your mouse over the visible properties, it should be the one to the left of the "Form Navigator" icon.
Now that the properties are up, make sure your new form (datasource) is selected in the form navigator, so that we can set some of its properties.
On the "General" tab you could set it’s name property to something like "dsPhotoInfo" (ds for data source).
The "Data" tab will have it’s "Content type" set to "Table", and it’s "Content" set to the actual table, in this case "tblPhotoInfo".
Add Description Text Box To Form
Time to put the description text box onto the form.
- Ensure dsPhotoInfo is selected in the "Form Navigator
- Click on the "Text Box" control on the "Form Controls" toolbox and draw it on the form.
- Set the "Data field" property of the "Data" tab of the "Properties" window to the "Description" field, also on the "General Tab" set the name property to "txtDescription".
Add An Image Control to Form
The image control is located towards the end of the "Form Controls" toolbox
This will mean that in some cases you will have to expand the double chevrons to locate the image control.
Again make sure that your datasource is selected in the form navigator, this selection governs which datacontrol your form control is bound to.
Click on the image control and draw the control on your form document large enough to display your image for your preferred size.
In the properties window give your image control a meaningful name such as "ctrlPictures" (see image below).
Then click the "Data" tab (in the properties window displayed below) and set the "Data field" property to "FileName" this being the field name in the table "tblPhotoInfo" that holds the name of your image file.
Prepare The Coding Editor
We’re going to go into the code editor now, to write the code so that your form image is updated from the image file name in your database table.
The download that’s available contains all the code already typed out however the code listing is also below.
The idea here is that you have the mindset of an "application builder" (dare I say a business programmer) and just like a house builder who doesn’t construct the doors, pipes, central heating etc, but instead installs readymade components, I’ve made some readymade components for you in the form of some of the functions below.
Add Code Module To Project
So with that in mind choose "Tools" then "Macros" then "Organise Macros" then "Basic".
What’s very important here is that you stay organised and pay attention, for some silly reason the cursor location seems to default to the "My Macros" section which is all well and good if you don’t want to distribute your file to other people or even other computers.
I however like my code to be in the file (Just like Excel or Access or most programs that you use), so make sure that your database is selected then click "Organiser…" see image below.
On the "Basic Macro Organiser" window choose the "Modules" tab.
We’re going to add a module with a name similar to the form that we created, this is so that when you’ve got loads of code in your project, you’ll be able to easily know where everything is (otherwise things can get messy real fast).
Given that the form will be called "frmImages" (we haven’t saved the form yet – oops…) I’m going to call this module "m_frmImages" (i.e. this is the code module to go with "frmImages").
So let’s click "New" to get the new form dialog box.
Type in "m_frmImages", Click "OK" and you’ve created your code module in your database project and you’re good to go.
Now we need to do some coding, so to get into our code module, we need to choose "Edit Macros" rather than "Organise Macros".
This will take us into the "Code Editor", pay attention to the "Object Catalogue" window below, again LibreOffice seems to automatically select "My Macros and Dialogs" (this can become a problem if you’re just using the standard "main" module, where you find you’re accidently typing code into the wrong module).
Expand your database and then the "Standard" Library (you can create your own custom Libraries in the "Organiser" section mentioned earlier), double click on your newly created module "m_frmImages" and delete the existing boiler plate code so that we can add our own code.
Now paste in the code that’s linked here to your module on line 32, this is boiler plate code that you can reuse in many different projects.
It consists of six functions that handle most of the "under the hood" code activities freeing you up to handle the "business rules" code.
These functions are
getFormFromEvent(event)
Gets a reference to the datacontrol that raised the event. This allows us to get "code" access to the current selected record.
getColumnValue(dataset, ColumnName)
wrapper function for getColumnData() below, allows us to just specify the column (field) name that we want the value of for the current record, without having to write too much code.
getColumnData(ColumnObject)
Get Column Data from passed in column object, the previous function (getColumnValue(dataset, ColumnName)) wraps this for simplicity.
getDbPath()
Gets the database path as a string in a LibreOffice friendly manner. Wraps about 20 lines of code into a simple function call.
createGraphic(ImageFilePath)
The image control needs to link to a "Graphic" object, this function converts the file path to your image file into this graphic object.
imageFileMissing(FilePath, ImageControl)
This is a simple function that checks the file path to see if we actually have an image file for the one specified in the database table, if no image file is found the image control is cleared.
The above functions are pasted into your form code module and we can discuss the function that you’ll manually type in next.
Get the Database used in the tutorial video above.
Also get updates when new blog or video tutorials are released.
Coding The Record Change Event
LibreOffice base has, like most Object-Oriented systems, an event model, this means that as it does stuff, it fires "events" (think messages) to the system.
So, every time you move your record selector an "After Record Change" event (among lots of others) is fired.
Our job is to have code that’s listening for this message, and grabs the message and it’s associated object (the event object which contains loads of information) when the event is fired.
… and the event is fired by LibreOffice Base when the database moves to a new record.
You’ve pasted in the code as instructed so let’s manually type in the AfterRecord_Change Subroutine.
Code listing 1
01 Option Explicit 02 10 Sub AfterRecord_Change(byref e as Object) 11 Dim dsVideos as Object 12 Dim oImageControl as Object 13 Dim oGraphic as Object 14 Dim szFullImagePath as String, szFile as String 15 16 ' Get reference to the form that threw this event 17 dsVideos = getFormFromEvent(e) ' Get Reference to form / resultset 18 19 oImageControl = dsVideos.getByName("ctrlPictures") ' Get Reference to Image Control 20 szFile = getColumnValue(dsVideos, "FileName") ' Get value from VideoID field 21 szFullImagePath = getDbPath() & "images/" & szFile 22 23 if imageFileMissing(szFullImagePath, oImageControl) then Exit Sub 24 25 oGraphic = createGraphic(szFullImagePath) 26 oImageControl.Graphic = oGraphic 27 oImageControl.ScaleImage = true 28 End Sub
Let’s analyse this code line by line, line 10 contains an argument "byref e as Object" this variable "e" captures the event object that Libre Office created, and contains all the information that this routine needs.
Further down this tutorial you’ll see that we set the event property of the datasource (in the properties window) to point to this subroutine, LibreOffice creates the object variable that’s passed to this routine via the "e" variable (you can call this variable whatever you want in the routine).
Lines 11 through 13, here we declare all the object variables that we’ll be using in this routine, unlike in Microsoft VBA where you declare all objects explicitly (you can just use "Object" (late binding) in vba, but then you lose intellisense, there are advantages and disadvantages to doing this) in LibreOffice basic you just use the generic "Object" datatype.
Line 14, here we declare two "String" variables, "szFile" will hold the file name from the current record of the "FileName" field in "tblPhotoInfo". The "szFullImagePath" variable will do what is says on the box, hold the full path to the image.
Line 17, this is where you get the reference to the datasource object, this object will always contain all information on the current record, meaning you can get the value of any field from the dsVideos variable, my getFormFromEvent() function (code listing 2 below) encapsulates all the code below, which means you don’t have to write the function, but also it separates the coding requirements from the business rules and thus keeps your code neater and easier to read.
Line 19 gets a reference to the image control on the form using the "getByName()" property of the of the form datasource (built in LibreOffice function), the "oImageControl" variable will be used to load the image to the form.
Code listing 2
93 Function getFormFromEvent(e as Object) as Object 94 Dim szModuleRoutineName as String 95 szModuleRoutineName = "m_frmImages..GetFormFromEvent" 96 On Error Goto ErrorCheck 97 98 select case e.Source.ImplementationName 99 case "com.sun.star.form.FmXFormController" 100 GetFormFromEvent = e.source.model 101 case "com.sun.star.form.OButtonControl" 102 GetFormFromEvent = e.source.model.parent 103 case "com.sun.star.comp.forms.ODatabaseForm" 104 GetFormFromEvent = e.source 105 case else 106 msgbox e.Source.ImplementationName 107 msgbox "Unknown event in mSwitchboard.LoadMainKeywordFile" 108 msgbox "Need to look at the locals window to trace up the stack" & chr(13) & "to find the form reference." 109 End select 110 Exit Function 111 112 ErrorCheck: 113 MsgBox "Error in " & szModuleRoutineName & chr(13) & "Error Number: " & err & " " & Error$ & chr(13) & "Error Line : " & erl 114 End Function
Line 20 gives us the image file name contained in the "VideoID" field with the help of the "getColumnValue()" function that you will have pasted in earlier (code listing 3 below).
As you can see below "getColumnValue()" is just a wrapper function for "getColumnData()" below.
But again all this helps to keep the code in code listing 1 neat and readable.
Code listing 3
118 ' This is just a wrapper function to make calling getColumnData (below) simpler 119 Private Function getColumnValue(byref ds as Object, byval szColumnName as String) as Variant 120 getColumnValue = getColumnData(ds.Columns.getByName(szColumnName)) 121 End Function 122 123 Private function getColumnData(oCol) as variant 124 ' Do not return Error code from this function 125 ' do checks in calling function 126 Dim vOut as variant 127 select case oCol.TypeName 128 case "INTEGER": vOut=oCol.Int 129 case "INT" : vOut=oCol.Int 130 case "LONG": vOut=oCol.Long 131 case "VARCHAR": vOut=oCol.String 132 case "DOUBLE": vOut=oCol.Double 123 case "BOOLEAN": vOut=oCol.Boolean 133 case "DECIMAL": vOut=oCol.Double 134 case "NULL": vOut=oCol.Null 135 case "SHORT": vOut=oCol.Short 136 case "ARRAY": vOut=oCol.Array 137 case "BLOB": vOut=oCol.Blob 138 case "BYTE": vOut=oCol.Byte 139 case "BYTES": vOut=oCol.Bytes 140 case "CLOB": vOut=oCol.Clob 141 case "DATE": vOut=oCol.Date 142 case "OBJECT": vOut=oCol.Object 143 case "REF": vOut=oCol.Ref 144 case "TIME": vOut=oCol.Time 145 case "TIMESTAMP": vOut=oCol.TimeStamp 146 case else: vOut=oCol.String 147 End Select 148 getColumnData = vOut 149 End Function
Line 21 gives us the full image path of the file, given that I suggested that you create a folder called "images" in the same folder as the database, this gives you the option of having a function that will always lead you to the images folder by getting the path of this database and then concatenating the images subfolder and the filename from the database.
My function "getDbPath()" below provides the path to the database for you.
Code listing 4
44 ' This function returns the Path (as a URL, which is best for LibreOffice as this handles cross platform issues best) 45 ' meaning if you put other files in subdirectory of database file will always be in the correct place 46 Function getDbPath() as String 47 Dim szPathFile as String 48 Dim oDoc as Object 49 Dim lErr as Long 50 Dim szModuleRoutineName as String 51 52 szModuleRoutineName = "m_frmImages.getDbPath" 53 54 On Error Resume Next 55 szPathFile = ThisComponent.getURL() 56 If err <> 0 Or szPathFile = "" Then 57 On Error Resume Next 58 szPathFile = ThisComponent.Parent.getURL() 59 if err <> 0 Or szPathFile = "" Then 60 lErr = err 61 On Error Goto ErrorCheck ' Reset Error Handling 62 error(err) ' Throw Error 63 Exit Function 64 End if 65 End if 66 On Error Goto ErrorCheck ' Reset Error Handling 67 68 getDbPath = removeFileNameFromPath(szPathFile) 69 Exit Function 70 71 ErrorCheck: 72 MsgBox "Error in " & szModuleRoutineName & chr(13) & "Error Number: " & err & " " & Error$ & chr(13) & "Error Line : " & erl 73 error(err) ' Throw unresolved error up the stack 74 End Function
Line 23 from Code Listing 1 above, neatly wraps all the code in Code Listing 5 below, it handles the situation where there is no file in the images folder, even though the database says there’s an image there (we all make mistakes right?), it’ll clear the previous image off the display if there’s nothing to show for the current record.
If the file is missing the "If" statement exits the subroutine at that point.
23 if imageFileMissing(szFullImagePath, oImageControl) then Exit Sub
Code listing 5
32 Private Function imageFileMissing(byval szPath as String, byref oImageControl as Object) as Boolean 33 Dim szTest as String 34 ' Exit the code if no image file present 35 szTest = Dir(szPath, 0) 36 If szTest = "" Then 37 oImageControl.Graphic = Nothing 38 Exit Function 39 End If 40 End Function
Lines 25 through 27 (of code listing 1) utilise the included "createGraphic()" function which takes the file path to the image as an argument and works it’s magic (thanks Roberto Benitez – base programming book, linked on this page) to create a graphic object that gets passed to the image control.
The magic alluded to being Line 82 create a File Access object, Line 84 open a binary (I guess) stream object, Lines 85 & 86 put this stream object (essentially the image graphic) into an array, line 87 create a graphic provider object and line 88 pass the binary graphic object into the graphic provider and pass that back as the result of the function.
Thankfully you don’t have to memorise all that, because as an "application builder" you just need to "fit" the "createGraphic" function to your project.
Line 26 takes the result of the function and passes it to the "Graphic" property of the "oImageControl" object (it might help to have this webpage open in two windows, the other window displaying code listing 1 while you read this), this has the effect of updateing the image on your form.
Line 28 ensures that the image fills your image control as best as possible.
Code listing 6
76 Function createGraphic(byval szFilePath as String) as Object 77 Dim oSFA as Object ' SFA is Simple File Access 78 Dim oInputStream as Object 78 Dim arrArgs(0) as New com.sun.star.beans.PropertyValue 79 Dim vGraphicProvider as Variant 80 Dim szURL as String 81 82 oSFA = CreateUnoService("com.sun.star.ucb.SimpleFileAccess") 83 szURL = ConvertToURL(szFilePath) ' Built in Basic Function 84 oInputStream = oSFA.openFileRead(szURL) 85 arrArgs(0).Name = "InputStream" 86 arrArgs(0).Value = oInputStream 87 vGraphicProvider = CreateUnoService("com.sun.star.graphic.GraphicProvider") 88 createGraphic = vGraphicProvider.queryGraphic(arrArgs) 89 End Function
Once you’ve done all this you’re good to go and off to the races.
Thank you very much for the tutorial
but how to add records to the form without adding it manually in the table
i mean the form don’t let you add any image name Dynamically
and thank you again , keep the good work
That’s a different tutorial, and a different kind of form,I’ll look into doing a video and blog post on that later.
Meanwhile, check out some other LibreOffice base videos I’ve created at https://www.youtube.com/playlist?list=PLQORZjfSPqSkFSFHR32WlfXhen0C2HyAi
maybe some of those will help.
Creating a Simple form to add data is kinda straight forward once you understand using forms (datasources) explained in this LibreOffice video and other ones in that playlist relating to base forms.
Good idea think, I’ll create a video on how to do that.
Do add 2 lines about security: one has to set the security level lower or add the map to “trusted locations”. Took me some time to figure out why no images was shown, even though I coded everything right. Only after lunch, when I reopened the database, the warning message gave me the idea: security option.
Sean,
Thanks for providing just exactly what I needed to get a project going. I can see that I wouldn’t have gotten to the finish line on my own any time soon.
Note: imageFileMissing() does not set return values, so AfterRecord_change always gets a false, meaning it always thinks that the image file exists. createGraphic() blows up when it doesn’t.
HI Dave
Glad you found the tutorial useful.
How to use this to show (multiple) different image using (multiple) different image control on a same form?