Lookup form

[Dorian Chalom]

Im trying to create a form that has two combo boxes that filter down the
records the user can view and edit. The first combo box selects the table
and the second onme selects the field. The table the information is stored
in looks like this:

Table
Field
Value

I cannot seem to get the filter to work correctly.

Can someone help?

Dorian

Private Sub cmbTable_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

On Error GoTo cmbTable_AfterUpdate_Err

Set rs = Me.Recordset.Clone

rs.FindFirst "

= '" & Me.cmbTable & "'"
'If Not rs.EOF Then
rs.Edit
Me.Bookmark = rs.Bookmark

Me.cmbField.Value = rs.Field
'End If


Me.cmbField.RowSourceType = "Table/Query"
Me.cmbField.RowSource = "SELECT DISTINCT Field FROM " & rs.Name & "
WHERE Table = '" & Me.cmbTable & "'"

Me.Filter = "

= '" & Me.cmbTable & "'"
Me.FilterOn = True

Me.cmbField.Requery
Me.Refresh

'Me.Requery

'pCBSetupPreviewReport m, m.cmbSchlID, m.cmbYear

cmbTable_AfterUpdate_Exit:
Exit Sub

cmbTable_AfterUpdate_Err:
MsgBox Err.Description
Resume cmbTable_AfterUpdate_Exit

End Sub

Private Sub cmbField_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

On Error GoTo cmbField_AfterUpdate_Err

Set rs = Me.Recordset.Clone

rs.FindFirst "[Field] = '" & Me![cmbField] & "'"
'If Not rs.NoMatch Then
rs.Edit
Me.Bookmark = rs.Bookmark
'End If

Me.Filter = "

= '" & Me.cmbTable & "' AND [Field] = '" &
Me.cmbField & "'"
Me.FilterOn = True

Me.Refresh

cmbField_AfterUpdate_Exit:
Exit Sub

cmbField_AfterUpdate_Err:
MsgBox Err.Description
Resume cmbField_AfterUpdate_Exit

End Sub