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

[solved] Refresh form with Macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
drfinale
General User
General User


Joined: 25 Aug 2006
Posts: 6

PostPosted: Sat Aug 15, 2009 10:24 am    Post subject: [solved] Refresh form with Macro Reply with quote

I have a database that is used to create shopping lists for grocery stores. In my main table "Items," I have two fields that are automatically updated to default values when called by a pushbutton. The code for the macro is below:

Code:
Sub ClearNeeded

Dim Context
Dim Database
Dim Connection
Dim Statement
Dim Result
Dim SQL As String

Beep
If MsgBox ("This will clear the shopping list.  Are you sure you want to do this?", 292, "Clear List?") = 6 Then

Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
Database=Context.getByName("Groceries")
Connection=Database.getConnection("","")
Statement=Connection.createStatement()

SQL="UPDATE ""Items"" SET ""ItemNeeded"" = 0, ""ItemQty"" = 1"
Result=Statement.executeQuery(SQL)

Connection.close()

Else

EndIF

End Sub


This works great, except I have to manually refresh the form in order to see the updated data. Is there a way to program a form refresh into this macro? Thanks for your help!

-drfinale
_________________
drfinale
Pine Hill, NJ


Last edited by drfinale on Sat Aug 15, 2009 6:05 pm; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
RPG
Super User
Super User


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

PostPosted: Sat Aug 15, 2009 12:09 pm    Post subject: Reply with quote

Hello

You asked how can I update my form when I have change the table with a macro?
The instruction you need is simple: oForm.reload. But this is not easy to implement in your code. For that reason I did make two macros. One macro is a little demonstration on how it works. The other macro is for use in your form.
I think this code is a little more easy.

I did also change executeQuery in ExecuteUpdate. This can give back how much record there are changed.
for more in formation look:
http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XStatement.html

most important part
Quote:
either the row count for INSERT, UPDATE or DELETE or 0 for SQL statements that return nothing


Limitation
In the code I wrote there is a limitation. It is only usable for a table in the same database where the form to is connect.. Most of the time this is not a problem

Romke

Code:

Sub Main (oEvent as object)
' This event is bound to a button
' It is the event before commencing that is the first in the list.
' I do assume that there is a buttom in your form
'print oEvent.source.model.parent.name ' This instruction give you the name of your form
dim oForm,oConnection,oStatement,sSQL,oResulset
oForm=oEvent.source.model.parent ' Now you have the object of your form
'This  give you some service,methods and interface, who you can use.
'One of them is ActiveConnection
Beep
If MsgBox ("This will clear the shopping list.  Are you sure you want to do this?", 292, "Clear List?") = 6 Then
   oConnection = oForm.ActiveConnection ' Use the connection of your form
   oStatement=oConnection.CreateStatement ' Make ready for statement
   sSQL="call  database()" ' Ask for the name of your database
   oResulset=oStatement.executeQuery(sSQL)
   oResulset.next
   print oResulset.getString(1) ' print the name
   oForm.reload
end if
End Sub




Code:
Sub Main2 (oEvent as object)
' This event is bound to a button
' It is the event before commencing that is the first in the list.
' I do asume that there is a buttom in your form
'print oEvent.source.model.parent.name ' This instruction give you the name of your form
dim oForm,oConnection,oStatement,sSQL,oResulset
oForm=oEvent.source.model.parent ' Now you hav the object of your form
'This  give you some service,methodes and interface, who you can use.
'One of them is ActiveConnection
Beep
If MsgBox ("This will clear the shopping list.  Are you sure you want to do this?", 292, "Clear List?") = 6 Then
   oConnection = oForm.ActiveConnection ' Use the connection of your form
   oStatement=oConnection.CreateStatement ' Make ready for stement
   sSQL="UPDATE ""Items"" SET ""ItemNeeded"" = 0, ""ItemQty"" = 1"
   print Statement.executeUpdate(SQL) ' can give back a number depend on the action
   oForm.reload
end if
End Sub
Back to top
View user's profile Send private message
drfinale
General User
General User


