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

Form Troubles

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


Joined: 25 Jun 2003
Posts: 24

PostPosted: Sat Nov 29, 2003 10:04 am    Post subject: Form Troubles Reply with quote

I am still having a hard time getting a form to work in OOo 1.1.

I have a CLIENT table in mySql which contains name, address and other specific client information...

I want to create a drop-down list which allows me to pick a specific client and then have that particular client's information posted on the screen....

I made a "listbox"... When I did this an "autopilot" popped up with a "Field Link" screen which requires me to match fields from a "Value Table" and a "List Table"....

What is this?....

The OOo help entry "Autopilot Combo Box/List Box" says:

"As a general rule, the database table that contains the visible list entries in the form is not the table on which the form is based. The list boxes in a form work by using references; that is, references to the visible list entries are located in the form table (values table) and are also entered as such in the values table if the user selects an entry from the list and saves it."

So, I take it there is some other table, other than the one I created, which controls the listbox?.... Is this the "List Table"?....

The Help entry makes no mention of a "List Table" though it does mention a "Form Table"... Are these tables actually the same things?... If so, where are these tables located?... What do I do with them?....

Assuming I got (by accident) the linking of these mysterious fields accomplished, how can I select data?....

Dfrench (thanks to him, by the way, for all his help) posted in reply to my earlier message:

"If you add a list box (from the show form functions icon), an autopilot will prompt you for the datasource; then provide a selection of tables from which you choose one; then you get a choice of dataitems(columns) from the table. You will need a macro to do anything with the data that you have selected, link this to an event using the control properties"

This brings up two questions:

1) Which "event" should I assign a macro to.. and

2) Which macro will select my record?.....

With my form the way it is, the listbox I have created, which is linked to the LAST_NAME field in my CLIENT table, will display all of my clients last names, but it keeps me in the first record.... I can pick a name from the list and save it in the current record, but I cannot select a new record....

For example, my first record is that of CLIENT Cindy Able.... When I open the form, her information comes up because she is the first record. If I replace "Able" with any of my client's last names by pulling downa list and selecting them, this changes Cindy Able's Last Name in the database......

What I would like to do is to select a client from the drop down list, and then have OOo display that client's information by retrieving it from that client's record..... And I don't want to be able to edit any information through this form..

OOo provides many listbox macros, so I am sure that there is a simple way to do what I need.... Thanks is advance for any help you can provide...
_________________
Paul Valois
Back to top
View user's profile Send private message
dfrench
Moderator
Moderator


Joined: 03 Mar 2003
Posts: 1605
Location: Wellington, New Zealand

PostPosted: Sat Nov 29, 2003 4:13 pm    Post subject: Reply with quote

Paul,
Thanks for your patience. I gave some duff info on listbox, I think.
I have not sorted out using listbox in the context you are tryng to use it but I may be able to demonstrate use of combobox to achieve the same thing.
I am using the Bibliographic datasource as distributed for the example. The intention is to use a combobox to display a drop down list of all the authors, the selection of anyone from that list will produce an update form for all (or a subset) data columns of biblio for that author.

1. Use autopilot to create a basic form for the table. Arrange to suit your needs.

2. Show the form properties,
Change the name to: mydata (to match my code and avoid problems with all the "Standards" that can appear)

Data tab should be:
Datasource - Bibliography
Content type - SQL
Content - Select * from biblio

3. Using forms navigator, create another form at same level as mydata form (not a sub form). rename this to "Select". Leave this form selected in forms navigator ..

4. Create a combobox (it will appear below Select on forms navigator)... if not move it there.


5. Make sure that the Select form is not linked to datasource .. go to forms property ... Datasource should be blank

6. Set up the datacontent for the combobox through the control properties
Data field - Author (what will be displayed in list)
Type of list contents - SQL
List content - select * from biblio

7. Add the following macro code to the document which will alter the selection for the mydata form based on the data selected in the control

Code:
Global oForm as Object

Sub Macro1
dim searchstr as string
' uses combo box
oCtl=thiscomponent.drawpage.forms.getbyname("Select").GetbyName("ComboBox")

oForm=thiscomponent.drawpage.forms.getbyname("mydata")
' use the content of the selection control to set the filter on the update
searchstr = "Author = '"+ oCtl.Text+"'"
SetFilter(searchstr)
End Sub

Sub SetFilter( sFilter as String)
' sets the forms filter property to reduce the displayed rowset
oForm.setPropertyValue("Filter", sFilter)

oForm.setPropertyValue("ApplyFilter",true)
' refresh the grid and dependent controls in form according to filter
oForm.reload()
end sub


8. Link this code to the "Text Modified" event of the combobox.

If I have written down all that I did, when you take the form out of design mode you will see an empty combo box and a random entry in the detail form. Clicking on the combobox gets you a list; selecting from that list will get you the detail of the selected author (actually there is not much data there but you have full update capability)

Note that the updates do not refesh the selection list in the combobox ... this is an exercise left for the reader Smile

I hope this helps. If not, thanks for your patience again. I am determined to understand listbox better and will get back to it refreshed in a week!
A short word of caution:
This did actually work when I followed the instructions above but there are probably a dozen better ways of doing it.
One improvement might be at step 6
List content = SELECT DISTINCT "Author" FROM "biblio" order by "Author"

For larger databases, avoiding loading the entire database at initial start up for both the combobox and mydata form might be sensible. Index the target data field (Author here) and set up an initial filter value Author = ''

I have discovered a problem with this. I noticed that Paul did not want to allow changes, so I set "allow modifications" = NO on the mydata form properties and the form stopped working (selections in the combo box did not fire the macro). Reverting to the previously saved version did not fix the problem. Nor did rebooting! If anybody can replicate this bizarre behaviour please let me know.
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