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

dropdown record switcher
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
gijsbert2002
Newbie
Newbie


Joined: 07 Jun 2008
Posts: 1

PostPosted: Sat Jun 07, 2008 7:38 am    Post subject: dropdown record switcher Reply with quote

I have a very simple database which shows information on my customers. I want to make a dropdown menu, that will display the last name of all of the customers and when I click on the name of the person I want the form will switch to the record of that customer.

How do i achieve this?
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sat Jun 07, 2008 9:50 pm    Post subject: Reply with quote

Hi there,

The simplest way I can think of without the use of macros is...
Each "writer" doc can have both mainforms and subforms in it. A subform is linked/joined to the mainform in the subform's data properties. To see this form structure, look at your form in "design mode" and there is a button on the "Form Design" toolbar called "Form Navigator". When you press this button, a "tree" structure shows displaying all the "forms" and "controls" that are in your "writer" document.

Presently you probably have your data fields in a mainform only. What you will need to do is move these all into a subform so that you can put a combobox into the mainform and use the linking/joining feature between the mainform and subform such that what you select in the listbox (or combobox) determins which record shows in your subform.

Ok... to best achieve this, create yourself a new table and call it "dummy". Create one field in this table and call it "Surnames_Holding". When prompted for a primary key say yes. You do not need to turn it into an "auto incrementing" primary key as this table will only ever have one row of data in it. Once created, go into this table and enter the number "1" into the "ID" field and some text (dosen't matter what) into the "Surnames_Holding" field. This is so that when we create the form to look at this table it dosen't complain about there not being a primary key.

Now, Back to your form. With the "Design Navigator", right click on your "mainform" and select "new form". It will create another form underneath called "standard". Highlight all your fields and controls and move them into this new form. You need to change the data properties of these forms to reflect which data table you want in each so with the "Design Navigator" right click and select properties and go to "data" tab. For the Data source of "mainform" you want "Dummy_Table" and you want "Names_Table" (or whatever your table is called) for your subform. Whilst in the subform properties select the [...] next to "Link Master Field" and choose "surname holding" for the master field and "surnames" for the slave fields. This is so the mainform and subform are linked together with the "surnames" data field's

Go to the "Form control" toolbar and make sure that you have the "form wizard" button pressed in (picture of a wand). Click back on the mainform (in the "form navigator"...so the control goes into the correct form) and from the toolbar select the combobox. Draw a combobox on your form and the wizard should open. For the datasource of the combobox choose "Names_Table" (or whatever your table is called). Next the wizard will ask you what field to display and select "Surnames". Next tell it to save it in the "surnames_holding" database field.

Ok, almost done... I found that I needed to go to the "mainform" properties and change "allow data additions" to "no" in the data tab. This was to stop an error occuring if you happened to press the "new record" button for the mainform. This is because the mainform is only based on one row of data and a new record would then want a primary key value and we had not turned "auto increment" for primary keys on.

I did say at the beginning that no macros were needed. This isn't quite true because the way it is at the moment you need to press the "refresh" button after each time you select something in the combobox. To neaten this up we need a very simple macro. Something like...

Code:
Sub Reload (oEv as object)

oEv.source.model.commit() Rem... commits the value from the combobox into its bound field
oEv.source.model.parent.GetByName("SubForm").Reload() Rem... reloads the subform.

End sub


This macro is driven off the "Item status changed" event of the combobox. The first line (after sub) commits the selection made to the "Dummy" data table. The reload() then reloads the subform. oEv is the "object event" passed into the sub from the control that fired it. You can think of the macro as.... following the path from the control to the form and then performing a reload().
With the oEv object passed in from the control,
Code:
oEv.source.model
is the properties of the control.
The control is sitting in the mainform so to get to the subform properties the path is
Code:
oEv.source.model.parent.GetByName("SubForm")
(or "Standard" if your subform is called that). By the way correct case is important with form and control names.

I have done a very quick example and put it on mediafire for you to download. The macro file is included with the form so you may get a macro warning when you run the database file. This is unavoidable when macro's travel with the "writer" doc's.

http://www.mediafire.com/?gmziyjyzxhi
(Example DataBase "surnames")

Edit - Updated example Surnames2 on page 3 of this thread. For filtering it uses macros to change the SQL command in the form, rather than using a link/join between the main and sub forms (i.e no dummy table needed). Bit more advanced though, due it having more code Crying or Very sad .

Let me know how you go.

Cheers

Voo


Last edited by Voobase on Fri Jan 21, 2011 8:02 am; edited 4 times in total
Back to top
View user's profile Send private message
JAnneP
General User
General User


Joined: 18 Aug 2007
Posts: 39
Location: Ontario

PostPosted: Sat Jun 28, 2008 1:16 pm    Post subject: Reply with quote

Thanks so much, Voobase, for this macro, and these instructions! I was trying to figure out how to do exactly the same thing, and your instructions and macro helped me out immensely! Surprised
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sat Jun 28, 2008 6:13 pm    Post subject: Reply with quote

No Worries. Very Happy

Voo
Back to top
View user's profile Send private message
madwolf
General User
General User


Joined: 02 Jul 2008
Posts: 13

PostPosted: Thu Jul 03, 2008 7:47 am    Post subject: Reply with quote

JanneP,

I hope you don't mind me tagging onto your thread.

What if we were to use macros to attain the same results?

I am looking into the line of changing the property of the textboxes etc...

Is it something like this:

Code:

Dim sqlStudent_ID as String
Dim txtName as String

txtName = comboBoxName.value

sqlStudent_ID = SQLQuery(SELECT Student_ID FROM tableParticulars WHERE StudentName = txtName)

txtStudent_ID,value = sqlStudent_ID


I'm sure there are alot of syntax error but I am looking along this area to solve the issue as I would like to use macro to manage this.

1) What is the method to retrieve the value of the combobox selected?
2) How can I change the value of the textbox?
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu Jul 03, 2008 9:09 am    Post subject: Reply with quote