Joined: 25 Aug 2006
Posts: 6

PostPosted: Sat Aug 15, 2009 6:05 pm    Post subject: Reply with quote

Works like a charm! Thank you!!
_________________
drfinale
Pine Hill, NJ
Back to top
View user's profile Send private message Visit poster's website
LostInBase
General User
General User


Joined: 18 Aug 2009
Posts: 10

PostPosted: Tue Aug 18, 2009 9:17 am    Post subject: explanation of the code above Reply with quote

I've been trying to learn some programming syntax from studying the code above (both question and answer postings), as I am trying to do something very similar, however I cannot figure out what an "oEvent" in:
Sub Main2 (oEvent as object)
is and how to use it. I guess that this is the code needed to activate the macro via some action like a mouse click. I am completely new to BASIC programming and OpenOffice in general.
_________________
Windows XP, Open Office 3.1
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 Aug 18, 2009 10:17 am    Post subject: Reply with quote

Hello

I will try to explain what happens it the sub

The oEvent is a variable who points to an object. You can only be sure which object it is when you understand the program not only the code you see, but there is some help. you can do
print oEvent.source.implementationname. This give you back how you can use this object.

I told the event is bound to a button in a form. When the action happens then this sub is called. It give then also the object from that button to this sub.

Now I must knew what kind of action I want do. In this case only update a table and then reload the form.

I will and connot explain every thing but in case this there is only one caller event for this sub but you can do more then one.

Understanding the event is real important there OOo is full of all kinds of events.

I hope this makes it a little more clear to you. Feel free to ask if it is not clear.

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


Joined: 18 Aug 2009
Posts: 10

PostPosted: Tue Aug 25, 2009 10:54 am    Post subject: Reply with quote

thanks, things make more sense now
_________________
Windows XP, Open Office 3.1
Back to top
View user's profile Send private message
ratacotufa
General User
General User


Joined: 15 Nov 2009
Posts: 10

PostPosted: Sun Nov 15, 2009 8:48 am    Post subject: reload not working Reply with quote

Hi guys I have been looking this thread by two days now... I am starting with OOo base and of course now I have to deal with macros.

I was looking for the macro to reload or refresh a form; In my application I have a main form with 3 sub forms, I am making a database for storing personnel CVs.

My problem is basically to refresh or reload the main form after I added new records to the tables feeding the comboboxes...

I am thinking to use some of the property of the main form (something like when getting activated or something like that) to update the complete form each time it receives the focus...

I am starting whith a buttom in order to test the macro. I implemented this macro based inthe macro posted by RPG

Sub Refresca (oEvent as object)
dim oForm
oForm=oEvent.source.model.parent ' Now you have the object of your form
oForm.Reload
End Sub

When I push the button the macro run, it does not give me any error message but it does not reload the form; I have to go the reload button inthe tool bar in order to refresh the information...

Can you help me tofind the solution?

I also would like to know if you know some page were I could learn about the methods, objects, syntaxis, etc...

Thanks in advance
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: Sun Nov 15, 2009 9:33 am    Post subject: Reply with quote

Hello

It look to me that your problem is not the same as the problem describe in this thread. It is also difficult for me to say what you need but I think you need combobox.refresh. It can be possible you also form.reload. I cannot tell what the order must be for those two commands. I have not test this code.

Code:
Sub RefreshComboBox (oEvent as object)
dim oComboBox
oComboBox=oEvent.source.model ' Now you have the object of your form
'or te next line
'oComboBox=oEvent.source ' Now you have the object of your form
' I do always test with this line of code or the next line most both
print  oComboBox.name ' You get the name of the control which you want and can check if  you are correct coorect.
print  oComboBox.implementationname
oComboBox.Refresh
End Sub
Back to top
View user's profile Send private message
ratacotufa
General User
General User


Joined: 15 Nov 2009
Posts: 10

PostPosted: Sun Nov 15, 2009 12:41 pm    Post subject: Reply with quote

