[Solved] Select a field with a macro

Creating and using forms

[Solved] Select a field with a macro

Postby evwool » Sun May 30, 2010 1:36 am

My existing macro (attached to the After Update event of the 'Email' field) tells me if that data is already in the table but then the cursor goes on to the next field. Is there a way, in Basic Code, to set the cursor back to the field which I've just left?
Last edited by evwool on Thu Jun 03, 2010 12:30 am, edited 1 time in total.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby Arineckaig » Sun May 30, 2010 11:34 am

Without a sight of your existing code it is not possible to give a simple answer. My apologies if the following general explanation merely confuses. The Model/View paradigm of the OOo API is powerful and flexible but not overly simple.

To set the focus to any particular Form Control it requires use of its View as well as its Model.

“SetFocus” is a method that is only supplied by the View of the Form Control. The View originates from the “CurrentController” property of the Form Document (the Writer document that contains both form(s) and form controls). If the “getControl()” method is applied to this “CurrentController” property it will generally supply a View. To get the View of a specific Form Control the “getControl()” method, however, requires the Model of the relevant Form Control as its parameter - “getControl(oControlModel)”. As your current macro is triggered by an event of that Form Control its Model can have been supplied by the Model property of the event's source.

Thus the code will look something like this:
oControlView = oFormDoc.CurrentController.getControl(oControlModel)
oControlView.setFocus()

Edit: I should have mentioned that the Form Document (Writer document) is obtained either from "ThisComponent" or more reliably but less easily by cycling up the parentage properties of the Event Source Model until the "ImplementationName" of the object is "SwXTextDocument". Essentially easier to demonstrate when it is known where the event source and/or Form Component are located.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby evwool » Sun May 30, 2010 4:10 pm

I'm not being coy about the code Arineck, it's just very long and involved with used and unused variables because, although I need to use the db as it is, I am still working on the code. If it helps, and you have the patience to read it, I'm happy to post it here of course
I'm not quite there yet. I think it's because I don't understand the difference between
oEvent.Source.Parent
and
oEvent.Source.Model.Parent
What is the Model thing about?
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby evwool » Sun May 30, 2010 4:45 pm

OK You asked for it :D (but I won't blame you if you don't tackle it):
The database has 2 main tables (plus several lookups). I use one table for typing in Email addresses and other contact details. I have 2 fields in it, one for the part before the @ sign and one for the part after the @. (Saves my rather weak right little finger from pressing the Shift key to type @). I use the other to paste in emails from documents etc so it doesn't have 2 fields, then I append emails from this latter table into the former, separating the email address into BefAt and AftAt in my query which does the appending. I'm working on the form which contains the pasted emails. I want to check, before I've finished entering the rest of the details in that row, if that email has been entered already in either table. I also check if the email I pasted didn't have an @ sign and if I pasted in trailing spaces.
The Main Form uses Villeroy's Lookup Table to filter the subform which contains a Table Grid where I type in the data. The field with the email addresses and the Control are both called Email
Code: Select all   Expand viewCollapse view
Sub CheckForDuplicateEmail (oEvent As Object)
'by EW
'FrmWebMail
'check if an item is already in a table
'assign to afterupdate event of
'control that may contain duplicate data
'EW
Dim oDoc as Object
Dim oDocView As Object
dim oControl as Object
Dim MyTableGrid As Object
Dim MySubform as Object
dim MyFormObj as Object
Dim FormFrame As Object
Dim FormPage As Object
Dim oControlName As String
Dim oControlView As Object
Dim oField As Object
rem Object variable for form
Dim MySubformName as String
Dim MySubDoc As Object
dim MyForm as Object
Dim MyFormName As String
Dim MyGridName As String
Dim MyTableControl As Object
dim MyStatement as Object
rem the SQL
Dim MyCount As String
Dim ReadField As Object
Dim ReadFieldName As String
Dim ReadData As String
Dim MyQuery AS Object
Dim TargetTable As String
'for checking TblEmail2
Dim TargetField As String
Dim TargetTable2 As String
Dim TargetField1 As String
Dim TargetField2 As String
Dim MyCount2 As String
Dim ReadData1 As String
Dim ReadData2 As String
Dim FindAt As Integer