Hi madwolf,

The method described here relies on the linking/joining between a (dummy) mainform and subform to narrow the results returned in the subform.

What you are proposing, (changing the SQL that determins what the form displays) could be an elegant way of displaying records but I am not sure it would work if you want to be able to update the records. Base has a shortcoming that forms based on queries that contain more than one table are not updateable. In those situations it is best to let the forms do the work by using the linking and joining between them. You can, of course, have multiple subforms and even subsubforms.

Another way to do narrowing is to use the "filter" features of base, which is like using SQL to some extent. That's the method which Kabing gave you a link to on the other forum.
http://user.services.openoffice.org/en/forum/viewtopic.php?p=6470#p6470

To answer your questions....
Quote:
1) What is the method to retrieve the value of the combobox selected?


Code:
Rem... driven off the "item status changed" event of vombobox
sub retrieve_combobox_value (oEv as object)

dim oForm as object
dim oControl as object
dim varValue as string

oForm = oEv.source.method.parent
oControl = oEv.source.method

varValue = oControl.boundfield.value Rem... or oControl.boundfield.string

msgbox varValue

End sub


Quote:
2) How can I change the value of the textbox?


Do you mean the text in the combobox? or a textbox?

For the text in the combobox you would add a line something like this...
Code:
oControl.boundfield.updatestring("Name Here")


and for a textbox it would be...
Code:
oForm.getByName("TextBoxName").boundfield.updatestring("Name Here")

Quote:

I am looking into the line of changing the property of the textboxes etc...

The best thing to do if you really want to see all the methods and properties available is to download the tool "XRay". Do a search for it on the forum and you should find a link to it. It is invaluable when it comes to writing macros.

Hope this helps

Cheers

Voo
Back to top
View user's profile Send private message
madwolf
General User
General User


Joined: 02 Jul 2008
Posts: 13

PostPosted: Sun Jul 06, 2008 7:41 am    Post subject: Reply with quote

Hey Voo,

Thanks for all the info. You'd answered much of my queries and I am currently working on it.

However, I'd used the first method that you'd proposed (dummy table) as you'd mentioned that Base has a shortcoming in the area u'd highlighted.

