I want to use combo boxes to locate specific records

[BobC]

I have built a basic form which I want to use to edit some of the fields
of a table. Two of the table fields (HA and POLYR) are indexed
(duplicates ok) and will be locked to editing. Given a particular HA
and POLYR, these two fields uniquely define a specific records in the
table. I have initially set up the form with these two fields displayed
in combo boxes. I would like to use these two combo boxes to quickly
locate particular records. POLYR has 25 unique values and HA has 150
unique values. I am having problems trying to configure the combo boxes
to do what I want to do.

 

[Jeanette Cunningham]

Bob,
there is a sample search form with all the code you can download from this
site
http://www.allenbrowne.com/ser-62.html
It shows how to write code to filter a form to the records selected in
combos.

Jeanette Cunningham

 

[BobC]

I am somewhat overwhelmed by the example. Is there a simpler example?

 

[Jeanette Cunningham]

1. The first lot of code goes as a separate sub in the code module for the
form ( just copy and paste it)
To use this code, put the following for the after update of each combo
Call FilterTheForm

2. How to set up the form so that it will work

3. How to open the form to look the way you want


Code for 1.
-------------------------------------
Private Sub FilterTheForm()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboHA) Then
strWhere = strWhere & "([HA] = """ & Me.cboHA & """) AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboPOLYR) Then
strWhere = strWhere & "([POLYR] = """ & Me.cboPOLYR& """) AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboPOLYR) Then
strWhere = strWhere & "([POLYR] = " & Me.cboPOLYR& ") AND "
End If

'Chop off the trailing " AND ", and use the string as the form's Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
--------------------------


code for 2.
-------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub
-----------------------------


code for 3.
-------------------------------
Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

 

[BobC]

Thank you for your time and help! Hopefully I will be able to work with
this over the next couple of days ... I'm working weekends.
Both HA and POLYR are integers and no fields in the table are empty or
null ... so hopefully this will keep the code simpler.
Thanks Again!
Bob

1. The first lot of code goes as a separate sub in the code module for the
form ( just copy and paste it)
To use this code, put the following for the after update of each combo
Call FilterTheForm

2. How to set up the form so that it will work

3. How to open the form to look the way you want


Code for 1.
-------------------------------------
Private Sub FilterTheForm()

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboHA) Then
strWhere = strWhere & "([HA] = """ & Me.cboHA & """) AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboPOLYR) Then
strWhere = strWhere & "([POLYR] = """ & Me.cboPOLYR& """) AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboPOLYR) Then
strWhere = strWhere & "([POLYR] = " & Me.cboPOLYR& ") AND "
End If

'Chop off the trailing " AND ", and use the string as the form's Filter.
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
--------------------------


code for 2.
-------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub
-----------------------------


code for 3.
-------------------------------
Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub
-----------------------------

Jeanette Cunningham

I am somewhat overwhelmed by the example. Is there a simpler example?