ReadFieldName = "Email"
rem Field in my table grid
TargetTable = "TblWebMail"
rem the table against which I want to check my data
TargetField = "Email"
'variables for search in TblEmail
TargetTable2 = "TblEmail2"
TargetField1 = "BefAt"
TargetField2 = "AftAt"

oControl=oEvent.Source
'oControl is the 'field' with the AfterUpdate Event
oControlName = oControl.Name
'this may or may not be the same
'as the name of the field
MyTableGrid = oControl.Parent
'my control is in a table grid
MyGridName = MyTableGrid.Name
'MainForm_Grid
MySubform = MyTableGrid.Parent
'the table grid is in a subform
MySubformName = MySubform.Name
'MainForm (it used to be my Mainform before I added the Lookup Form to filter it)
MyFormObj= MySubform.Parent
' the main form of the subform
'in which the Table Grid sits
MyFormName = MyFormObj.Name
'FrmLookup
FormFrame = MyFormObj.Parent
'the collection of forms
'Implementation Name in MRI is oFormsCollection
FormPage = FormFrame.Parent
'This contains all the controls
'Implementation Name in MRI is SwXTextDocument
'oControlView = FormPage.CurrentController.GetControl(TableGridDailyTotals)
'I'm getting an Object Not Defined Error in the above line
MyForm = ThisComponent.Drawpage.Forms.getbyName(MyFormName)
'Subform as a document containing controls
' rather than a data holder?

ReadData = oControl.Text
   If Len(ReadData)<>Len(Trim(ReadData)) Then
      MsgBox "Remove trailing spaces"   
      'I'd like to remove the trailing spaces
      ' using   ReadData = Trim(ReadData)
      'and
      're-enter the email without the spaces
      'not working yet
      'failing that, I'd like to
      'set focus on the Email control again.
   Exit Sub
   End If
ReadData = Trim(ReadData)
'Trim string here so FindAt works
FindAt = Instr(1,ReadData,"@")
If FindAt = 0 THEN
MsgBox "This isn't a real email address"
Exit Sub
End If