Currently, I have 3 subforms which needs to be updated based on the "dummy" name that the user selected.

In one table, "Particulars", I have the field, "Names"

However, in the remaining 2 tables, I only have the "Student_ID" residing in the tables, as such, selecting the name would not automatically select the Unique Student_ID that is residing in the 2 tables. That is why I couldn't link the dummy form with the remaining 2 forms which has the "Names" inexsitent in the table.

I am still working on it though.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sun Jul 06, 2008 8:45 am    Post subject: Reply with quote

Hi again,

I worked out this macro the other day. It gives a combobox the ability to also have a separate value as a bound field, similar to what a listbox does. See if it helps you at all.

It takes the value from the combobox and enters it into a statement which is created (createstatement). The statement searches the table that the combobox is based on and finds the first time that the displayed value appears in the table and returns the "ID" field. The "ID" field is then put into a UPDATE "prepared statement" and copied to the "dummy" form. From the dummy form you could then link your other forms.

Be careful as it only returns the first time a name is found in the Combobox's table. If there are Duplicate names in your table this could cause problems as you will be then joining your subforms to a value which should be unique if it is a student_ID and it will only return one record so it might be hard to return records for the other people with the same surname. You may need to modify the macro, use a "While" loop to get all the Result.next(2) values which are returned and then determine which one you really wanted.

Code:
Rem... this macro is sort of like adding a separate bound field to a combobox like a listbox has. While you are using the combobox's
   Rem... natural features to copy the displayed value somewhere, this macro copies the associated primary key. By Voo!
   Rem... Triggered from the item status changed event of the combobox.
Sub Combobox_Add_Extra_Bound_Field (oEv as object)
dim oForm as object
dim oControl as object
dim ID_Main as string
dim FieldName_ID as integer
dim Result
dim strSQL As String
dim oCreateStatement as object

oForm = oEv.source.model.parent
oControl = oForm.GetByName("cbxName")
ID_Main = oControl.CurrentValue

   strSQL="SELECT ""FIELD_NAME"",""ID"" FROM ""TableName"" WHERE ""FIELD_NAME"" = '" & ID_Main & "'"
 
        oCreateStatement = oForm.ActiveConnection.CreateStatement
       Result = oCreateStatement.executeQuery(strSQL)
       
    If Result.next() then rem... this actually does a result.next()
      FieldName_ID = Result.getstring(2)
   Else
   exit sub
   End if

dim oPrepStatement
dim sSQL as string
         Rem... This bit updates the table with the Foreign Key field of what was selected in combobox.
       sSQL = "UPDATE ""Dummy_Table"" SET ""Foreign_Key_Field"" = ? "
       sSQL = sSQL & "WHERE ""ID"" = ? "
   
         oPrepStatement = oForm.ActiveConnection.prepareStatement( sSQL )
         oPrepStatement.setInt( 1, FieldName_ID ) Rem... PK of table which holds the combobox list
         oPrepStatement.setInt( 2, 1 ) Rem... There should only be 1 row in this dummy table
         oPrepStatement.executeUpdate()


End Sub


You will need to customize field and table names of course.

For help on this sort of stuff (create, prepare statements) see:
Forms and dialogs pdf and Database Development pdf by C.Benitez
http://www.geocities.com/rbenitez22/

Learn Open Office.org Spreadsheet Macro Programing
http://www.packtpub.com/openoffice-ooobasic-calc-automation/book#indetail
(Chapter 6 on databases is free... look for link on RHS of page)

Andrew Pitonyak macro examples
http://www.pitonyak.org/oo.php

Cheers

Voo
Back to top
View user's profile Send private message
madwolf
General User
General User


Joined: 02 Jul 2008
Posts: 13

PostPosted: Sun Jul 06, 2008 10:01 am    Post subject: Reply with quote

Hey voo,

Thanks for your suggestion as I was thinking along that line.

I made the neccessary modifications to the codings and it looked like this:

Code:


Rem Contributed by Voo!

Sub Reload (oEv As Object)

Dim oForm as Object
Dim strStudent_ID as String
Dim oExecuteSQL as Object
Dim FieldName_ID as String

