PDA

View Full Version : [SOLVED] Autofilter via a form - not working



Glaswegian
08-25-2005, 01:26 PM
I'm finally getting around to creating my movie database but having a problem that I just can't seem to work out.

I've created several forms, one of which is a search form. The form contains some option buttons and a textbox. You simply select a search criteria, e.g. Actor, select the format, DVD or video, and then type a name in the textbox. The code then checks the option button and runs the appropriate search. The search is simply an autofilter on the relevant sheet, with the film titles only then displayed in another form. The film titles are in column A and the actors names are in column D. Row 1 contains headings.

When I step through the code everything works fine, but when I run the code direct from the form, the code returns all the film titles and the Autofilter does not appear to have worked.

I really can't see what the problem is (probably because I've been looking at it for so long now) and would appreciate any help.



Sub ActorSearch()
Dim strSearch As String
Dim myRng As Range
Dim c As Range
strSearch = frmSearchVideo.tbxSearchString.Value
If strSearch = "" Then
MsgBox "You must type in a search value.", vbOKOnly + vbInformation, "Search criteria missing"
Exit Sub
Else
On Error Resume Next
Sheets("Video").Range("D1").AutoFilter Field:=4, Criteria1:=strSearch
Set myRng = Sheets("Video").Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
If Not myRng Is Nothing Then
For Each c In myRng
If c.Value = "Title" Then GoTo noFilms
frmSearchResults.lbxSearchResults.AddItem c.Value
Next c
frmSearchVideo.Hide
frmSearchResults.Show
Else
noFilms: MsgBox "No matches were found", vbOKOnly + vbInformation, "Information"
frmSearchResults.Hide
End If
End If
On Error GoTo 0
Sheets("Video").AutoFilterMode = False
End Sub

Thanks as always.

mdmackillop
08-25-2005, 03:32 PM
Hi Iain,
Can you post a copy of your file?

Glaswegian
08-25-2005, 03:54 PM
Hi Malcom

Even zipped the file is too large.(400K).

The code I posted is the only search code I've done. All other code is showing and hiding forms and setting the Rowsource for the comboboxes so that I can select a name from a list - nothing else so far (18 months work - you'd think I would have progressed a bit further!)

Sorry if that's not much help.

Regards

Glaswegian
08-26-2005, 04:07 AM
Solved via E-mail with Malcolm.

File corruption - all now copied to a new file.

Thanks Malcolm.