Solved

Lookup-list in dbgrid

Posted on 1998-07-15
4
429 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Deep Diver
4 Comments
 
LVL 6

Expert Comment

by:anthonyc
Comment Utility
Check out the valueitems collection of a column object.
0
 
LVL 4

Expert Comment

by:trkcorp
Comment Utility
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.
0
 
LVL 1

Accepted Solution

by:
movieguy earned 200 total points
Comment Utility
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

    List1.SetFocus

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.
0
 

Author Comment

by:Deep Diver
Comment Utility
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
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now