Dim Result

oForm = oEv.source.model.parent
oEv.source.model.commit()
oEv.source.model.parent.GetByName("Particulars").Reload()

strStudent_ID = oEv.source.model.parent.GetByName("Particulars").txtStudent_ID.boundfield.value

oExecuteSQL = oForm.ActiveConnection.CreateStatement

MsgBox (oEv.source.model.parent.ComboBox.boundfield.value)

Result = oExecuteSQL.executeQuery ("SELECT Name, Student_ID FROM tableParticulars WHERE Name = '" & oEv.source.model.parent.ComboBox.boundfield.value & "'")

   If Result.next() then
   
      FieldName_ID = Result.getstring(2)

   Else

      exit sub

   End if
   
dim oPrepStatement
dim sSQL as string

sSQL = "UPDATE " & "tableDummy" &  " SET Student_ID = '" & FieldName_ID & "' "
sSQL = sSQL & "WHERE ID = '1' "

MsgBox sSQL

oPrepStatement = oForm.ActiveConnection.prepareStatement( sSQL )
oPrepStatement.setInt( 1, FieldName_ID )
oPrepStatement.setInt( 2, 1 )
oPrepStatement.executeUpdate()

End Sub



I kept getting the "Table not found in statement" error. I have the table, tableDummy.

What is wrong?
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Mon Jul 07, 2008 6:57 am    Post subject: Reply with quote

Hi madwolf,

I think there are lots of Syntax issues with the SQL statements in the code. There are some rules that you have to follow which means having to double double quote and sometimes tripple double quote things. (I think that makes sense Rolling Eyes ) I'll call these (") inverted commas to make it easier then.

The SQL in those statements is being dealt with like a string, that is why the whole statement is between inverted commas. Now the SQL requires that if a field or table name is not in capitals then it must be between inverted commas. But why two lots of inverted commas??. That's because when you have inverted commas inside a string you have to double them. Otherwise the string dosen't know where it begins and ends.

Now when a variable is used in an SQL statement like this it has to go between the & symbols. I can't recall the exact reasons right now but if you follow the way it looks in my example you should be right.

Having a quick glance, these are some of the bits I think you should look at...
Code:

strStudent_ID = oEv.source.model.parent.GetByName("Particulars").txtStudent_ID.boundfield.value

... txtStudent_ID looks out of place.
Code:
Result = oExecuteSQL.executeQuery ("SELECT Name, Student_ID FROM tableParticulars WHERE Name = '" & oEv.source.model.parent.ComboBox.boundfield.value & "'")

... Don't forget all the double quotes you need.

Now in a prepared statement the question marks are all there for a reason. Later in the statement where it has the numbers 1 and 2, these are the parts that will be inserted where the question marks are. Have read of some of the doco I linked with before and this might make some sense. So where you have substituted the questionmarks with your variables and values I don't think that will work.

Let me know how you go

Voo
Back to top
View user's profile Send private message
madwolf
General User
General User


Joined: 02 Jul 2008
Posts: 13

PostPosted: Mon Jul 07, 2008 7:24 am    Post subject: Reply with quote

Hey Voo,

Appreciate all the help that you'd rendered thus far. I guess I just change my table name to all uppercase, (DUMMY), and surprisingly, it works. tableDummy don't seem to work, as what you'd mentioned where we might need to doube or triple quote.

I have 2 questions:

1) How do we use double or triple quotes? That means, how do we insert the symbol " into the string? We can add a single quote, such as (') into the string by doing this: " ' ". What about double quote? I know it's an ignorant question but I can't seem to recall as I'd done programming 8 years back. Sorry about that.

2) I don't quite understand what the below 2 statement does:

Code:
oPrepStatement.setInt( 1, FieldName_ID )
oPrepStatement.setInt( 2, 1 )


The problem now is that I'd linked all 3 sub-forms to the dummy table that includes the name. How should I make it go to allow the 3 sub-forms to refresh?[/code]
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Mon Jul 07, 2008 8:00 am    Post subject: Reply with quote

Hi again,

