ACCESS FEED: Re: [MS_AccessPros] Tables split, now how do I update my filters?

Kamis, 06 Oktober 2011

Re: [MS_AccessPros] Tables split, now how do I update my filters?

Exactly John...that's what has me failing to get the code right!!!

I'll try yours later, many thanks.

Nige

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Nige-
>
> No problem with the PartNumber and SDNNumber filters, but because the
> SerialNumber is in the subform recordset, you have to do something tricky.
>
> If Len(Me.SerialNumberFilter) > 0 Then
> 'Serial number is selected so add it to the string...
> If Len(mFilter) > 0 Then
> 'String already has something in it from PartNumber and SDN Number, so add
> AND first...
> mFilter = mFilter & " AND "
> End If
> 'If Part Number only following is used, if keyword also the AND bit above is
> also used
> ' SerialNumber is in a related table, so use a subquery:
> mFilter = mFilter & _
> "[Number] IN (SELECT [Number] FROM tblSerialNumbers " & _
> " WHERE [SerialNumber]= '" & Me.SerialNumberFilter & "')"
> End If
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nigelhjackson
> Sent: Thursday, October 06, 2011 3:02 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Tables split, now how do I update my filters?
>
> Hi all,
>
> Thanks to AJT and for advice regarding my warranty database, and entering a
> single header (for the delivery note number and date and part number, separate
> from the list of serial numbers). In fact I realised I know how to do this as
> our other database works exactly this way...doh!
>
> I have 2 table as follows...
>
> tblSDNHeader - fields Number, SDNNumber, SDNDate, PartNumber
> tblSerialNumbers - fields Number, SerialNumber, WarrantyExpiryDate,
> WarrantyDaysLeft, WarrantyPeriodDays, Quantity
>
> The form used to display enter data are frmSerialAndSDN for the header
> information, and subform subfrmSerialDetail. The fields Number join the 2 forms
> and are the primary keys.
>
> So, I have in the previous single-table version of the database a set of combo
> boxes and an apply button that act as filters; the user can select data in any
> or all of the combos (PartNumberFilter, SDNNumberFilter, SerialNumberFilter)
> then click apply to filter the list to only records that match the filters used.
>
> This was all good when the data was in a single table and displayed in a
> continuous form, but now I have 2 tables and a form/sub-form I can't get the
> filters to work any more. Below is the previous code that worked, can someone
> help to correct it for the 2 table/form/sub-form setup please?
>
> Cheers, Nige
>
>
> Private Sub BtnApplyFilter_Click()
>
> 'Set the filter string
> Dim mFilter As String
> 'ensure the string is empty to start
> mFilter = ""
> 'ensure the filter is off and empty to start
> Me.FilterOn = False
> Me.Filter = ""
>
> 'if all filter selectors are empty, do nothing
> If IsNull(Me.PartNumberFilter) And IsNull(Me.SDNNumberFilter) And
> IsNull(Me.SerialNumberFilter) Then
> Me.Requery
> End If
>
> If Len(Me.PartNumberFilter) > 0 Then
> 'Part number is selected so add it to the string...
> mFilter = "[PartNumber]= '" & Me.PartNumberFilter & "'"
> End If
>
> If Len(Me.SDNNumberFilter) > 0 Then
> 'SDN number is selected so add it to the string...
> If Len(mFilter) > 0 Then
> 'String already has something in it from PartNumber, so add AND first...
> mFilter = mFilter & " AND "
> End If
> 'If Part Number only following is used, if keyword also the AND bit above is
> also used
> mFilter = mFilter & "[SDNNumber]= '" & Me.SDNNumberFilter & "'"
> End If
>
> If Len(Me.SerialNumberFilter) > 0 Then
> 'Serial number is selected so add it to the string...
> If Len(mFilter) > 0 Then
> 'String already has something in it from PartNumber and SDN Number, so add
> AND first...
> mFilter = mFilter & " AND "
> End If
> 'If Part Number only following is used, if keyword also the AND bit above is
> also used
> mFilter = mFilter & "[SerialNumber]= '" & Me.SerialNumberFilter & "'"
> End If
>
> 'Filter the form
> If Len(mFilter) > 0 Then
> Me.Filter = mFilter
> Me.FilterOn = True
> Else
> Me.FilterOn = False
> Me.Filter = ""
> End If
>
> Me.Requery
> mFilter = ""
>
> End Sub
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>