Listbox as filter to open form 1

[newestAF]

Since I was kindly referred to go somewhere else and ask this question:

I have a listbox linked to a table showing different ranges of numbers from 0-9999. What I want to do it have the user click on one option, then ok and open a form based off the number range filter. Form's RS is an updatable query. I've searched a bunch of forums and know this can't be that complicated, but I just can't figure it out.

 

[dhookom]

Please provide all significant information with your questions. We don't know:
-your list box row source
-field in form's record source to filter on
-data type of field to filter on
-form name
-list box name


Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

Listbox row source is from table called "Range". The listbox is on a form called "Select Range".
The main form's RS (form called ITF) is a query called Query1. I'm using a field not visible on the form as the filter ("Last 4"). Last 4 is a long integer as well as the listbox row source. The user selects what range they work then click ok. On Ok, I need the ITF form to open showing only the fields they work. The data Last 4 is actually the last 4 of the SSAN. Wasn't sure how to get it to read and sort by last 4 of a text field so I recreated another column as number instead.

 

[MajP]

I have a table called tblRanges

rangeText   rangeMin     rangeMax
1-10           1          10
11-20          11         20
21-30          21         30
31-40          31         40
All            0          100

my listbox sql and displaying only the rangeText

SELECT [tblRanges].[rangeText], [tblRanges].[rangeMax], [tblRanges].[rangeMin] FROM [tblRanges] ORDER BY [rangeMin];

my code

Public Sub filterForm()
  Dim minRng As Integer
  Dim maxRng As Integer
  Dim strFilter As String
  minRng = Me.lstFilter.Column(2)
  maxRng = Me.lstFilter.Column(1)
  strFilter = "supplierID >=" & minRng & " AND supplierID <= " & maxRng
  
  Me.Filter = strFilter
  Me.FilterOn = True
End Sub

Private Sub lstFilter_AfterUpdate()
 Call filterForm
End Sub

 

[MajP]

I had the listbox at the top of my form to filter, but if you are opening a form it is the same idea but the strfilter is used in a docmd.openform