OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

import links from spreadsheet to database forms
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Sun Jul 13, 2008 7:33 am    Post subject: import links from spreadsheet to database forms Reply with quote

Hello. I have a spreadsheet with a column called PICTURES. Each row has a plant name and a picture link which I can view by clicking on the link. How can I use a database to display the name and PICTURE on a form without having to choose "insert graphics from..." for each one. Can I use the links with the database in a similar way as I have in the spread sheet?
Thanks very much. My goal is to make a seed database for helping people to grow more of their own diets. I have a good spread sheet with hundreds of plants, now I'm trying to display the info from the spreadsheet on individual seed packages. I posted this question on June 25th and didn't get any replies so I have tried to reword it this time more clearly.
Jonathan on Cortes Island B.C. Canada.
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Jul 15, 2008 2:14 pm    Post subject: Reply with quote

Hello

Insert text by Romke wrote:
I edit this post for readers who think it is not important for them and want skip this post.

If you want use images in your database then it can be important goto page two of this thread.
It can also be important for users of OOo3.1 and higher.

Some explanation in this thread are the same for Lower and higher version of OOo3.1. But you donot need more macros for an image control in OOo3.1 and higher


Romke wrote:
There is start an other thread where is a summary of all information in this thread. You can find it here


I did have some testing and it is possible to insert pictures in a database with macro's.
The test i have done is with pictures who are saved as a normal file.You are talking about url's and I don't know how you have saved it.

I did a test with 4 pictures in a map. I give the map name to a variabele and then all the picture in that map are inserted to the database. I will help with the code if it is possible to me.

The reason that I did not send the code is I'm not sure it is usefull to you

Romke


Last edited by RPG on Sat May 08, 2010 7:24 am; edited 2 times in total
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Fri Jul 18, 2008 9:58 pm    Post subject: import links from database to spreadsheet form Reply with quote

Thank you for your reply Romke.
I will learn about macros and how to use them. The pictures that I am talking about are not however URL's. They are .jpg files saved to my hard drive.
As I am new to using open office I do not understand how you "give the map name to a variabele and then all the picture in that map are inserted to the database."

Your help is much appreciated and I look forward to learning about macros. Hopefully by the time you reply to this I will have a basic understand of what they are and how they work.
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Jul 19, 2008 1:07 am    Post subject: Reply with quote

Hello


What is the connection on this moment between the database and the picture. You are talking about an URL. Normaly it is pointing to a file. Maybe the url is in your data base. In that case you have all filenames already in your database

There are two way to do it
a) save all pictures in the database
b) save all file name in the database

Maybe you have all file names already in your database.

In both cases we need
from tables
1) tablenames
2) fieldnames

from forms
1)form names,
2)name of controls where name of file is
3)name of control where the picture is

If you want learn about macro's and forms look to a manual of
http://www.geocities.com/rbenitez22/OOo/index.html

The help files with OOo give also good information about macros

Romke
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Sun Jul 20, 2008 10:50 pm    Post subject: display hyperlinks from a spreadsheet within a database form Reply with quote

Thank you again for your reply. I live on the west coast of Canada and this could be very far from you. Nonetheless you are being very generous with your patience.
I think that there is some confusion about the kind of files that I am trying to make a link to. You say that they are URL's and I think that they are not because I read on wikipedia that URL's are "a compact string of characters used to represent a resource available on the Internet". Here is an example of one of the files that I am trying to display on the form for the Amaranth Plant: file:///Users/farmhouse/Documents/Jonathan/LANdpLAN/pictures/Amaranth-sprout.jpg This file is not on the internet.
You say that it is possible to insert pictures in a database with macro's.
I read the file 'Base Forms' that I found at the end of the link you suggested and I have a much better understanding of what macros are. I did not find anything within the document explaining how to work with pictures, OLE objects or images.
Perhaps you can answer this question: What field type should I choose when pasting a group of spreadsheet cells that include a column of hyperlinks?
You say there are two ways to do it. I would like to use the second, b) save all file names in the database. This way I can change the pictures and as long as the new picture has the same title there should be no problems.
You say we need from tables
1) table names= Plant Data
2) field names= Common name, Plant-Picture, Seed Picture and Sprout Picture
From forms
1)form names= Plant Data
2)name of controls where name of file is= I don't understand
3)name of control where the picture is= I don't understand.