Hi RPG, thanks for your help. The way you say could work, but I have 4 comboboxes and also the 3 subforms that must be reloaded. I think the way you tell me I have to implement this reload macro for each combobox, so I have to repeat at least 4 times this macro; and I would also have to reload the 3 subforms individually, so I would have to implement 4+3=7 control properties to reload the entire main form...
I think it coulb be better just to implement the reload form macro to only one main form property, for example at load. This way each time the main form get active (for example when you close the popup wiht one of the comboboxes information) the entire main form is reloaded or refreshed.
On the other hand, I am not sure if there is such properti in a form like when getting active or when getting focus....
So I am looking for the way to refresh the main form each time it gets active...
Did I explain myself?

Thanks a lot in advance...
Back to top
View user's profile Send private message
ratacotufa
General User
General User


Joined: 15 Nov 2009
Posts: 10

PostPosted: Sun Nov 15, 2009 12:50 pm    Post subject: Reply with quote

Guys, do you see some reason for this code to fail the uploading???

Sub Refresca (oEvent as object)
dim oForm
oForm=oEvent.source.model.parent ' Now you have the object of your form
oForm.Reload
End Sub

Thanksin advance....
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: Sun Nov 15, 2009 1:34 pm    Post subject: Reply with quote

Hello

Try first to get a working sytem and later do it better.

It is most of the time wrong doing what is clearly the best without understanding how it works.
The code you present is good, But how can we say you reload the good tables. You are the only one who can tell it and test it.

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


Joined: 15 Nov 2009
Posts: 10

PostPosted: Sun Nov 15, 2009 3:39 pm    Post subject: Reply with quote

Hi there, thanks for your soon response... I implemented as you say for the comboboxes and is working just fine...
Nevertheless, I think the way I wanted should be more efficient. Basically I need to the same action that the refresh button in the tool bar does... any idea about how to do it??
On the other hand, I am now in the reporting; first of all, I need to run a query to get all the information from many tables. I call the report from a form, and my key field for the report's query is the content of a control in the form... So the question here is:
How can I use the control in the form for the filter in the query? On other word, How can I use the content of that control in the query criteria????

Thanks a lot in advance...
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: Sun Nov 15, 2009 3:55 pm    Post subject: Reply with quote

Hello

The button you mean does a reload on all tables and queries in your form. You have to find out which form or combobox must be reloaded. And I don't see your form or code so I cannot say anything. Efficient means not always less code. The meaning of efficient can differ from time to time and I think on this moment efficient means less processor load. And that means on this moment reload those tables you need.

And for other questions start a new thread there you not on topic.

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


Joined: 15 Nov 2009
Posts: 10

PostPosted: Sun Nov 15, 2009 4:43 pm    Post subject: Reply with quote

Well, you are right. I will got to the queries forum... Thanks for your help with reloading...

Regards...
Back to top
View user's profile Send private message
RDR323
General User
General User


Joined: 19 Sep 2009
Posts: 9
Location: San Jose, Ca. USA

PostPosted: Thu Nov 19, 2009 6:44 pm    Post subject: Huh? Reply with quote

I have found that the sample code provided never works"AS IS". Besides changing the name of the tables - forms-etc that are referenced there is obviously other things that need to be changed to what the refer to...... hummmm, Hard to 'splain. Example: ""ItemNeeded"" in macro below. How do I know what I should change it to exactly or maybe I don't change it, it is code and is (says) what it needs to be/say? For 'us' Newbies,this actualy applies to almost every word below. Maybe if the sample text to be replaced was color coded or Italic bold or a XXX111XXX entry was used and a key was provided saying "XXX111XXX is where you enter the name of the table, XXX222XXX is the name of your form" etc.?

If MsgBox ("This will clear the shopping list. Are you sure you want to do this?", 292, "Clear List?") = 6 Then
oConnection = oForm.ActiveConnection ' Use the connection of your form
oStatement=oConnection.CreateStatement ' Make ready for stement
sSQL="UPDATE ""Items"" SET ""ItemNeeded"" = 0, ""ItemQty"" = 1"
print Statement.executeUpdate(SQL) ' can give back a number depend on the action
oForm.reload
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
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