Steve S,
Thank you so much for reviewing my question. Regarding the #Name?
error, both the text boxes (checked for repeats) and refrences clear.
The problem must be something different. Under different
circumstances, I would not hesitate to send you a copy of the database
but too much of the data is sensitive, and I don't know how to go
about editing it (so that you can understand and work with it)
without removing a considerable portion of the data (if not all of
it). However, I can copy/paste the code without hesitation. So you
know: The continuous form example I have adapted to only use text
boxes with partial searches. This is why a lot of the original form
is commented out.
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they
wish, _
and results are shown one per line.
'Note: Only records matching ALL of the criteria are returned.
'Author: Allen Browne (
[email protected]), June 2006.
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
'[2007-08ScholarshipMaster].
If Not IsNull(Me.txtFilterUMID) Then
'
strWhere = strWhere & " ([UMID] Like ""*" & Me.txtFilterUMID &
"*"") AND "
End If
'Another text field example. Use Like to find anywhere in the
field.
'Me. -> Me.txtFilterName
If Not IsNull(Me.txtFilterName) Then
strWhere = strWhere & " ([Name] Like ""*" & Me.txtFilterName &
"*"") AND "
End If
'Number field example. Do not add the extra quotes.
If Not IsNull(Me.txtProjectGrant) Then
strWhere = strWhere & " ([ProjectGrant] LIKE ""*" &
Me.txtProjectGrant & "*"") AND "
End If
'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
'If cboFilterResidency = -1 Then
' strWhere = strWhere & "([Residency] = True) "
'ElseIf cboFilterResidency = 0 Then
'strWhere = strWhere & "([Residency] = False) "
'End If
'Date field example. Use the format string to add the # delimiters
and get the right international format.
'If Not IsNull(txtStartDate) Then
'strWhere = strWhere & "([EnteredOn] >= " &
Format(txtStartDate, conJetDate) & ") AND "
'End If
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
'If Not IsNull(txtEndDate) Then 'Less than the next day.
'strWhere = strWhere & "([EnteredOn] < " & Format(txtEndDate +
1, conJetDate) & ") 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
Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show
all records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.FilterOn = False
End Sub
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
'Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially
show no records.
'Me.MultiValueSearchForm].Filter = "(False)"
'Me.MultiValueSearchForm].FilterOn = True
'End Sub
Hi Michelle,
The "#Name?" error (usually) means Access cannot find the object being
refered to. And usually it is a spelling error........ like a field name that
is "txtCustomer" in the table but is "txtCustomers" (with an "s") in the
query/code.
Check that the control source property of the controls refers to the right
fields in the table/query.
Sorry.......without seeing the code and the form I can't be much more help.
If you want, do a "Compact & repair", then WinZip the mdb and email it to
me. (remove sensitive data)
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
- Show quoted text -
