VB Filters using SQL in Access 2000

I am trying to make an Access 2000 database user friendly. I want to apply filters on the database. What I want to do is have the user select a field from a drop down box, then enter a value, and have the filter display all records in a set range. So far I have started a Module that lets the user pick a field, and prompts for a value. I then am trying to apply a filter via VB, but I don't know how to display it, here is what I have so far.

Function Main()
Dim n, pnum As Integer
Dim d, od, bore As Double
Dim prompt, title, default, invalue
Dim dbload As Database
Dim rsload As DAO.Recordset
Dim sload As String

prompt = "Sort by Part Number, Wheel OD, or Bore 1:"
title = "Sort by User Value"
default = "Part Number"
invalue = InputBox(prompt, title, default)
sload = "SELECT * FROM ALL_LOADWHEELS;"
Set dbload = CurrentDb()
Set rsload = dbload.OpenRecordset(sload, dbOpenDynaset)
If invalue = "Part Number" Then
pnum = InputBox("Enter the Part Number.", , 0)
rsload.Filter = "PART_NUMBER = pnum"
MsgBox "You chose the Part Number " & pnum & "."
End If
rsload.Close
dbload.Close
End Function

I have 2 problems.
1. How do I switch the MsgBox for invalue to a dropdown box where the values are Field Names?
2. How do I display my filter? I tried rsload.FilterOn = True, but it wasn't found.

1. How do I switch the MsgBox for invalue to a dropdown box where the values are Field Names?

You can't - use a form with combo box and select field list of a table for the row source.

2. How do I display my filter? I tried rsload.FilterOn = True, but it wasn't found.

Forget filtering. Build the SQL string after all values are selected and/or specified. This could be a parameter SQL or similar (there are more ways to skin a cat).

Create the recordset after the string is complete for results display or whatever.

Ok I decided to use a form instead. Now I am trying to populate a list box with a RowSourceType function. My function however is not working and I am getting the following error.

'SearchResults()' may not be a valid setting for the RowSourceType property, or there was a compile error in the function.

Here is my function:


Function SearchResults(fld As Control, id As Variant, _
row As Variant, col As Variant, _
code As Variant) As Variant

Dim Search As Variant
Select Case code
Case acLBInitialize ' Initialize.
Search = "Select * From ALL_LOADWHEELS;"
Case acLBOpen ' Open.
Search = Timer
Case acLBGetRowCount ' Get rows.
Search = -1
Case acLBGetColumnCount ' Get columns.
Search = 33
Case acLBGetColumnWidth ' Get column width.
Search = -1
Case acLBGetValue ' Get the data.
Search = "Select * From ALL_LOADWHEELS;"
End Select
SearchResults = Search
End Function

I am going to eventually add more to the GetValue part, but I am just trying to get this basic search to work.