I'm sorry that i do not understand all of your directions and I hope that you will elaborate.
Take care Romke
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Jul 21, 2008 4:22 am    Post subject: Reply with quote

Hello Jonathan

I live in the Netherlands. When I was young I did live on a little farm. So I knew plant for food are important. Computers are important this days but we canot eat them. I'm a homeuser and not a proffesional

I'm a person of a step by step methode. I think this can work for you.
Make sure you have a backup of the database.

The spreadsheet you have you can use as a database in OOo, without changing a lot. You can also copy them and insert a Primarykey most of the time it is a number and be sure it is autovalue. If you choose to use the sheets you cannot edit the data in the database you have to do it in the sheets.


URL
OOo give maybe a little else definition of URL then wikipeadia.
Quote:
file:///Users/farmhouse/Documents/Jonathan/LANdpLAN/pictures/Amaranth-sprout.jpg

OOo say this is a URL For a field in a database you can use a text field

The definition is not important.
Important is
a) you have a database with filenames who point to a picture
b) The picture control of OOo can display a picture if it have a filename

The only problem the picture must get the filename

How we come to a solution
Step 1 insert this code to the macros
Code:

sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
beginform=oEv.Source
Yourpicturename="ImageControl" 'Here comes the name of your control for the picture
UrlField="url" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
end sub

Step 2
Bound this macro to the form event when going to a new record. It number 13 in the list.

Step 3
Control the names if theu correct.

I think this is all to display the picture.
I knew the capabillity of the picture control is not great. For printing I do not have a solution on this moment. You are not talking about it in this thread but in the first one talk about it and that can give more problems.

Romke
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Mon Jul 21, 2008 2:27 pm    Post subject: Reply with quote

I was very excited to try your solution and I hit a wall...
Quote:
Step 1 insert this code to the macros

**I have inserted the code by opening the Database and choosing Tools - Macros - Organize Macros - OpenOffice.org Basic - New - and then I paste your code and choose file - save and file - close.
Quote:
Step 2
Bound this macro to the form event when going to a new record. It number 13 in the list.


** I have tried to do this by choosing Tools - Customize... - events. The list looks like:
Start Application
Close Application
Creat Documnt
Document is closing
Close Document
Open Document
Save Document
Save Document AS
Document has been saved
Document has been saved as
Activate Document
Deactivate document
Print document
'Modified' status was changed

Numeber 13 is Print Document and I do not find an event called new record.

I am a little bit in the dark and I am lucky if you don't mind explaining in more detail how to do this second step.
Jonathan
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Jul 21, 2008 3:48 pm    Post subject: How to bind a macro Reply with quote

Hello

I think you have inserted the macro on a wrong place. let stay there and inserted it again on the good place

what is the good place?
Close as much as possible of OOo. that make it less confusing.
But you should not close your program / form and not close the database


remember the name of your program / form

try again to save the macro. You knew how to do. But choose now the name of your program / form

If this is the first time you are working with macros there are no macros in existing macros click on new.

a new popup ask for a name accept the module1
Now you get a nearly blank window past your macro here.

Don't change the name. The reason for not changing the name is you can recognized the name if you have to choose it again..
You have now two macro's
a) main which is empty
b) begintoon

go back to your program / form

be sure that you are in edit mode


I assume you have a control for displaying the pictures. If you not have you must make a control Look in the manual of Benitz how to do

open the form navigator
Try to open the form navigator. The form navigator is not the normal navigator
You see a structuur like a map in the explorer. select the second of the top with, I execpt, the name "Standard" click with right and then on properties goto tabpages Events

