Lookup-list in dbgrid

Can a column in a dbgrid be made to react like a dropdowncombolist-type object? The possible values of a field in one dbtable is stored in a field in another table and I want to prevent the user from entering invalid data.
The listed field should be of text, inserting a corresponding numeric "key" into the destination DB field

Im using VB 5.0
Deep DiverAsked:
Who is Participating?
movieguyConnect With a Mentor Commented:
This works for me on True Database Grid version 5.0.

Using a ListBox control containing pre-defined input values in order to facilitate user data entry.  The list will drop down whenever the user initiates editing, such as by clicking the current cell.  We will also place a button in the cell which, when clicked, will cause the ListBox control to drop down.  You can drop down any control from a grid cell using techniques similar to those described below

The database has a table named Customers
Our database has a fields named FirstName(text), LastName(text), CustType(a number), and Contacted(A Date) under the Customers table.

Step 1.      Start a new project.
Step 2.      Place a Data control (Data1) and a DBGrid control (DBGrid1) on the form (Form1).  Set the DatabaseName property of Data1 to Name of your database, and the RecordSource property to Customers.
Step 3.      Set the Caption property of Data1 to Customers.
Step 4.      Set the DataSource property of DBGrid1 to Data1.

Configuring the grid at design time

We shall configure the grid using its popup context menus and property pages.  For more details, see Tutorial 5 and also these topics.

Step 5.      Right click on the grid to activate the primary context menu.
Step 6.      Choose Edit from the popup menu.  The grid will become UI-active at design time, allowing you to interactively change the grid's row and column layout.
Step 7.      By default, the grid contains two columns.  We are going to create two more.  Right click on the grid to activate the UI-active context menu.  Choose the Append option from the popup to add a new column at the end of the grid.  Execute this option one more times to create two more columns.  A total of four columns are now in the grid.

Step 8.      Right click again to bring up the UI-active context menu.  This time choose Properties... to bring up the DBGrid Control Properties window.  Select the Columns property page by clicking the Columns tab.  The Column combo box will display Column0.  Click the dropdown button of the Column combo box to display the default names of the four columns created in step 8 above: Column0, Column1, Column2, and Column3.  Choose Column0 from the list to display its property values.  Click the dropdown button of the DataField combo box to reveal a list of all the fields in the joined table.  Choose FirstName from the list.  The Caption property will default to the same name.

Step 9.      Repeat the previous step with the remaining three columns.  Column1: DataField = LastName, Column2: DataField = CustType, Column3: DataField = Contacted.
Step 10.      After configuring the four columns, accept the layout by clicking the Apply button at the bottom of the page.  This allows you to view the changes without closing the property page dialog (move the DBGrid Control Properties window to observe if necessary).  Click the OK button at the bottom of the property page dialog to close the Properties window.

Step 11.      Note that you are still in the grid's UI-active mode.  Place the mouse cursor over the column dividers within the column header area.  It will turn into a horizontal double-arrow cursor, indicating that column resizing can now occur.  Drag the dividers to adjust the column widths so that the gray area between the rightmost column (Contacted) and the right edge of the grid disappears.  The grid now looks like the one below:

Step 12.      Bring up the DBGrid Control Properties window again as in step 8 above.  This time select the Splits property page by clicking the Splits tab.  
Step 13.      Drop down the MarqueeStyle combo box and select 2 - Highlight Cell. For more information on this property, see Controlling the Marquee and the Floating Editor.  By default, the grid has one split.  Although you have not created any additional splits, you are still working with the properties of the default split.  The MarqueeStyle property is on the Splits property page because each split in the grid can have a different value for this property.  Properties such as this are referred to as split-specific properties.  Click the OK button at the bottom of the property page dialog to close the Properties window.

Step 14.      Click on Form1 anywhere outside DBGrid1 to exit UI-active mode.  You have now finished configuring the grid.
Step 15.      Add a ListBox control (List1) to the form (Form1) as shown in the figure:

Step 16.      Set the Visible property of List1 to False.

Adding Code to Drop Down a ListBox Control

The CustType field in the second column (Column1) of the grid displays numeric values ranging from 1 through 5 which represent the following customer types:

1 = Prospective
2 = Normal
3 = Buyer
4 = Distributor
5 = Other

We shall drop down List1, which will contain textual customer type descriptions, and allow users to double click an item in order to enter the associated value into the grid.

