Look up

  • Thread starter Thread starter Dorian Chalom
  • Start date Start date
D

Dorian Chalom

Screen is:
Table:
Field:
Value:

Table is:
Table
Field
Value

How do I set this up so that when I select a Table the Field is filtered to
those Fields for that Table and the Value is filtered to the Vaules for the
Field selected. Then when the Field is selected the Values are filtered to
the values for that field?

Do you understand?
Can you help?

Dorian Chalom
 
Screen is:
Table:
Field:
Value:

Table is:
Table
Field
Value

How do I set this up so that when I select a Table the Field is filtered to
those Fields for that Table and the RecordSet is filtered to the Records
for the
Field selected. Then when the Field is selected the RecordSet is filtered
to
the Records for that field?

Do you understand?
Can you help?

Dorian Chalom
 
Dorian Chalom said:
Screen is:
Table:
Field:
Value:

Table is:
Table
Field
Value

How do I set this up so that when I select a Table the Field is filtered to
those Fields for that Table and the Value is filtered to the Vaules for the
Field selected. Then when the Field is selected the Values are filtered to
the values for that field?

Do you understand?
Can you help?
Click to expand...

Dorian,

there was understanding and help :-) You may want to set the 'watch
thread' flag when you download your message's header.

This I wrote you on your first message on the same subject, it answers
your problem as you put it now:

I assume you would want to display in the 2nd combo the fields of the
table chosen in the 1st, then filter the form with the chosen table
and the chosen field. If so, you must first set a criteria in the
query for the 2nd combo to limit the list of field names to the chosen
table. The SQL view would look something like this:

SELECT DISTINCT Field, Table FROM MyInfoTable WHERE Table =
Forms!MyForm!cmbTable

The code would look something like this:

Private Sub cmbTable_AfterUpdate()

If Not IsNull(Me!cmbTable) Then
'Update the list in cmbField to show
'the chosen table fields
Me!cmbField.Requery
Else
'No table chosen, so reset the field combo
Me!cmbField = Null
End If

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 strWhere As String

On Error GoTo cmbField_AfterUpdate_Err

'Check if there is criteria chosen and
'proceed only when yes
If Not IsNull(Me!cmbField) Then
'Put together the filter criteria
strWhere = "
= '" & Me!cmbTable & "' AND [Field] = '"
& Me!cmbField & "'"

'First, delete the filter set previously
Me.FilterOn = False

'Set the filter
Me.Filter = strWhere
Me.FilterOn = True
End If

cmbField_AfterUpdate_Exit:
Exit Sub

cmbField_AfterUpdate_Err:
MsgBox Err.Description
Resume cmbField_AfterUpdate_Exit

End Sub

Some remarks to your original code: you don't need to search the
recordset and set a bookmark if you want to set a filter in the form,
and you don't need to edit a recordset to set the form's bookmark.

I'm not sure what you wanted with the code in cmbTable_AfterUpdate,
but I'd say, there is not much use to search a record in the form if
the user has selected the table but no field from that table.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Thanks for responding....it was most helpful.

In responce to your last comment...
I'm not sure what you wanted with the code in cmbTable_AfterUpdate,
but I'd say, there is not much use to search a record in the form if
the user has selected the table but no field from that table.
Click to expand...

I would want to search for records based off the Table selected and the
first value in the list of Fields.
And thus display the first value in the list of fields as the active field,
much the same for the table. I guess I want to default to the first Table
and Field in the list rather then Null.

Thanks again for your help




Emilia Maxim said:
Dorian Chalom said:
Screen is:
Table:
Field:
Value:

Table is:
Table
Field
Value

How do I set this up so that when I select a Table the Field is filtered to
those Fields for that Table and the Value is filtered to the Vaules for the
Field selected. Then when the Field is selected the Values are filtered to
the values for that field?

Do you understand?
Can you help?
Click to expand...

Dorian,

there was understanding and help :-) You may want to set the 'watch
thread' flag when you download your message's header.

This I wrote you on your first message on the same subject, it answers
your problem as you put it now:

I assume you would want to display in the 2nd combo the fields of the
table chosen in the 1st, then filter the form with the chosen table
and the chosen field. If so, you must first set a criteria in the
query for the 2nd combo to limit the list of field names to the chosen
table. The SQL view would look something like this:

SELECT DISTINCT Field, Table FROM MyInfoTable WHERE Table =
Forms!MyForm!cmbTable

The code would look something like this:

Private Sub cmbTable_AfterUpdate()

If Not IsNull(Me!cmbTable) Then
'Update the list in cmbField to show
'the chosen table fields
Me!cmbField.Requery
Else
'No table chosen, so reset the field combo
Me!cmbField = Null
End If

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 strWhere As String

On Error GoTo cmbField_AfterUpdate_Err

'Check if there is criteria chosen and
'proceed only when yes
If Not IsNull(Me!cmbField) Then
'Put together the filter criteria
strWhere = "
= '" & Me!cmbTable & "' AND [Field] = '"
& Me!cmbField & "'"

'First, delete the filter set previously
Me.FilterOn = False

'Set the filter
Me.Filter = strWhere
Me.FilterOn = True
End If

cmbField_AfterUpdate_Exit:
Exit Sub

cmbField_AfterUpdate_Err:
MsgBox Err.Description
Resume cmbField_AfterUpdate_Exit

End Sub

Some remarks to your original code: you don't need to search the
recordset and set a bookmark if you want to set a filter in the form,
and you don't need to edit a recordset to set the form's bookmark.

I'm not sure what you wanted with the code in cmbTable_AfterUpdate,
but I'd say, there is not much use to search a record in the form if
the user has selected the table but no field from that table.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
Click to expand...
 
Back
Top