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

ListBox Control for a Form (like MS Access)

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


Joined: 02 Jan 2006
Posts: 4

PostPosted: Mon Jan 02, 2006 9:54 pm    Post subject: ListBox Control for a Form (like MS Access) Reply with quote

After numerous hours of frustration and Googling, I shall rely on the wisdom of those more experienced than I.

I have a simple database with three fields:
ProductID, ProductPrice, and ProductDescription

Here's what I want to do:
Have a ListBox that contains all of the ProductIDs. Whatever ProductID is in the box, the corresponding information should be displayed in two Text Boxes. When the user changes the selection in the List Box, the rest of the form should change as well.

Yes, I know there are other posts and tutorials about this sort of thing, but they tend to be confusing and indirect. Hopefully this can serve as a simple walkthrough for others as well.

Thanks in advance!
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Jan 02, 2006 10:58 pm    Post subject: Reply with quote

As simply as I have come up with.

My table is defined as :

products( ProductID INTEGER,
ProductPrice DECIMAL,
ProductDescription VARCHAR)

Start the new form wizard ( 'Use wizard to Create Form...' )

Select the product table.

Select all three fields for the form.

On the page for layout choose a style other then a table grid.

Finish with the form in modify (design) mode.

Right click on the ProductID control group. Remember that the wizard grouped the text control and the label control together, so choose group>ungroup.

Right click on the ProductID text box control.

Select 'Replace with > Combob box'. (Not list box)

Double click the same control, to open the properties editor.

The properties dialog has three tabs, 'General', 'Data', and 'Event'.
The data tab should be the one select now, if not select it.
In the first property line 'Data field' it will say "ProductID", blank this out.

The second line 'Empty string is NULL' we don't care about.

The third line 'Type of list contents', change this to SQL

The fourth line, 'List Content...', enter the following statement:
SELECT "ProductID" from "products"

Change to the 'General' tab.

Scroll down till you see the line 'Dropdown...', change this to Yes.

Close the properties dialog.

OK - now is where Base is more difficult then Access. You have to use a macro - but in this case it is a very simple one.

Select the menu Tools>Macros>Organize Macros>OpenOffice.org Basic

The dialog box 'OpenOffice.org Basic Macros' will open. The default library 'Standard' and module1 is automaticaly selected at this point.

Hit the edit button.

The basic editor will open, and you will see a sub procedure named Main already defined.

Move the cursor to a line or two below the end of this procedure and enter this new one.

Code:
sub ChangeProductID( oEventObject as object )
dim oDataForm
dim strProductID

  oDataForm = oEventObject.Source.Model.Parent
  strProductID = oEventObject.Source.Text
  oDataForm.Filter = " ProductID = " & strProductID
  oDataForm.ApplyFilter = TRUE
  oDataForm.Reload
end sub


Save the library and close the editor.

Double click the ProductID combobox again.

This time in the properties editor select the tab 'Events'

On the line 'Item Status Changed' click on the button all the way to the right "...", this brings up the macro assignment dialog.

Click the button 'Assign'

This opens the Macro Selector Dialog

In the 'Library' list double click 'My Macros'.
Double click on the library 'Standard' and click on 'Module1'
In the 'Macro Name' list select "ChangeProductID"
Click OK
Click OK
Close the property editor.

Save the form.

It will do what you want it to do now.

I think that is the easiest way, least it is that I have found. If someone else can show you a different way it would be great.


There are some good resouces you should grab.

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
trenkler
Power User
Power User


Joined: 21 May 2005
Posts: 55

PostPosted: Mon Jan 02, 2006 11:54 pm    Post subject: Reply with quote

DrewJensen wrote:
As simply as I have come up with.
.....

Right click on the ProductID control group. Remember that the wizard grouped the text control and the label control together, so choose group>ungroup.

.......
The fourth line, 'List Content...', enter the following statement:
SELECT "ProductID" from "products"

Drew


I should like to add two remarks.

1. You can select control within group by holding CTRL when clicking on control.

2. You have to change "products" for "Products" in statement above.

juraj
Back to top
View user's profile Send private message
fujijr
Newbie
Newbie


Joined: 02 Jan 2006
Posts: 4

PostPosted: Tue Jan 03, 2006 9:43 pm    Post subject: Reply with quote

This worked very well for me. Thank you for the walk-through. (PM me if you have a problem with me citing it.)

The previous macro works with a Combo Box. As this cause slightly different behavior, the following works with a ListBox:

Code:

sub ChangeProductID( oEventObject as object )
dim oDataForm
dim strProductID
  oDataForm = oEventObject.Source.Model.Parent
  strProductID = oEventObject.Source.SelectedItem
  oDataForm.Filter = " ProductID LIKE '" & strProductID & "'"
  oDataForm.ApplyFilter = TRUE
  oDataForm.Reload
end sub


This way, a user can select the ListBox and start typing. The form will automatically jump to that record as they type and the selection changes.

I also changed the filter to use the LIKE keyword instead of =
Back to top
View user's profile Send private message
Keith Silva
General User
General User


Joined: 17 Jun 2005
Posts: 20
Location: San Francisco, California

PostPosted: Sun Jan 08, 2006 2:30 pm    Post subject: ListBox Operation Reply with quote

I have tried to be a good follower here because I want to use a list box in the same way as fujijr. Unfortunately, my list box is not working. My list box's name is lst_person_id and I placed it on the form using the list box control wizard. The list box displays the person_id field and both the list box and the form are based on the table tbl_person. I followed Drew's instructions including setting the Data field property to blank. However, I did set the Dropdown property to No. The list box is based on the wizard's SQL statement and displays properly.

Here is the macro code I used:

Code:
Sub Main

End Sub

sub ChangePersonID(oEventObject as object)
dim oDataForm
dim strPersonID
   oDataForm=oEventObject.Source.Model.Parent
   strPersonID=oEventObject.Source.SelectedItem
   oDataForm.Filter="person_id="&strPersonID
   oDataForm.ApplyFilter=True
   oDataForm.Reload
end sub


This code completely froze Base and I had to recover and restart the program. I then tried Drew's initial suggested line of:

Code:
strPersonID = oEventObject.Source.Text


This did not work and produced a macro error message, but Base kept alive. As you can see, I'm in over-my-head with this macro programming. However, if I can get this list box to work, I'll have enough of a database to start using it every day with live data.

Thanks, Keith Silva[/code]
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Jan 08, 2006 3:34 pm    Post subject: Reply with quote

keith,

Look again at the code snippet the fujijr posted.

When you use a combobox there is a property TEXT, when you use a ListBox there is not, insetead you must use the SelectedItem property.

HTH

Drew
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Keith Silva
General User
General User


Joined: 17 Jun 2005
Posts: 20
Location: San Francisco, California

PostPosted: Sun Jan 08, 2006 7:23 pm    Post subject: Reply with quote

My code does include the SelectedItem property rather than text. I'm definitely using a list box and I tried both the SelectedItem property and the Text property. Both resulted in errors. The SelectedItem property caused the biggest problem by freezing Base.

Thanks, Keith S.
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Jan 08, 2006 7:43 pm    Post subject: Reply with quote

oops, so you do...sorry.

Ok, you say that both the form and the list box contents come from the same table - right?

Let me try to put together a little test app and see what I come up with.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Jan 09, 2006 10:00 am    Post subject: Reply with quote

Well, I can duplicate the problem...I can conistently bring OO to a halt.

I will get back to you on what I can find for a WHY and how to get around it.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Keith Silva
General User
General User


Joined: 17 Jun 2005
Posts: 20
Location: San Francisco, California

PostPosted: Mon Jan 09, 2006 5:24 pm    Post subject: Reply with quote

Thank you so much. I'll keep experimenting, but I'm just stumbling in the dark.
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Mon Jan 09, 2006 6:37 pm    Post subject: Reply with quote

OK,

for the example what I used is a database that consists of just the supplied table definition for Employees.

I created one form

OK, so we have two drop down boxes that can filter the form.

Let's look at the department filter first.

The control's property for list content is:

The 'Item Status Changed' event set to this procedure
Code:
sub setFilterOnDept( oEv as variant )
  frm = thisComponent.Drawpage.forms(0)
  frm.filter = " Department = " & "'" & oEv.Source.SelectedItem & "'"
  frm.ApplyFilter = True
  frm.reload
end sub

Now, this works perfectly - select Shipping and the form goes into filter mode and only 2 records are available. Be sure to use single quotes for the comparision string, in this case the SelectedItem text is surronded by single quotes.

OK - so what about the employeeName drop down...

Here is the content selection:

And this combobox has the 'Item Status Changed' event assigned to the following procedure:
Code:
sub setFilterOnID( oEv as variant )
  frm = thisComponent.Drawpage.forms(0)
  frm.filter = " EmployeeID = " & oEv.Source.Text
  frm.ApplyFilter = True
  frm.reload
end sub

Pull this dropdown down and pick a name...and POW (Holy hangman - Batman!)...OpenOffice is hung.

Why?
Well first there is this - The column Employees.EmployeeID is the primary key and of type integer. I am setting the filter to compare it to a string.

But wait, I have done this mistake before in certain cases and all that should happen is that I should fail a comparison..or throw an error that the data can not be retrieved because the SQL statement is no longer valid.

So what else is happening..

Escape processing..that's what. Sometimes things can try to help way too much..and this is one of those times. OO.o is looking at the filter we set and fixing it for us. Notice again in the routine that works what we set our filter statement to

frm.filter = " Department = " & "'" & oEv.Source.SelectedItem & "'"

this is fixed for our use as a 'Where' clause for our dataform into:

WHERE "Department" = 'Shipping'

Notice the quoting that was added for us. Double quotes around the column name. It left the string 'Shipping' alone because we had it surronded by single quotes.

Now look at the second procedure again:

frm.filter = " EmployeeID = " & oEv.Source.Text

Guess what this will become:

WHERE "EmployeeID" = "Doe, John"

HSQLDB is going to read that as looking for a COLUMN named "Doe, John". Which there is not one of and this causes the hang. It shouldn't really, but apparently when combined with the dataform...well you have seen the final effect.

So, now what...

First - DON'T do this... Shocked

Unfortunaltly I have not found a way to get at the actual columns in the content provider for either the ListBox or ComboBox controls. There may be some access point in the API, but it has eluded me to date.

So, if I really had to do this for the employee name then I would have to change my procedure to this:
Code:
sub setFilterOnID( oEv as variant )
dim LName as string
dim FName as string
dim tmp as string
  frm = thisComponent.Drawpage.forms(0)
  tmp = oEv.Source.text
  LName = trim(left( tmp, inStr( tmp, ", " ) -1 ) )
  FName = trim(right( tmp, len( tmp ) - inStr( tmp, ", " ) ) )
  frm.filter = " LastName = " & "'" & LName & "'" & " AND FirstName = " & "'" & FName & "'"
  frm.ApplyFilter = True
  frm.reload
end sub

Sure enough this works like a champ...

I hope that this makes sense to you and that it helps you do what you wanted.

Drew

EDIT - Interesting side note. I was able to consistently hang OO when i was testing all this. Then after posting this I thought I should report this as an Issue. I went back and changed back to the original procedure that was causing the problem...now all I get is an error message saying that the SQL is not valid...go figure.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Keith Silva
General User
General User


Joined: 17 Jun 2005
Posts: 20
Location: San Francisco, California

PostPosted: Mon Jan 09, 2006 8:44 pm    Post subject: Reply with quote

Thanks again for putting so much time into this issue. I've printed this thread, will study it, and give the code a try over the next day or so. On first review, I'm understanding your analysis, so I think I can come up with the code for my database. I'll report back and let everyone know how it goes. This thread is being viewed frequently, so it looks like it will be helpful to others beside myself. The coming weekend is supposed to be rainy, perfect conditions for database study and development.

Thanks, Keith S. Very Happy
Back to top
View user's profile Send private message Visit poster's website
JeanClo
Newbie
Newbie


Joined: 03 Jan 2006
Posts: 1

PostPosted: Sat Jan 14, 2006 8:00 am    Post subject: Reply with quote

Here is another way to go to the selected record with a combox without using a filter.

Code:
Sub ChangeProductID( oEventObject as object )
dim oDataForm
dim strSelectText
dim oComboSelect

   oDataForm = oEventObject.Source.Model.Parent
   oComboSelect = oEventObject.Source
   strSelectText = oComboSelect.Text
   nCount = oComboSelect.getItemCount()
   nCounter = 0

    Do While ( nCount > nCounter)
      If strSelectText = oComboSelect.Items( nCounter ) then
         oDataForm.absolute(nCounter + 1)
          Exit Do
      End If
      nCounter = nCounter + 1
   Loop
End sub



Off course the sort order of the form must be the same as the list in the combo and the user must no be able to used the filter. Therefore it is best not to show the navigation bar of the form and to add a navigation bar control. Set the property of this navigation bar "Filtering/Sorting" to "Hide".

The same macro for a list box:

Code:
sub ChangeProductID( oEventObject as object )
dim oDataForm
dim strProductID
dim oListSelect

    oDataForm = oEventObject.Source.Model.Parent
   oListSelect = oEventObject.Source

         oDataForm.absolute(oListSelect.SelectedItemPos +1)

end sub



To make a selection with 2 combo ( one set a filter and the other one goes to the record) have look at this page http://www.lopura.com/LopuraOO.html
Back to top
View user's profile Send private message
jlentzram
General User
General User


Joined: 15 Sep 2008
Posts: 15
Location: Memphis, TN

PostPosted: Mon Sep 15, 2008 10:10 am    Post subject: Still New to oBase, struggling with simple stuff Reply with quote

I thought this post was going to solve my development problem, but I'm not sure that it's there. I have a similar Purchase Order application where I want to select a VendorName from a combobox and have the other Vendor text fields fill in automatically.

The two requirements that keeps this posted solution from being functional are 1) that I already have many Purchase Order records with vendor names in them, and they have to be browsable, whereas this solution filters all Purchase Order records down to just one, and 2) also that this solution display nothing in the combo box for records that already have data.

I managed to make my form work almost correctly purely through relationships between my Purchase Orders table and my separate Vendors table, but to make the text fields (VendorAddress, VendorCity, etc) update, I have to make my VendorName selection first, then scroll backward to the previous Purchase Order record, then back to the new one. (I have the same problem with the AutoNumber field that assigns a Purchase Order number - it doesn't fill in until I scroll away and return).

There seems to be some sort of form "refresh" that takes place when I scroll away and return, and if I could just do that as an ItemChanged event on the combobox it would work fine, with no (other) macro programming required. The oDataForm.Reload statement seems to reload the whole form, which takes me back to Purchase Order record 1 instead of just leaving me at the record I was on.

To show you how new I am at this, until I read this post, I believed the Help File statement that Macros could be recorded only in Writer, and decided I couldn't use them in oBase as a result (not realizing the editing actually takes place in Writer). And to think I actually used to dabble in native VBA programming some years back! Laughing

I'm next going to try an ItemChanged event assignment to a macro that SELECTs the Vendor text fields directly, and will post back here if that works. My challenge is that I have no idea where to find the methods and properties to put in the macros. I have yet to find them listed anywhere, and even the oDataForm.Reload statement was a new one on me, from the searching I've done.

I'd welcome any shortcuts or help while I'm still working on it.
_________________
Jeff Lentz
Ramcon-Fiberlok, Inc.
Back to top
View user's profile Send private message Visit poster's website
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