| View previous topic :: View next topic |
| Author |
Message |
drfinale
General User
Joined: 25 Aug 2006
Posts: 6
|
 Posted: Sat Aug 15, 2009 10:24 am Post subject: [solved] Refresh form with Macro |
 |
|
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 |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sat Aug 15, 2009 12:09 pm Post subject: |
|
|
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 |
|
|
drfinale
General User
Joined: 25 Aug 2006
Posts: 6
|
| Posted: Sat Aug 15, 2009 6:05 pm Post subject: |
|
|
Works like a charm! Thank you!!
_________________
drfinale
Pine Hill, NJ |
|
| Back to top |
|
|
LostInBase
General User
Joined: 18 Aug 2009
Posts: 10
|
| Posted: Tue Aug 18, 2009 9:17 am Post subject: explanation of the code above |
|
|
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Aug 18, 2009 10:17 am Post subject: |
|
|
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 |
|
|
LostInBase
General User
Joined: 18 Aug 2009
Posts: 10
|
| Posted: Tue Aug 25, 2009 10:54 am Post subject: |
|
|
thanks, things make more sense now
_________________
Windows XP, Open Office 3.1 |
|
| Back to top |
|
|
ratacotufa
General User
Joined: 15 Nov 2009
Posts: 10
|
| Posted: Sun Nov 15, 2009 8:48 am Post subject: reload not working |
|
|
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Nov 15, 2009 9:33 am Post subject: |
|
|
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 |
|
|
ratacotufa
General User
Joined: 15 Nov 2009
Posts: 10
|
| Posted: Sun Nov 15, 2009 12:41 pm Post subject: |
|
|
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 |
|
|
ratacotufa
General User
Joined: 15 Nov 2009
Posts: 10
|
| Posted: Sun Nov 15, 2009 12:50 pm Post subject: |
|
|
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Nov 15, 2009 1:34 pm Post subject: |
|
|
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 |
|
|
ratacotufa
General User
Joined: 15 Nov 2009
Posts: 10
|
| Posted: Sun Nov 15, 2009 3:39 pm Post subject: |
|
|
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Nov 15, 2009 3:55 pm Post subject: |
|
|
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 |
|
|
ratacotufa
General User
Joined: 15 Nov 2009
Posts: 10
|
| Posted: Sun Nov 15, 2009 4:43 pm Post subject: |
|
|
Well, you are right. I will got to the queries forum... Thanks for your help with reloading...
Regards... |
|
| Back to top |
|
|
RDR323
General User
Joined: 19 Sep 2009
Posts: 9
Location: San Jose, Ca. USA
|
| Posted: Thu Nov 19, 2009 6:44 pm Post subject: Huh? |
|
|
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 |
|
|
|
