Marshall Barton said:
Alasdair said:
Alasdair said:
:
Alasdair wrote:
After some very helpful advice from Marsh Barton I've set up a form which
allows users to search a database using a series of If statements like so:
If Not IsNull(Me.txtb4) Then
strWhere = strWhere & " AND textfield2 Like ""*" _
& Me.txtb4 & "*"""
End If
And the strWhere string is then used as the Where condition of
Docmd.Openform statement which opens a viewform.
The search works well if there are entries which match the criteria but if
there are none then none of the fields in the viewform are displayed and none
of the navigation buttons I placed on the viewform are visible. So the user
has to close down the window and select the search option again from the
switchboard.
Ideally I'd like the user to see a message saying something like "No hits
found on those critera" and be taken back to the search form automatically
but I'm not sure how to do that.
Use the form's Load event to check if it has an empty
dataset. If it does, then the form can display a message
and close itself:
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No data matches your cirteria"
DoCmd.Close acForm, Me.Name, acSaveNo
End If
Thanks a lot for the rapid advice, you are a huge help. The load event code
works exactly as it should.
As a follow on question, my validation rule on the date fields is
IsDate([StartDate])=True
which stops any non-date data entry. But if a date is entered and then the
user wants to delete it then my validation rule doesn't let them, it forces
the date to remain. I tried changing the rule to
IsDate([StartDate])=True Or [StartDate]=""
but it didn't work. I'd like the user to be able to go back and edit their
search fields if their search gives no results but I can't seem to let the
date fields be empty or dates. Ideas are appreciated.
Are you sure you need the validation rule? A date/time type
field can not be assigned a non-date value and the datatype
violation seems to appear before the validation rule is
checked so I don't see the need. Besides, without the
validataion rule, the field can be cleared (set to Null),
without worrying about it.
If you really want the validation rule, then I think you
want:
IsDate([StartDate]) Or IsNull([StartDate])
Marsh,
I've probably done this in a clumsy way. When I built the search form I
didn't want the user to accidentally add a new record or edit an existing
record so I unlinked all of the search fields from the table fields. So they
are basically just boxes which are filled and then searched on. So the start
and end date text boxes don't have any inherent validation, hence my attempt
to do something with the validation rule which isn't working very well.
I tried the Validation rule you suggested but now the fields don't accept
dates entered in a reasonable dd/mm/yyyy format. I guess I can actually do
the validation in the VBA that processes the dates entered, it might be the
easiest way.
FYI, I've played about with the search code to now cope with multiple
keywords entered in the search fields by breaking the string into different
words (WordFind function) and building a query for all of the words
(QueryString function). Seems to work ok.
Private Function WordFind(TextEntered)
Dim WordFinish As Integer, Wordstart As Integer
' Wordfinish increments as each character in the string is checked
' Wordstart is assigned at the beginning of the word
Dim Words(20) As String
' All words found by this procedure
Dim WordCounter As Integer
' Wordcounter tracks how many words are recorded
Wordstart = 1
WordCounter = 0
For WordFinish = 2 To Len(TextEntered)
If Mid(TextEntered, WordFinish, 1) <> " " And _
Mid(TextEntered, WordFinish - 1, 1) = " " Then
' A new word has started
Wordstart = WordFinish
' So set Wordstart to this character
End If
If Mid(TextEntered, WordFinish, 1) = " " And _
Mid(TextEntered, WordFinish - 1, 1) <> " " Then
' If a space is found after a letter then a word has finished
Words(WordCounter) = Mid(TextEntered, Wordstart, WordFinish -
Wordstart)
' Save the word
WordCounter = WordCounter + 1
' Set the counter ready for the next word to be found
End If
Next WordFinish
Words(WordCounter) = Mid(TextEntered, Wordstart, WordFinish - Wordstart)
' Store the last word found
WordFind = Words
' Return the array of words
End Function
Function QueryString(Field As String, WordList) As String
Dim WordCounter As Integer
' Counts the number of words in Wordlist
Dim TempString As String
' Temporary query string built
Do While WordList(WordCounter) <> ""
' For all words on the wordlist
TempString = TempString & " AND " & Field & " like ""*" _
& WordList(WordCounter) & "*"""
' Build an SQL query to look for all words or word fragments
WordCounter = WordCounter + 1
Loop
QueryString = TempString
' Output the search string
End Function
