Combobox problem

[aguilasol]

I have two combo boxes that contain data from one table.
table has two fields: Name and ID

cboName
cboID

I would like both combo boxes to update each other.
example if start typing in the cboName box it fills in after update I would like the cboID to be updated with the correct value and vise versa if i start typing the ID in the cboID box when selected the cboName should be updated. Does that make since

example table
ID NAME
1 joe
2 jane
3 mark



so if i type in or select 1 in the ID combo box it should put 'joe' in the Name combo box.

or if i type in or select jane in the NAME combo box it should put '2' in the ID box.

I have read how to cascade combo boxes but that is not what i think i need.

I am using a Access 2010 web database.

 

[JamesM]

Hi,
I think you may have to have 2 unbound combo boxes.

make cboName row source = Select Name from Table
make cboID = Select ID from Table

Make both combo boxes restricted to list

Below is the code you could use

'----------------------------------------------------------------------
Private Sub cboID_AfterUpdate()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT Name_Col From Table1 where ID = " & Me.cboID.Value
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        
        rs.MoveFirst
        
        If IsNull(rs.Fields(0)) Then
            Me.cboName.Value = "Unknown" ' No Name against ID
        Else
            Me.cboName.Value = rs.Fields(0)
        End If
        
        rs.Close
    End If
End Sub
'----------------------------------------------------------------------
Private Sub cboName_AfterUpdate()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT ID From Table1 where Name_Col = " & Chr$(34) & Me.cboName.Value & Chr$(34)
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        
        rs.MoveFirst
        
        If IsNull(rs.Fields(0)) Then
            Me.cboID.Value = "Unknown" ' No ID against Name
        Else
            Me.cboID.Value = rs.Fields(0)
        End If
        
        rs.Close
    End If
End Sub

*note you shouldn't call a column "Name" in your table as that is a reserved word and could cause you issues in the future. I renamed the "Name" column to "Name_Col" in this example.

James