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] Document Save As Name Generated From Fields

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


Joined: 16 Mar 2009
Posts: 63

PostPosted: Sun Apr 22, 2012 6:02 am    Post subject: [SOLVED] Document Save As Name Generated From Fields Reply with quote

Hi all,

I have hobbled together a macro which pulls information from 3 separate fields in a form document and generates a file name. This works fine, but one of the fields is a listbox and the item I want is the actual data in the field, and not the BoundField. For some reason I am having difficulty pulling this data out. The only way I can get any data is if I use the BoundField which I do not want.

Code:

Sub   Save_With_Field_Names (oEvent As Object) 'assigned to Push Button execute event
   oForm = oEvent.Source.Model.Parent 'get the host Form object from the Push Button's Execute Event
   iID=oForm.getByName("txtTransID").getString
   iID2=oForm.getByName("txtProjectCode").BoundField.getString
   iID3=oForm.getByName("txtTransTypeID").BoundField.getString 'This field is a listbox where I would rather have displayed data than BoundField. 
         'When removing BoundField and using .text or .getString the macro fails with unknown 'element
   cFile = "C:\Users\Me\Documents" & "" & iID3 & "" & "-" & "" & iID2 & "" & "-" & "" & iID & "" & ".odt"
   cUrl = ConvertToURL( cFile )
   ThisComponent.storeAsUrl (cUrl, Array ())
End Sub


This saves the file as C:\Users\Me\Documents\6-45001-4401.odt
What I am after is C:\Users\Me\Documents\Invoice-45001-4401.odt

In desperation (this may well be overkill), I have attempted to formulate an executeQuery to pull the text required to print as a test as follows:

Code:

Sub GetTransType (oEvent As Object)
   oForm=oEvent.Source.Model.Parent
   iID2=oForm.getByName("txtTransID").BoundField.getString
   iID3=oForm.getByName("txtTransTypeID").BoundField.getString
   TestSqlResult
End Sub

Sub TestSQLResult
   oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
   oDataSource = oDatabaseContext.GetByName("MyDB")
   oConnection = oDataSource.GetConnection("", "")
   Dim strQuery As String
   Dim iID2 As String
   Dim iID3 As String
   strQuery = "SELECT ""TransTypePrint"" FROM ""Transactions"", ""TransType"""&_
   " WHERE ""Transactions"".""TransTypeID"" = ""TransType"".""TransTypeID"""&_
   " AND ""TransID"" = "& iID2 &" AND ""TransTypeID"" = "& iID3 &" "  'Fails here with "unexpected token :AND"
   oStatement = oConnection.createStatement()
   ResultSet = oStatement.executeQuery(strQuery)
   String s = Result.getString(1) 'If iID2 and iID3 in SQL above are substituted for actual data in this case 4401
'and 6 the macro also fails here with "invalid cursor state: identifier cursor not positioned on row in .........ResultSet is positioned
'before first row.
   Print s
End Sub


Can anybody help with either method?


Last edited by cazbym on Sun Apr 22, 2012 10:51 am; edited 1 time in total
Back to top
View user's profile Send private message
cazbym
Power User
Power User


Joined: 16 Mar 2009
Posts: 63

PostPosted: Sun Apr 22, 2012 9:04 am    Post subject: Reply with quote

Well - solved one problem with macro. Altered code to add WhileResultSet and Wend as apparently the cursor sits above first record and will only move onto it when next is called even if only 1 record returned.

Code:

Sub TestSQLResult
   oDatabaseContext = createUnoService( "com.sun.star.sdb.DatabaseContext" )
   oDataSource = oDatabaseContext.GetByName("MyDB")
   oConnection = oDataSource.GetConnection("", "")
   Dim strQuery As String
   strQuery = "SELECT ""TransTypePrint"" FROM ""Transactions"", ""TransType"""&_
   " WHERE ""Transactions"".""TransTypeID"" = ""TransType"".""TransTypeID"""&_
   " AND ""TransID"" = "& iID2 &" "
   oStatement = oConnection.createStatement()
   ResultSet = oStatement.executeQuery(strQuery) 'fails here with unexpected end of statement
   While ResultSet.next
   Result.getString(1)
   s = ResultSet.getString(1)
   Print s
   Wend
End Sub


Also simplified query as only TransID needed specifying.

Now if I remove the section in the strQuery "& iID2 &", and replace with actual string eg 4410, everything works as it should, but I need this part of query to be picked up programatically - or an answer to the 1st macro problem posted.

Any ideas?
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 Apr 22, 2012 9:47 am    Post subject: Reply with quote

Hello

Do you need the property currenvalue.

Code:
olistbox.CurrentValue


Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Apr 22, 2012 10:02 am    Post subject: Re: Document Save As Name Generated From Fields (one listbox Reply with quote

Specifically:
Code:
iID3=oForm.getByName("txtTransTypeID").CurrentValue

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
cazbym
Power User
Power User


Joined: 16 Mar 2009
Posts: 63

PostPosted: Sun Apr 22, 2012 10:52 am    Post subject: Reply with quote

Hi Dacm & RPG,

Yep - that is what I needed - so obvious Embarassed

Many many thanks - thread marked solved.

Incidentally, would anybody know if second option using SQL statement could ever have worked?
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 Apr 22, 2012 12:05 pm    Post subject: Reply with quote

Hello cazbym

I have the idea that the first macro cannot work. That idea makes me to write now. I think it can be real good for you to spend more time to understand SQL in stead of macros. I also did have trouble to understand SQL. In the beginning I did think it as a not so powerful language to solve problems in OOo. But with better understanding I did discover it is a real powerful language and when you understand it is much more easy then understanding macros. Understanding SQL does help you better working with the forms there forms are based on SQL. I did learn to make some views who are important for the complete database. Views are the same as queries but they are stored in the database and can be used in forms. The queries in OOo-base must be more simple then the views you can use and store in the database.

Spending time to SQL will pay back quicker then spending time to macros.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
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
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