You describes possible the wrong list. I hope you get now the good list where you can select "going to a new record" or "changing to an other record" . It is number 13 in the list. click on the little square right of the white field
new pop up choose macro
new pop up and now it is more difficult
a) the name of your program /form is displayed choose the name, go further with common
b) it is possible that your name is not but the name "public" choose it , go further with common
c) If both cases are not true you have to save and close your program / form and reload it. The macros are hopely displayed the second time

common
choose "Standard: , choose "module" choose "begintoon" , click several times on OK

I hope it is clear but come with new question. The first time is always difficult. I spent one day on 10 line of text to run HSQLDB as server.

I hope this bring a little light in the darkness

Good luck

Romke
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Mon Jul 21, 2008 7:56 pm    Post subject: Reply with quote

I was able to do everything you instructed except this last step
Quote:
Now you get a nearly blank window past your macro here.

I could not edit any of the text which read:
Code:
REM  *****  BASIC  *****

Sub Main

End Sub

I was able to edit the text when I saved the macro in My Macros - Standard - Module 1 and I was not able to edit the text when the macro is saved in Plant Data - Standard -Module 1
'Platn Data' is the name of my program / form.

Thank you for persevering with me.
Jonathan
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Jul 22, 2008 2:18 am    Post subject: Reply with quote

Hello

Maybe not all is good but we are on the good road.
There are three places for macro's
My macros
Openoffice Macros
and macros who belong to a file.

If you save it in My macro's that is not a problem. All other things are the same.
If you save it in the part for Openoffice it is possible that OOo is not more good working.

One different between My Macros and in a file: If you put the macro's in My macro's they will not saved automatic. You must explicit save them and maybe there is a problem with running. I'm not sure. But if it is working no problem.

Quote:

I could not edit any of the text which read:

You must open the program /form in edit mode. Right click.

I did talk about program /form the reason was this it should not confuse you about form in the form navigator. The word form is use for two things:
a) program /form or writer document
b) what you can see in the form navigator.

I hope you can see now the pictures in the database. You must make a picture control in your form. That picture control is not connect to a field in the table

I hope we will succeed

In one of the post you wrote that I did help you, while there such a great distance between us. I also get a lot of help by reading this kind of forums. I work with openSUSE linux and that depends on working together. And I believed and it is true we live in one world

Romke
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Wed Jul 23, 2008 11:47 am    Post subject: Reply with quote

Hey good news it worked! The next step I want to do is have two more pictures displayed on each form, (one for seed and one for sprout). So, can I assign two more macros to the same event? or extend the code for begintoon macro? or something else?
This is the code that worked for me:
Quote:
sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
beginform=oEv.Source
Yourpicturename="Plant-Picture" 'Here comes the name of your control for the picture
UrlField="Plant-Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
end sub

The other pictures could use code like
Code:
sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
beginform=oEv.Source
Yourpicturename="Sprout Picture" 'Here comes the name of your control for the picture
UrlField="Sprout Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
end sub
and
Code:
sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
beginform=oEv.Source
Yourpicturename="Seed Picture" 'Here comes the name of your control for the picture
UrlField="Seed Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
end sub
however I do not know how to bind multiple macros to one event. I also had the idea of grouping the codes in one macro but before I try this I want to know what you think. It may look like this...
Code:
sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
beginform=oEv.Source
Yourpicturename="Plant-Picture" 'Here comes the name of your control for the picture
UrlField="Plant-Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
dim beginformseed
beginformseed=oEv.Source
Yourpicturenameseed="Seed Picture" 'Here comes the name of your control for the picture
UrlFieldseed="Seed Picture" 'Here comes the url of the pictue out your data base
beginformseed.getbyname(Yourpicturenameseed).ImageURL= beginformseed.getstring(beginformseed.findcolumn(UrlFieldseed))
dim beginformsprout
beginformsprout=oEv.Source
Yourpicturenamesprout="Sprout Picture" 'Here comes the name of your control for the picture
UrlFieldsprout="PSprout Picture" 'Here comes the url of the pictue out your data base
beginformsprout.getbyname(Yourpicturenamesprout).ImageURL= beginformsprout.getstring(beginformsprout.findcolumn(UrlFieldsprout))
end sub
This is my guess and I don't have very much experience with programing so I will not be surprised if it is wrong. Thanks again for all of your help. My teacher Oliver Swain helped me for 15 minutes yesterday with some coding and I am very happy.
How do you think I should get these two pictures (Sprout Picture and Seed Picture) into my form?
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Jul 23, 2008 4:13 pm    Post subject: Reply with quote

