DBGrid List of Values or Default Value

In one of my columns in a DBGrid, I need to place a Default Value.  The default Value is under the Columns, Default Value property, however, when adding a new record, the default value is not inserted into the column.  

The DB is tied to an Access table, and the field in Access has no default value..as it shouldn't.  It is important that I NOT set it there, as the default value might change in each form in VB.  ie Contractor Form, default value "Contractor", Main form, default value "Primary".

Additionally, I need to validate or give an option group to have my choices for this column(6) in my DBGrid to have the choices of : Primary, Contractor, Sub-Consultant, Outside-Associate or Partner.  Can I build this into my DBGrid?
KathyBrowningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jgvCommented:
Hi Kathy,

According to the help files the "Default Value" for a column is only effective within an unbound grid. This is something that you will have to arrange manually. The bulk of the following code is borrowed from a tutorial (http://www.apexsc.com/dbgrid/survivalkit.html) which I have modified. If I have read your Q correctly, you want a column in your grid that will, depending on the form currently loaded, display a default value when you add a new record. You then want a drop down list to choose an alternate value from.

You might want to test this code in a new project using a test mdb. You will have to add a data control, dbgrid and a list box. If you have any trouble let me know.

Option Explicit
'//array to hold list choices
Dim ListValues(2) As String

'//column # for the default value
Const DefaultCol = 6

Private Sub Form_Load()
    ' Add choices to array
    ListValues(0) = "Primary"
    ListValues(1) = "Contractor"
    ListValues(2) = "Other"
   
    ' add array values to List1
    Dim x
    For x = 0 To 2
        List1.AddItem ListValues(x)
    Next
   
    ' Place a button in the default column and lock
    DBGrid1.Columns(DefaultCol).Button = True
    DBGrid1.Columns(DefaultCol).Locked = True
   
    List1.Visible = False
    List1.BackColor = &HC0FFC0
End Sub

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(DefaultCol)
   
If ColIndex = DefaultCol Then
    If List1.Visible = True Then
        List1.Visible = False
        Exit Sub
    End If
   
    ' Position and drop down List1 below the current cell
    With List1
        .Left = DBGrid1.Left + Co.Left
        .Top = DBGrid1.Top + DBGrid1.RowTop(DBGrid1.Row) + DBGrid1.RowHeight
        .Width = Co.Width
        .Visible = True
        .ZOrder 0
        .SetFocus
    End With
End If

End Sub

Private Sub DBGrid1_Scroll(Cancel As Integer)
    ' Hide the list if we scroll.
    List1.Visible = False
End Sub

Private Sub DBGrid1_OnAddNew()

Dim DefValue As String

'//get form name to determine default value
Select Case Me.Name
    Case "frmMain"
        DefValue = ListValues(0) '=Primary
    Case "frmContractor"
        DefValue = ListValues(1) '=Contractor
    Case Else
        '//unspecified form...do not insert a default
        Exit Sub
End Select

DBGrid1.Columns(DefaultCol).Value = DefValue

End Sub

Private Sub List1_Click()
    ' When an item is selected in List1, copy it to
    ' the column, then make List1 invisible.
    DBGrid1.Columns(DefaultCol).Value = List1.List(List1.ListIndex)
    List1.Visible = False
End Sub

Private Sub List1_LostFocus()
    ' Hide the list if it loses focus.
    List1.Visible = False
End Sub
0
jgvCommented:
Can I assume that this is what you were looking for? As usual, if you have any questions about this code please let me know.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.