Step 17.      In the Form_Load event, we place code to add the customer types to List1.  We also place a button in the CustType column using the Button property.  (Alternatively, you may also set the Button property from the Layout property page at design time.)  The Form_Load event handler now looks like this:

Private Sub Form_Load()

    ' Add customer types to List1

    List1.AddItem "Prospective"

    List1.AddItem "Normal"

    List1.AddItem "Buyer"

    List1.AddItem "Distributor"

    List1.AddItem "Other"

    ' Place a button in the CustType column

    DBGrid1.Columns("CustType").Button = True

End Sub

Step 18.      If a cell in the CustType column becomes current, a button will be placed at the right edge of the cell.  Clicking the button will trigger the grid's ButtonClick event.  We will drop down List1 whenever the button is clicked:

Private Sub DBGrid1_ButtonClick(ByVal ColIndex As Integer)

    ' Assign the Column object to Co because it will be used

    ' more than once.

    Dim Co As Column

    Set Co = DBGrid1.Columns(ColIndex)


    ' Position and drop down List1 at the right edge of the

    ' current cell.

    List1.Left = DBGrid1.Left + Co.Left + Co.Width

    List1.Top = DBGrid1.Top + DBGrid1.RowTop(DBGrid1.Row)

    List1.Visible = True

    List1.ZOrder 0


End Sub

Step 19.      In the grid's BeforeColEdit event, we add the following code to drop down List1 if we are editing the CustType column (Column1).  Note that the code below will not work if the MarqueeStyle property is set to to 6 - Floating Editor.  See Controlling the Marquee and the Floating Editor for more details.

Private Sub DBGrid1_BeforeColEdit(ByVal ColIndex As Integer, _

        ByVal KeyAscii As Integer, Cancel As Integer)

    ' BeforeColEdit is called before the grid enters into

    ' edit mode.  You can decide what happens and whether

    ' standard editing proceeds.  This allows you to

    ' substitute different kinds of editing for the current

    ' cell, as is done here.

    If DBGrid1.Columns(ColIndex).DataField = "CustType" Then

        ' Let the user edit by entering a key.

        If KeyAscii <> 0 Then Exit Sub


        ' Otherwise, cancel built-in editing and call the

        ' ButtonClick event to drop down List1.

        Cancel = True

        DBGrid1_ButtonClick (ColIndex)

    End If

End Sub

Step 20.      We allow the user to enter data into the CustType column of the grid by double clicking the desired selection in List1:

Private List1_DblClick()

    ' When an item is selected in List1, copy its index to the

    ' proper column in DBGrid1, then make List1 invisible.

    DBGrid1.Columns("CustType").Text = List1.ListIndex + 1

    List1.Visible = False

End Sub

Step 21.      Finally, we make List1 invisible whenever it loses focus or when the user scrolls the grid:

Private Sub List1_LostFocus()

    ' Hide the list if it loses focus.

    List1.Visible = False

End Sub

Private Sub DBGrid1_Scroll(Cancel As Integer)

    ' Hide the list if we scroll.

    List1.Visible = False

End Sub

Run the program and observe the following:

^      DBGrid1 displays 4 columns of data from the Customers table.

^      Click on a cell in the CustType column to make it the current cell (indicated by the highlight).  A button will be displayed at the right edge of the cell.  Click on the button to fire the ButtonClick event.  List1 will drop down at the right edge of the cell as shown in the following illustration:

^      You can use the mouse or the up and down arrow keys to move the highlight bar of List1.  If you click on another cell in the grid, List1 will lose focus and become invisible.

^      Double click any item in List1.  The current cell in the grid will be updated with the selected item, and List1 will disappear until you initiate editing again.

^      If you move the current cell to another column, the button will disappear from the cell in the CustType column.

^      Make a cell in the CustType column current again.  This time, instead of clicking the button, click on the text area of the current cell to put it in edit mode.  Before the grid enters edit mode, it fires the BeforeColEdit event, and List1 pops up at the right edge of the current cell as if you had clicked the in-cell button.  You can use the list to select an item for data entry as in the previous steps.
Check out the valueitems collection of a column object.
If you were to upgrade the control to True Database Grid Pro (APEX) the function you want is integral via a link to a provided drop down control that comes with the upgrade.
Deep DiverAuthor Commented:
Thats a real mouth-full movieguy....
Im not gonna be able to test it right away so please be pasient.

Many TNX anyway for a lot of effort!!!
Its greatly appreciated.
Deep Diver
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.