I want to use combo boxes to locate specific records

B

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.
 
J

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
 
B

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

Jeanette said:
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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top