List Box Form Filter

[mactheknife]

I want to be able to show all records from tblRecords where, The [tblUser][PositionID] = The Value selected on the list box.The problem being I don't really know how to do this with them all being on seperate tables and linked via relationships. I.e. [tblRecords] only holds the UserID, [tblUser] has the PositionID.

I also need this value to keep building towards my filter string, i.e. I can select this position ID and records from within the past 4 days.I've attached pics of the form and relationships.Thankyou.

Here is my current code:

Option Compare Database
Option Explicit
Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acListBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Cancel = True
    MsgBox "You cannot add new records to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub
Private Sub LstDays_AfterUpdate()
    Dim startDate As Date
    Dim endDate As Date
    Dim period As String
    
    period = Me.LstDays
            startDate = DateSerial(Year(Date), Month(Date), Day(Date))
    Select Case period
        Case "Today"
            endDate = DateAdd("d", 1, startDate)
        Case "Yesterday"
            endDate = startDate
            startDate = DateAdd("d", -1, startDate)
        Case "Last 4 Days"
            endDate = startDate
            startDate = DateAdd("d", -4, endDate)
        Case "Last 9 Days"
            endDate = startDate
            startDate = DateAdd("d", -9, endDate)
    End Select
    
   
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    
        'Number field example.
    If Not IsNull(Me.LstUser) Then
        strWhere = strWhere & "([User] = " & Me.LstUser & ") AND "
    End If
       
       
    'Date field example.
    If Not IsNull(Me.LstDays) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format(startDate, conJetDate) & ") AND "
    End If
    
    
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
Private Sub LstDays_BeforeUpdate(Cancel As Integer)
End Sub

 

[pr2-eugin]

You have shown some code - Good. However your description does not marry up. Is it possible, if in simple words you could explain what you are actually after, so we could help you out?

 

[mactheknife]

Pr2-eugin, thanks for the reply. Sorry I shall try and explain better.

I am trying to create a form that filters all the Report records based on the criteria that is selected in the list boxes.

This is the table whose records I am filtering.
TblReport

[/B]
[B]ReportID[/B]
[B]ReportDate
Shift[/B]
[B]OrderType[/B]
[B]Hours[/B]
[B]WorkOrderNumber[/B]
[B]Area[/B]
[B]Description
DetailedText
User

One of the criteria I want to filter by is the position of the user [PosID], I.e. Was it a "Shift Fitter", "Electrician", "Contractor" that did the job.

The problem I have is that the position ID is on a seperate table - [tblUser], however the two tables are linked via a common userID on both tables.

I have created a query that displays all the UserID's that = the current POSID. How do I then apply these UserID's to my filter? Is it possible to apply multiple values to a filter variable.

For example

UserID     PosID
1            1
2            1
3            1

4            2
5            2
6            2
7            3
8            3

If my query returns all the userID's that correspound to Pos ID (1,2 & 3) can I apply this to my filter, i.e. show all records where the userID = 1,2,3?

Hope this helps explain it a bit better, thanks for any help.

 

[pr2-eugin]

You can use the IN keyword. Something like..

SELECT someFields
FROM theTable
WHERE theField IN (1, 2, 3);

 

[jdraw]

See if this material helps.

 

[mactheknife]

I'd ideally like to keep it all filtered through the current VBA Filter and not a query.

In the same format as:

If Not IsNull(Me.LstUser) Then
strWhere = strWhere & "([User] = " & Me.LstUser & ") AND "
End If
 
If Not IsNull(Me.LstDays) Then
strWhere = strWhere & "([EnteredOn] >= " & Format(startDate, conJetDate) & ") AND "
End If
 
Me.Filter = strWhere
Me.FilterOn = True

 

[mactheknife]

Managed to figure it out,

For anyone with a similar problem, the soloution was to create a query showing records with only the userID which corresponded to the PosID and then change the record set of the form to this query:

Private Sub LstPos_AfterUpdate()
    ' Purpose: Change the form's RecordSource to only reports from this UserID.
    Dim sSQL As String
    Dim bWasFilterOn As Boolean
    ' Save the FilterOn state. (It's lost during RecordSource change.)
    bWasFilterOn = Me.FilterOn
    ' Change the RecordSource.
    If IsNull(Me.LstPos) Then
        If Me.RecordSource <> "tblReport" Then
            Me.RecordSource = "tblReport"
        End If
    Else
        sSQL = "SELECT tblReport.[UserID],tblReport.[ReportID],tblReport.[EnteredOn],tblReport.[Shift],tblReport.[OrderType],tblReport.[WorkOrderNumber],tblReport.[Description],tblReport.[Area]" & _
               "FROM tbl_Users INNER JOIN tblReport ON tbl_Users.[UID] = tblReport.[UserID]" & _
               "WHERE (((tbl_Users.[PositionID])=[Forms]![frm_SimpleSearch]![LstPos]));"
        Me.RecordSource = sSQL
    End If
    ' Apply the filter again, if it was on.
    If bWasFilterOn And Not Me.FilterOn Then
        Me.FilterOn = True
    End If
    
End Sub