MyStatement = MyFormObj.ActiveConnection.CreateStatement
rem give the variable place for statement
MyCount = "SELECT """ & TargetField & """"
MyCount = MyCount & " FROM """ & TargetTable & """"
MyCount = MyCount & " WHERE """ & TargetField & """ = '" & ReadData & "'"
MyQuery = MyStatement.ExecuteQuery(MyCount)
   If MyQuery.Next = True Then
      MsgBox "Email already in table"
      'tidy up
      MyQuery.Close
      MyStatement.Close
   'I'd like to focus back on the ReadField control
   'but no joy so far
   'oDocView = ThisComponent.CurrentController.GetControl(MyTableGrid)
   'oDocView.SetFocus
'oDocView.SetCurrentColumnPosition(1)

   Else
      'close existing query and statement
      MyQuery.Close
      MyStatement.Close
   'it isn't in FrmWebmail
         'so check in TblEmail
         'where the email address is divided into
         'before and after the @ sign
      ReadData1 = Left$(ReadData,FindAt-1)   
      ReadData2 = Mid(ReadData, FindAt+1)
      

      MyStatement = MyFormObj.ActiveConnection.CreateStatement

      MyCount = "SELECT """ & TargetField1 & """, """ & TargetField2 & """"
      MyCount = MyCount & " FROM """ & TargetTable2 & """"
      MyCount = MyCount & " WHERE """ & TargetField1 & """ = '" & ReadData1 & "'"
      MyCount = MyCount & " AND """ & TargetField2 & """= '" & ReadData2 & "'"
      MyQuery = MyStatement.ExecuteQuery(MyCount)
            If MyQuery.Next = True Then
               MsgBox "Email already in table"   
               'so here, I want to reselect the Email field
               
               MyQuery.Close
               MyStatement.Close
            Else
                  'Tidy up
               MyQuery.Close
               MyStatement.Close
            End If   
               
   End If
End Sub
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby Arineckaig » Sun May 30, 2010 7:40 pm

What is the Model thing about?

To answer the simple question first I would refer you to the BASIC guide at:
http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide/Working_With_Forms

I read that chapter several times and if I ever understand it properly it will give me the key to programming Form Controls.

I will examine your code and try to come back with some suggestions.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby Arineckaig » Sun May 30, 2010 8:09 pm

After a quick look at your code I offer some off-the-cuff suggestions.

Your code has
Code: Select all   Expand viewCollapse view
oControl=oEvent.Source

It would appear that oControl is the Form Control which triggered the event and to which you wish to return the focus.

EDIT: I overlooked the complication that arises by which event it is that triggered your Macro. If it was the After Updating event oEvent.Source already supplies the Model, so the next step is not required (though it is if it was triggered by the Changed or by Text Modified events).

So to get its Model add this code:
Code: Select all   Expand viewCollapse view
oControlModel = oControl.Model


Then the simplest further additions to your code might be no more than:
Code: Select all   Expand viewCollapse view
oControlView = ThisComponent.getCurrentController()
oControlView.getControl(oControlModel).setFocus()


Edit If After Updating was the triggering event the second of these two line should merely read:
Code: Select all   Expand viewCollapse view
oControlView.getControl(oControl).setFocus()


That should work if ThisComponent represents your (Writer) Form Document. Quite likely it will fail as I must confess I need to study your code with more care to get a better understanding of how many generations there may be between the Form Control (oControl) and the Form Document. Meanwhile the errors that arise from trying the suggestions above may give you some pointers to what is required.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby evwool » Sun May 30, 2010 10:04 pm

Thanks for having a go at this, Arin. The code is triggered by the After Update event so the oControl.Model, as you said, gives an error message.
I've set the object variables thus:
Code: Select all   Expand viewCollapse view
dim oControl as Object
Dim oControlController As Object

oControl=oEvent.Source
oControlController = ThisComponent.GetCurrentController()

ok up to here
Code: Select all   Expand viewCollapse view
ReadData = oControl.Text
   If Len(ReadData)<>Len(Trim(ReadData)) Then
      MsgBox "Remove trailing spaces"

was working before so now I've added the line here
oControlController.GetControl(oControl).SetFocus()

But this last line gives an error message, Object Variable Not Defined.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby Arineckaig » Mon May 31, 2010 12:20 am

I need more time to examine your code. Alas, I am fully committed for the next couple of days. In the meantime hopefully a better guide than I will give you the answer.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby evwool » Mon May 31, 2010 12:32 am

Thanks so much for taking the trouble to even look, Arin. It would have daunted me.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby Arineckaig » Mon May 31, 2010 1:08 am

Can you confirm where the Form Control to which you have set the Update action is located. If the control to which you wish to set the focus is an item in a Table/Grid control the code I have suggested will not work.

You would need something along the lines indicated at:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=39&t=24051#p109758
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby evwool » Mon May 31, 2010 9:43 am

Hi Arin, yes it is a control in a Table Grid. I had a horrible suspicion that this was what was causing the problem. Thanks for giving it a go, though. I've been having a look at the Columns properties but although code allows me to read the columns and looks as if it will let me select something, it doesn't actually do anything.
Code: Select all   Expand viewCollapse view
   oDocView = ThisComponent.CurrentController.GetControl(MyTableGrid)
   oDocView.SetFocus
'focus on the Table Grid
'I don't know if this is necessary
   oDocView.SetCurrentColumnPosition(1)

' don't know if this actually selects the column but I can't get the cursor to the column I want, whichever index number I type there.

I think I need to experiment with very simple test database first, with a very simple criteria to trigger the code then there'll be a fighting chance of spotting what happens. Of course, if I do find a solution, I will post it
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby RPG » Mon May 31, 2010 12:07 pm

Hello

I did test it and I do get the same problem as you have.

On this moment I do think to an other solution with a combobox what can do the searching but I do not know what you want. Must you be sure there is a value or must there not be the same value.

In a small test I see that you have less problem when you use the event before update and do there testing.
result the function with true when you agree and false with not agree.

So use a function and not a sub. See your help file for the event.
In this function you can also remove spaces

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RPG
Volunteer
 
Posts: 2210
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Select a field with a macro

Postby evwool » Tue Jun 01, 2010 8:07 am

Hi RPG, I've never figured out the difference between a function and a sub in BASE. Functions in base just seem a way of 'tidying up' coding when you want to perform the same set of actions repeatedly. However they can make it harder for a beginner (and me) to follow the coding. In Access, Functions can be used in Queries, allowing the user to take advantage of VBA functions as well as Access's Query functions but I haven't seen them being used for that in BASE. How would a function help in a way that a sub doesn't? I would like to remove the trailing spaces rather than just checking for them but then I can't seem to re-enter the amended text

I tried out the Before Update when I started this (can't remember what I did though - it was some time ago) and didn't have much luck with it, it seemed to 'get stuck' inside the control; perhaps I can make it work now. If there is some kind of CurrentControl property then I could use that, if Before Update works. I'd certainly like the idea of removing trailing spaces with it.

My code checks that the user isn't typing in an email which has been entered already. I want to avoid duplicate emails. Although my Table has an index which stops the user when he reaches the end of the row, I want this check to happen before that. Also, as I said, the emails are being entered into 1 of 2 tables so even if the email hasn't been entered into this table, I want to make sure that it hasn't already been entered into the other.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby RPG » Tue Jun 01, 2010 11:39 am

Hello

Function
The difference between a function and a sub is not so big. The function returns a value which can be used for an other purpose, maybe an other function. When you want have back the value then you must give that value to the functionname in that function.

evwool wrote:Functions in base just seem a way of 'tidying up' coding when you want to perform the same set of actions repeatedly.

Function and sub can both be used for reapeting action. I think for the most users and also for me it is more important to understand what I call the programming idea of OOo then how to code. I see in your problem when you use the good event then it is more easy to find a solution. I did not expect that.

evwool wrote:How would a function help in a way that a sub doesn't?
In your case. When you test the value you give in an event before updating then you can test the value in the field and return to the program true or false.
true when it is good
false when it is bad.
The result for the person who input data is:
you stay in the same field with wrong data and go to the next field when it is good.

evwool wrote:I would like to remove the trailing spaces rather than
This can also better be done before updating then after updating.

Solution
I think I have a solution for all your problems in the code I have add. Also I have add a line what can change a space for an at. Then you can easy type.
I do use a combobox but that is not so important you can use your queries.
when you use a combobox in the same way as I do then the combobox must contain a SQL line for filtering. That SQL line I change partly in the macro.

I think the most important is using the before update event. Do a little test with the small code for understanding. There is a complicating factor there you use two fields for the email address. You better use one field we can not so easy change the focus. But I have a solution for it.

I hope this answer the question but I realize that not all thing are clear.

Code for testing
Code: Select all   Expand viewCollapse view
function zoekuit_test (oEvent as object)
'Bind this function to the control what you want test
' out comment one of the next lines
'zoekuit=true ' See what happens
zoekuit_test=false ' see what happens
end function



Code for a combobox with query in the SQL
Code: Select all   Expand viewCollapse view
Option Explicit  'add this line as the first code line in your module

function zoekuit (oEvent as object)
' Bind this function to the control what you want test
   dim oControl,oNextControl,sCurrentfieldname
   oControl=oEvent.source  '.model
   dim sValue
   sValue=trim(oControl.text) ' trim spaces
   oControl.text=replace (svalue," ","@") 'replace a space with a at
' This can be shorter but only for me clear reason this is long but any body can change it.
   if len(oControl.tag)=0 then
         sCurrentfieldname=oControl.datafield
   else    sCurrentfieldname=split(oControl.tag,",")(0)
   end if
'
   call InitQueryComboBox02 oControl,oControl,sCurrentfieldname

if ubound(oControl.StringItemList)= -1 then
   zoekuit=true ' There is nothing
else zoekuit=false :beep
end if
end function


sub InitQueryComboBox02(oControl as object,oNextControl as object,sField)
dim oComboBox ',oForm,aComboBoxGroup()
dim nPlace,nPlace2
' We try to find the place where we must change the SQL
nPlace=instr(oNextControl.listsource(0),"WheRe") ' First pass Where
nPlace=instr(nPlace,oNextControl.listsource(0),sField) ' Then the field name
nPlace=instr(nPlace,oNextControl.listsource(0),"=") ' Then the separation char
nPlace=instr(nPlace,oNextControl.listsource(0),"'")+1 ' Then the opening single quote
nPlace2=instr(nPlace,oNextControl.listsource(0),"'") ' Then then closing single quote

select case sgn(nPlace-nPlace2)
   case 1 : print "Warempel" : end
   case 0 : oNextControl.listsource(0)=left(oNextControl.listsource(0),nPlace-1) & lcase(oControl.currentvalue) & mid(oNextControl.listsource(0),nPlace)
            oNextControl.refresh
   case -1 : oNextControl.listsource(0)=replace(oNextControl.listsource(0),mid(oNextControl.listsource(0),nPlace,nPlace2-nPlace), lCase(oControl.currentvalue))
            oNextControl.refresh
end select

end sub


 Edit: I have edit some typos 
Last edited by RPG on Sun Mar 20, 2011 1:12 pm, edited 1 time in total.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RPG
Volunteer
 
Posts: 2210
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Select a field with a macro

Postby evwool » Tue Jun 01, 2010 2:09 pm

Hi RPG. Thanks for having a go at this but I can't follow your code. The functions calling to each other and the sub calling the function do make it hard for me to read. Could you do me a big favour and give a very simple example, using a text box bound to a field, rather than a combo box containing SQL (since I suspect that a combo box has properties which a text box doesn't).
Could you let the code just
1. Test for trailing spaces in the text box
2. Remove them
3. Put the edited text back in the text box.
It is step 3 that is causing me difficulties.

Remember, the text box is in a table grid in a sub-subform.
I do like your idea of using a space instead of 2 fields. One of the reasons I kept the 2 fields was so that I could sort by the domain portion of the email or by the first part (in the case of generic email addresses like aol.com but I suppose I could do that in a query. A space is certainly easier to type than a tab.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby RPG » Tue Jun 01, 2010 5:24 pm

Hello

In this post there is code for learning how functions can work. Also I have made a new example special for a textbox as you use. I have test it in a gridtable. I have also make that there are only lowercase characters in the email. The other questions are already answered in the macro what I give to you.

sValue=trim(oControl.text) ' trim spaces
oControl.text=replace (svalue," ","@") 'replace a space with a at and place it back in the control
There is no test only it trims the strings from leading and trailing space always.
The second line change a space in the string with an at.
It also place it back in the control.

I think for you on this moment the best is do test how functions work. Understand the example I have done in this post.
Second test If you can handle the two little examples with false and true. Then you see the difference.
When that is working then go the greater part.

I have done this all in an gridtable. When you want use only a text box then
1) use my idea.
2) use partly my code
3) use partly your code.

' This code is for learning how function can be used. Hello world is a standard way for learning a basic (fundamental) part
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
option explicit
sub test_one
print "hello world"
end sub

sub test_two
print help_function("world" , "Hello" )
end sub

function help_function(valueone,valuetwo)
dim total
Total=valuetwo & " " & valueone  ' concat the two string together and add between them a space
' Init the return value of the function
help_function=total ' This is not calling the function but give the return value.
end function

' from the help file
'The same process applies to FUNCTIONS.
' In addition, functions always return a function result.
' The result of a function is defined by assigning the return value

' When a functionname is on the side of the equal sign it not calling that function it get then the value of
' the part what is on the left side.
'to the function name:





This code can be used for a textbox the query must be adjust .

Code: Select all   Expand viewCollapse view
function zoekuit_textbox (oEvent as object)
   ' Bind this function to the control what you want test
   dim oControl,oNextControl,sCurrentfieldname
   dim sValue,oForm
   dim oStatement,oResulset,sSQL
   oControl=oEvent.source
   sValue=lcase(trim(oControl.text)) ' trim spaces and make lower case
   oControl.text=replace (svalue," ","@") 'replace a space with a at
   'oForm=oControl.parent.parent ' when this works then use this line
   oForm=thiscomponent.drawpage.forms.getbyname("Standard") ' give your name up here
   ' The lower is used for searching only lower case. It is not basic but SQL
   sSQL="SELECT ""plaats"" FROM ""client"" where lower(""plaats"") = '" &_
   oControl.text  & "'"
   oStatement=oForm.ActiveConnection.CreateStatement
   oResulset=oStatement.executeQuery(sSQL)
   dim doe
   doe=oResulset.next ' returns a false when there is no result
   if doe then
      zoekuit_textbox= false ' There is already an value there
      beep ' for warning user
   else
      zoekuit_textbox= true ' write it away
   end if
end function
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RPG
Volunteer
 
Posts: 2210
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Select a field with a macro

Postby evwool » Tue Jun 01, 2010 9:53 pm

Thanks for your patience, RPG. Your suggestions are, as always, invaluable and instructive.
I've put the code in the Before Update event. You pointed me right at the missing piece of the jigsaw with this line:
oControl.text=replace (svalue," ","@")
I assumed that oControl.text was read-only. I didn't know it could be used to write to the control
So now, for example, to remove trailing spaces, I could use something like:

Code: Select all   Expand viewCollapse view
Sub CheckBeforeUpdate (oEvent As Object)
Dim oControl As object
Dim MyText As String
Dim NewText As String
oControl = oEvent.Source
MyText = oControl.Text
If Len(MyText) > Len(Trim(MyText))Then
'I have trailing spaces
NewText = Trim(MyText)
oControl.Text = NewText
End If
End Sub

That's fantastic. I can perform all sorts of other stuff on the text and then enter it when it's how I want it to be I'll be very happy to drop the 2 table idea, I love that idea of using a space instead of the @. The Replace function you suggested will do the trick nicely.
When the code edits the text box it works perfectly and quietly and the tab simply moves me to the next column in the grid
However, I still have a problem with selecting controls when I need to display an error message. For instance, I don't want to merely delete the email if it is a duplicate, I want the user to have a look at the email and then decide why it has been entered twice but when the warning message box appears, after I've clicked on it, my cursor ends up in some control in the main form, so I'll still need to work on that but thanks for what you've done, and thanks to Arin too.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Select a field with a macro

Postby Arineckaig » Tue Jun 01, 2010 11:54 pm

Remember, the text box is in a table grid in a sub-subform.

Setting focus to a particular box/cell in a grid/table form control is relatively straight forward PROVIDED a particular rowset has already been selected. The following code is merely illustrative and is triggered by a button to select the cell in the third column of the grid control's current record.

Code: Select all   Expand viewCollapse view
Sub ButtonGrid         REM Called by Button Click
     oDrawPage = ThisComponent.GetDrawPage()
     oForms = oDrawPage.getForms()
     oForm = oForms.getByName("MainFormName")
     oSubForm = oForm.getByName("SubformContainingGridName")
     oGridModel = oSubForm.getByName("TableControl")
     oGridView = ThisComponent.CurrentController.GetControl(oGridModel)
     oGridView.setCurrentColumnPosition(2)
     Wait 500   REM delay required to set focus after move
     oGridView.setFocus()
End Sub


If no record is currently selected it can set by applying a Method to oSubForm: i.e., the form that contains the grid form control.

I would hesitate to use a particular cell in a grid control as an event source. It tends to have severely limited properties: for example its ImplementationName and Parent properties both tend to throw errors. I had no luck with the following part of your code:
Code: Select all   Expand viewCollapse view
oControl=oEvent.Source
'oControl is the 'field' with the AfterUpdate Event
oControlName = oControl.Name
'this may or may not be the same
'as the name of the field
MyTableGrid = oControl.Parent
'my control is in a table grid
MyGridName = MyTableGrid.Name


Later edit: I should add some further explanation though my understanding of the API is strictly limited. Unlike a Text Box in a normal form or sub-form, an indivivual cell in a Table/Grid is not itself a Form Control: hence its properties and methods are limited. Cursor selection and focus therefore is controlled by the Table/Grid object which is a Form Control, whose View has a ".CurrentColumnPosition" property and its related methods. This property is determined by, or determines, the horizontal position of the selected cell. The vertical location in a Grid/Table Form Control is determined by the selected RecordSet, which in turn is determined by a property of the Form or Sub-form that contains the Grid/Table. The ".Bookmark" property is one key to locating the selected record/row and there are several methods available to change the row, or record, selection. These can be found at:

http://api.openoffice.org/docs/common/ref/com/sun/star/sdbcx/XRowLocate.html#getBookmark

Thus if you wish to select the third row in the Grid/Table this line should be added to the code:
oSubForm.MoveToBookmark(3)
just after the line:
oSubForm = oForm.getByName("SubformContainingGridName")

Furthermore I should mention that I have yet to develop the skill to predict reliably the outcome when the After updating event is triggered by either a Table/Grid form control or by an individual cell therein. As a coward I tend to use a mouse click event, which appears to be more predictable and thus the outcome more controllable. I would welcome further education on use of the After updating event.
Last edited by Arineckaig on Wed Jun 02, 2010 9:39 am, edited 2 times in total.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby RPG » Wed Jun 02, 2010 1:18 am

Hello

I believe that there are several problems with OOo3.2. Evwool did use for that reason OOo3.1. I have the same problems as Arineckaig. We all know that OOo is maybe full of problems. But I have the idea that the developers use the little time they have for those things who are important. That we cannot use parent seemes to me as a bug in OOo3.2 maybe this bug is not in OOo3.1 and I hope when it is a bug also no more in the new version.
With a version earlier I did have test with this event and parent and then it works good. So it is to the user to decide with version to use.

For evwool learn in your own way how to use all the new things. I hope you will succeed.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RPG
Volunteer
 
Posts: 2210
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Select a field with a macro

Postby Arineckaig » Wed Jun 02, 2010 9:40 am

I do not think in this case that the issue is with 3.2 which I use. See the edited addition to my hurried post last night.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Select a field with a macro

Postby RPG » Wed Jun 02, 2010 11:57 am

Hello

Arineckaig wrote about the placing of the cursor in a special field. That work good when you use a button. It does not work good in the way Evwool want use it. For me it is not clear if that problem is a bug or something else. But for me the problem is not important there I use the before update event and as long I do not return the routine with a true the focus is in that field for that reason I have use a function and not a sub.

Maybe it is important to explain the difference between beforeupdate and afterupdate.

When you edit a textbox the value is in that textbox. When you press return then there is started a process what moves/copy the value to the record what is a different place. Before the moving/copying you can change or test the value in the textbox. When there is a function call then on the result of the function there is decide must the value move/copy or not. The next step is when the value is moved/copied calling the after update event.

In an other way the same

If resultevent_BeforeUpdate=true then
go further program and update field
else
Stop updating field
endif

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
RPG
Volunteer
 
Posts: 2210
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Select a field with a macro

Postby evwool » Thu Jun 03, 2010 12:29 am

I've put all my code in the Before Update, as RPG suggested, and it all works fine. The code is much smoother in the Before Update and there is less danger of a faulty record being actually entered and having to be deleted. Using Arin's code, setting the column position to 0 doesn't actually highlight any field but it does stop the cursor moving elsewhere in the row so I can edit the field after a click.
Thanks both of you, you've given me some really useful stuff. I'm stil testing replacing spaces with an @. I'm marking this as solved.
 Edit: The above bit should read 'Using Arin's code, setting the column position to 1' 
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests