[Solved] How do I link a combo box to a grid display?

[jasper89]

How do i link a combo box to a grid display?
Thus upon selecting something from the combo box , my grid will display the info related to the combo box.
Help is greatly appriciated!!!

[RPG]

Hello

You cannot link a combobox to a gridtable only to a table, query or list.
There is a different in use for listboxes and comboxes.
A listbox is more for input an ID in a table while you see an other value.
A combobox is for inputting the select value or you can type in a value.

see for more information here.

Romke

[jasper89]

how do i go about creating the query with the value i selected in the combo box to be displayed on the grid .
or how do i go about getting the same outcome using the combo box and something else.

eg . in the table indexes , name , address , gender.
when combo box selected name = john
how do i list only john's address and gender in the grid?

[evwool]

I'm guessing you're an Access Refugee Jasper. The OO combo box is more like the Access List Box while the OO ListBox behaves in a similar way to the Access combobox except that the visible data is in the first column of its underlying query and the Value is in the second. Also, there isn't a way of reading that Value in OO. Here's a Macro way of achieving the result until someone comes up with a better one. I've attached a db below so you can see it at work and adapt it to your db. You can use a similar method to open up a second form which is filtered to show the details you want. That may be a better solution than filtering a table grid when you have quite long data like personal details so I've included that macro by attaching it to a button in the same form.
Adapting Villeroy's brilliant lookup table idea
Given a Table TblPeople with PersID (Primary Key) , PersName, PersAddress as its fields
Adapting Villeroy's excellent method, create a table TblLookup with its Primary Key Field and an Integer field called IntField
Create a Query (QryListPeople) based on your TblPeoples containing in the first column the data you want to see in your list box. and in the second the Table's Primary Key field.

If your TblPeople has FirstName, LastName fields, you may want to concatenate those fields by typing
"LastName" || ',' || "FirstName" in the Field row of the query in Design view and
FullName in the Alias Row. (I haven't put a space after the comma because Listboxes can't seem to cope with spaces so if you have a lot of Smith's you may have a lot of scrolling to do rather than just typing in the required contents.)

In Form Navigator of your form, Rclick on Forms at the top of the list and choose New, Form
(Choose Rename to call your new form FrmLookup)
In Navigator RClick on FrmLookup and go to Properties.
On the Data tab choose
ContentType:Table
Content: TblLookup
Navigation Bar: No

Draw a ListBox and a Formatted Field in the Form. (If they aren't under FrmLookup in Navigator then drag them to FrmLookup in the Navigator Window.)
Add a button ensuring that it is in FrmLookup as with the ListBox and Formatted Field

On the Properties of the Formatted field on the Data tab next to Data Field, choose IntField
On the General Tab replace the Name with txtPersID.
In the Properties of the ListBox next to Data Field, choose IntField.
Next to Type of List Contents choose Query
Next to List Content choose QryListPeople

Add a macro to the database as follows and assign it to the Before Updating or After Updating Event of the ListBox as preferred (slightly different reactions but the same result)

Code: Select all

Sub FilterByListBox(oEvent As Object)
'EW
'Filter fields in a mainform grid
'assign this macro to the Change Event
'of the ListBox
'the field I want to filter contains an Integer

Dim MyForm As Object
Dim MyMainForm As Object
Dim MyFormName As String
Dim MyMainFormName As String
Dim FiltPart As string
Dim FiltWhole As String
Dim MyCtrl As Object
Dim MyCtrlName As String
Dim MyTextBox As Object
Dim MyTextBoxName As String
Dim FiltValue As Integer
'define my variables

MyTextBoxName = "txtPersID"
'TextBox in FrmLookup
MyCtrlName = "PersID"
'the field by which you want to filter
FiltPart = "PersID=" 
'there'll be a way I can use
'MyCtrlName and just put
''&"=" here
'but darned if I can work out how to get rid of the
'superfluous quote mark!
MyFormName = "FrmLookup"
'the name in Form Navigator of the form
'that contains the listbox and the textbox
MyMainFormName = "MainForm"
'The name in Navigator of the form that contains
'The TableGrid you want to filter

MyForm = ThisComponent.Drawpage.Forms.getbyName(MyFormName)
MyTextBox = MyForm.GetByName(MyTextBoxName)
FiltValue = MyTextBox.Text
'read the current value in the text box
'which was put there by the ListBox
FiltWhole = FiltPart & FiltValue
MyMainForm = ThisComponent.Drawpage.Forms.getbyName(MyMainFormName)
MyMainForm.ApplyFilter = False
MyMainForm.Filter = FiltWhole
MyMainForm.ApplyFilter = True
MyMainForm.Reload
'so that you can see the filter
End Sub

[goodvibes]

I'm only new to Base but hopefully this will help some-one. If I'm doing something stupid, please some-one tell me.

Here is an example of how to use a combo box to filter the records selected in the RowSet of the form.
In this case, making a selection from the combo box enables you to uniquely select a record as the value of the firstfield in the combobox is the primary key of the units table.

This uses some-one elses example I saw to overcome the limitation of ooBase ComboBoxes not having a bound field as in MS Access.
The values in the combobox are strung together so that we know the first 5 chars are the Unit, then a space, then the UnitName.
In case the unit is not 5 long, we add spaces to make it 5 long in the SELECT statement.
This makes it look nice in the combo dropdown and also enables us to extract the primary key from the text value of the combobox.
When we extract the Unit from the ComboBox in cmbFindUnit_ItemStatusChanged, we use trim() to strip off the trailing spaces so the unit can be found because it is not stored with trailing spaces in the DB.

My table is very simply. Table name is Units. It has 2 fields: Unit char(5) which is primary key & UnitName char(40).

Form is called frmUnits, ComboBox is called cmbFindUnit

ComboBox cmbFindUnit:
Data Field: Empty - do NOT want to modify a column in the data record
Type of List contents: SQL
List content: SELECT "Unit" + space(6 -length("Unit")) + "UnitName" FROM "Units"
Events: ItemStatusChanged: Standard.frmUnits.cmbFindUnit_ItemStatusChanged (document, Basic)
When Receiving Focus Standard.frmUnits.cmbFindUnit_GetFocus (document, Basic)

Code: Select all

sub cmbFindUnit_ItemStausChanged

	Dim TopForm As Object

	' Fetch the appropiate form
	TopForm = ThisComponent.DrawPage.Forms.GetByName("frmUnits")

	' Now we set the filter attribute
	' Note cmbFindUnit.Text is Unit (5 chars), 1 space, UnitName
	TopForm.Filter = "Unit = '" & trim(left(TopForm.GetByName("cmbFindUnit").Text, 5)) & "'"

	' and don't forget to reload
	TopForm.reload()

end sub

sub cmbFindUnit_GetFocus

	' When ComboBox receives focus, refresh it so it reflects additions, changes & modifications
	
	Dim TopForm As Object

	TopForm = ThisComponent.DrawPage.Forms.GetByName("frmUnits")
	TopForm.GetByName("cmbFindUnit").refresh

end sub

Note: 15 May 2010 I have editted my previous post to change the event from TextModified to ItemStatusChanged.
I have found that TextModified is executed every time a char is typed in the combo box, which is far too often, although it still works.
You cannot use AfterUpdate because, as this is an unbound control, this never happens.

[goodvibes]

Also, make the font of the combo box a non-proportional font like courier-new so the columns in the drop down box lineup.