| View previous topic :: View next topic |
| Author |
Message |
cazbym
Power User
Joined: 16 Mar 2009
Posts: 63
|
 Posted: Sun Apr 22, 2012 6:02 am Post subject: [SOLVED] Document Save As Name Generated From Fields |
 |
|
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 |
|
 |
cazbym
Power User
Joined: 16 Mar 2009
Posts: 63
|
| Posted: Sun Apr 22, 2012 9:04 am Post subject: |
|
|
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 |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Apr 22, 2012 9:47 am Post subject: |
|
|
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 |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
|
| Back to top |
|
|
cazbym
Power User
Joined: 16 Mar 2009
Posts: 63
|
| Posted: Sun Apr 22, 2012 10:52 am Post subject: |
|
|
Hi Dacm & RPG,
Yep - that is what I needed - so obvious 
Many many thanks - thread marked solved.
Incidentally, would anybody know if second option using SQL statement could ever have worked? |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Apr 22, 2012 12:05 pm Post subject: |
|
|
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 |
|
|
|
