Comboboxes as Lookups in vb6

[stewea99]

Hi,

I have this form which so far displays data if its there or lets you enter new data in the form. I have a combo box which at present inserts whatever is selected into my table.

How can I make it so the id of the item in the combobox is inserted instead of the actual text displayed.

The table where the ComboBox data comes from is like this:
PackageID: An Autonumber
Package: Description of selected package.

How can I also make it, so that when I go back to the form to view the saved data, the combobox is preselected with corresponding id.

Heres my code so far.

Private Sub Form_Activate()
    'On Error GoTo errHandler
    Dim sSql As String
    Dim adoRSA As ADODB.Recordset
    Dim adoField As ADODB.Field
    Dim lCol As Long
    Dim lRow As Long
    Dim lWidth As Long
    Dim labEntry As Label
    Dim txtEntry As TextBox
    Dim vArr As Variant
    Dim lX As Long
    Dim sHide As String
    
    If Me.Tag = "loaded" Then Exit Sub
       
    Me.Tag = "loaded"
    Me.Icon = frmAccount.Icon
    'txtEdit.Visible = False
    
    'load IP details
        sSql = "SELECT IPdetails.* " & _
            "FROM IPdetails WHERE Clis = " & glCli
        Set adoRS1 = gadoConnDB.Execute(sSql)
        
        If Not adoRS1.EOF Then
            'If adoRS1(tickProgrammed.DataField) Then
                'tickProgrammed.Value = 1
            'Else
                'tickProgrammed.Value = 0
            'End If
            For Each txtEntry In txtIPDetails
                txtEntry.Text = adoRS1(txtEntry.DataField)
                'If tickProgrammed.Value Then
                    'txtEntry.BackColor = &H8080FF
                'Else
                    'txtEntry.BackColor = &H80000005
                'End If
            Next
            'framePBX.Tag = .TextMatrix(.Row, 2)     'save lCli
        Else
            For Each txtEntry In txtIPDetails
                txtEntry.Text = ""
                txtEntry.BackColor = &H80000005
            Next
            'tickProgrammed.Value = 0
            'framePBX.Tag = .TextMatrix(.Row, 2)     'save lCli
        End If

    Dim strSQL As String 'Declare the variables we need
    'Load the data
    '** change this SQL to load the data you want.
    strSQL = "SELECT * FROM PackageType"
    '** change oConn to the name of your Connection object
    Set adoRScombo1 = gadoConnDB.Execute(strSQL)
    
    'Fill the combo box (or ListBox)
    '** change the name of the combo to the one you want to fill
    With CboPackageType
    .Clear
    Do While Not adoRScombo1.EOF
    '** change the name of the field here to the one you want to show
    .AddItem adoRScombo1.Fields("Package").Value
    adoRScombo1.MoveNext
    Loop
    End With
    
    
    'Tidy up
    adoRScombo1.Close
    Set adoRScombo1 = Nothing




End Sub

Private Sub cmdUpdateContact_Click()
    Dim sSql As String
    Dim adoRS1 As New ADODB.Recordset
    Dim txtEntry As TextBox
    
    'update ip data
    sSql = "SELECT IPdetails.* " & _
            "FROM IPdetails WHERE Clis = " & glCli
    adoRS1.Open sSql, gadoConnDB, adOpenStatic, adLockOptimistic

    If adoRS1.EOF Then
        adoRS1.AddNew
    End If
    adoRS1("Clis") = glCli
    adoRS1("dtype") = CboPackageType
    'If tickProgrammed.Value = 0 Then
        'adoRS1(tickProgrammed.DataField) = False
    'Else
        'adoRS1(tickProgrammed.DataField) = True
    'End If
    For Each txtEntry In txtIPDetails
        adoRS1(txtEntry.DataField) = Trimmer(txtEntry.Text)
    Next
    adoRS1.Update

    cmdUpdateContact.Enabled = False

End Sub

[cometburn]

'strSQL is the query string
'dName is the Fieldname to be displayed
'dID is the ID field to be inserted on the Itemdata
'cbo is the name of the combobox that will contain the datas

Sub populateComboBox(ByVal strSql As String, ByVal dName As String, ByVal dID As String, ByVal cbo As ComboBox)
Dim rs As New ADODB.Recordset
Dim n As Integer
n = 0
If rs.State = 1 Then rs.Close
rs.Open strSql, myDe.Conn, 1, 3
    If rs.RecordCount >= 1 Then
        cbo.Clear
        rs.MoveFirst
           Do While Not rs.EOF
            If IsNull(rs.Fields(dName)) Then
            cbo.AddItem ("")
            Else
            cbo.AddItem (rs.Fields(dName))
            End If
            cbo.ItemData(n) = rs.Fields(dID)
        rs.MoveNext
        n = n + 1
        Loop
    End If
End Sub

hope this would help you.

[swatirao]

Hi!

Use .additem feature of the combo box to add the ids into the combo box. The help file guides u to do that.

Hope it helps...

bye

swati

[nth]

i tried commetburn's example
cbo.ItemData(n) = rs.Fields(dID)
but it returns a type mismatch error.
My dID field is String...what do i do??