Hello

I'm glad you have succeed. If you start with this kinds of thing it is always difficult.

You cannot bound two event on the same place as far as I know. but is possibly to do it in the way you want do it.

So I have only a little changed to the code.
I assume that all pictures in one form. and now I mean the navigator form and not writer document.

I have out comment the lines who I think you do not need more.
also I have change the names, there I have out comment some names.

Out comment means line do not more work.
It is difficult for me to test it, and I have not done it.

I hope in this have answered your questions of inserting the pictures

Romke

Code:
REM  *****  BASIC  *****
option explicit
'it is a good habit to start with "option explicit" If you make error in the name most of the time it give an error
' This error is easier find then a name with one charactor wrong and the program does not what you expectt.

sub begintoon(oEv as object)
'begintoon means begin-show
' Call this macro every time when you load a new reord
' bind this to the form to the event go to new record
' this is not bound to the picture control
' The picture control has a empty date field
dim beginform
dim  Yourpicturename
dim UrlField
dim Yourpicturenameseed
dim UrlFieldseed
dim Yourpicturenamesprout
dim UrlFieldsprout
beginform=oEv.Source
Yourpicturename="Plant-Picture" 'Here comes the name of your control for the picture
UrlField="Plant-Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturename).ImageURL= beginform.getstring(beginform.findcolumn(UrlField))
'dim beginformseed
'beginformseed=oEv.Source
Yourpicturenameseed="Seed Picture" 'Here comes the name of your control for the picture
UrlFieldseed="Seed Picture" 'Here comes the url of the pictue out your data base
'beginformseed.getbyname(Yourpicturenameseed).ImageURL= beginformseed.getstring(beginformseed.findcolumn(UrlFieldseed))
beginform.getbyname(Yourpicturenameseed).ImageURL= beginform.getstring(beginform.findcolumn(UrlFieldseed))
'dim beginformsprout
'beginformsprout=oEv.Source
Yourpicturenamesprout="Sprout Picture" 'Here comes the name of your control for the picture
UrlFieldsprout="PSprout Picture" 'Here comes the url of the pictue out your data base
beginform.getbyname(Yourpicturenamesprout).ImageURL= beginform.getstring(beginform.findcolumn(UrlFieldsprout))
end sub
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Thu Jul 24, 2008 7:13 am    Post subject: Reply with quote

After successfully implementing begintoon macro I cosed down my database for the night and reopened it the next morning. When I tried to open my form I got the error message:
Quote:
A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Standard.Module 1.begintoon?
language=Basic&location=document.

Message: BasicProviderImpl::getScript: no script!"

Some research on the internet showed me that the solution is to save begintoon macro into mymacros > Standard > Module1 Now the problem is solved.

Then I used the code from your last post and it worked!

THank you so much Romke. If you are interested in seeing the project I will send you a copy. You have helped me in a way that I would like to encourage and proactice. Take care. Very Happy
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Thu Jul 24, 2008 2:13 pm    Post subject: Reply with quote

Hello

It is nice you have succeed. I have send a pm with my email address and I' like to see what you have made.

I hope it working good for you. It is good to help other if it is possible.

Romke
Back to top
View user's profile Send private message
lesfrancoeurs
General User
General User


Joined: 14 Jun 2008
Posts: 25
Location: Student

PostPosted: Fri Aug 22, 2008 10:08 pm    Post subject: Do you have any new information that cna help me print? Reply with quote

Hello Romke. Finally everything is the way it should be and the forms look perfect. Then I tried to print one and as you said earlier there was a problem. The pictures were very very small in the print out.
If you learn something new please let me know.
Bye for now.
_________________
JOnathan
Immersion is a great way to learn how to grow all our own food.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3  Next
Page 1 of 3

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group