Awesome Search Form

[browny]

Hi all,
I downloaded a search form from the forum a couple of weeks ago and I have just got around to setting it up on my database and it works really well. I have attached it below and recommend it to any others looking for a quick and simple Search function for their database.
I have however come across a problem that I cannot figure out and think it is to do with the changes I had to make to the code to suit my database. I cannot attach a copy of the database so will do my best to explain.
Originally, I had the "Search" text box searching for a Vehicle Registration number(AAA123) or a Fleet Number(123). I designed the form and table like the attached sample and it was working great. There is another field in my table with the vehicles driver and I was asked if I could set it up to search on that field too. As with the other query criteria, I added Like "*" & [Forms]![frmFleetList]![Search2] & "*" to the "Driver" field in the query. Using the text box "Search" still works and returns results as the text is entered however when I select the record in the list box I want to display, some of the records do not update the information in the fields below the list box and the error procedure below is activated.
Private Sub QuickSearch_AfterUpdate()

DoCmd.Requery
Me.RecordsetClone.FindFirst "[FLEET] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub
If anyone could tell my why they think this is happening or where I have gone wrong it would be appreciated.
This is a great search tool and as I said above, I recommend it to anyone looking for a search function.

Regards
Craig

 

[browny]

Hi all
More information on my problem, both the form and the unbound list box get the information from the same query. The list box displays the "NA" value as in the table but the form does not. Not sure what this means exactly but could have some bearing on the problem.
Craig

 

[bonekrusher]

Check which column the list box is bound to.

 

[browny]

The list box is 'unbound', the row source is from qryFleetList. The form record source is from qryFleetList
Craig

 

[bonekrusher]

The list box has a bound column. When you clicmk on it, it returns the value of the bound column (defaults to 1). try changing it.

 

[browny]

Hi Bonekrusher
Found out what you mean, I changed the bound column, as you said, default to 1 and tried changing it but it made no difference. The error message changed each time to reflect the column I had bound the list box to. Hope this makes sense. Will keep looking at other options.
Craig

 

[browny]

Hi there
Solved the problem. The bound field of the list box contained Null values and these were the results returning errors. I added a default value in the field criteria of the table and the code no longer returns errors.
Thanks for the help.
Regards
Craig

 

[bonekrusher]

Glad to help.

Bones