I think rather than changing your table name to capitals that you could have just put an extra set of double quotes around the table name and that might have done it. Some programmers say that it is good practice to use capitals for table and field names so leaving it as capitals is probably not a bad thing.

1/ To insert the " into a string you just put another one with it. For example if I were to create a messagebox to display the sentence... Johnny had mentioned "Base is fun" and he is right. I would need to do as such.
Code:
msgbox "Johnny had mentioned ""Base is fun"" and he is right."


I think something similar goes for single quotes, you need another single quote next to it, but don't quote me on it (excuse the pun). I found a good post on this somewhere... I'll try to dig it up.


http://www.oooforum.org/forum/viewtopic.phtml?t=48477
http://www.oooforum.org/forum/viewtopic.phtml?t=67892

2/ The prepared statement is replacing the ? marks in the SQL statement with the values you have supplied after the comma in the brackets. The setInt is referring that it is setting an integer, it sometimes could be another data type for example setstring.

So what it is doing is substituting your FieldName_ID where the first question mark is and the number 1 where the second question mark is.

To refresh your subforms do a reload, something like

Code:
oForm.GetByName("Subform_Name").Reload()

for each of your subforms.

Cheers

Voo
Back to top
View user's profile Send private message
madwolf
General User
General User


Joined: 02 Jul 2008
Posts: 13

PostPosted: Mon Jul 07, 2008 10:44 pm    Post subject: Reply with quote

I finnaly understand what it means by the setInt()

Anyway, I did use the Reload() Functions but it failed.

My Particulars sub-form is able to reload, however, the remaining 2 sub-forms reloads with no value.

I use a MsgBox to check whether the Student_ID had been replaced and it proved that it did.

What could be the problem?

oForm.GetByName("Particulars").Reload() works

oForm.GetByName("Status").Reload() and oForm.GetByName("Lessons").Reload() failed to work.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Tue Jul 08, 2008 1:25 am    Post subject: Reply with quote

Hi again,

Just checking the way you have your forms set up. Your dummy form should contain a PK (ID) which dosen't auto increment. It should also contain three fields, a varchar field for your first subform to link/join to and two integer fields for each of your other subforms to link/join to. Each subform should have its data properties set to join the appropriate fields and display the appropriate data table.

The macro should copy the Student_ID to the "Dummy" table, but..... I just thought of something!

The form holds a rowset of data from the table in memory ready to use. The form will not automatically use a new value that has landed in the data table unless it is told to refresh its rowset. (we put the value directly in the table using a prepared statement). Refreshing the rowset of a form is done automatically if "save" or a form "refresh"(reload) is done on the form. Until now we have only been reloading the subforms.

You do not need to do a complete reload of the (dummy) mainform but you will need to call refreshRow() on it. Try putting the following line of code in before you reload the subforms.
Code:
oForm.refreshRow()


The other way to get around this is to not use a prepared statement (as they copy to the data table direct) but rather copy the values to the form's rowset instead. You could instead use some code which looked like this for each of the values you needed to copy...
Code:

oForm.UpdateInt( oForm.findColumn("Student_ID"), FieldName_ID )

(If student_ID is one of your field name in Dummy table and FieldName_ID is that variable you loaded with the value you are copying))

I think that should work. Let me know if it dosen't.

Cheers

Voo
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Tue Jul 08, 2008 1:54 am    Post subject: Reply with quote

Now, all that being said, i've been thinking about the data structure you have in your forms. I am wondering if your "Particulars" form holds (or can hold) the foreign key values to your other two forms. Remember I was querying what would happen if two people shared the same surname as only the first person would be shown in your subforms which linked using the student_ID. I am wondering if you would be better off having these two subforms instead link as subsubforms off your particulars form. You could easily set that up with the "Form Navigator". Additionally, you would then not need the bulk of that macro.

Form operation would then be... use the combobox to fill the "Particulars" subform with the selected names (This form could be a table grid). You could then select the correct person from this subform and this would automatically display the correct record for that person you selected.

Cheers

Voo
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
Goto page 1, 2, 3  Next
Page 1 of 3

 
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