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

OK...will do on Wednesday morning as I'm going away now until very last tomorrow night.

Nige

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Nige-
>
> OK, let's start over. What's the Record Source of the outer form? (The SQL,
> please if it's a query, the list of relevant fields if it's a table.) What's
> the Record Source of the subform? (Ditto on the SQL/fields.) What is in the
> search field? (I assume the control is unbound.)
>
> 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: Monday, October 24, 2011 9:38 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Tables split, now how do I update my filters?
>
> I tried this first (as it was your original code suggestion, but when I ran the
> filter I got the enter parameter value pop up asking for Number?
>
> There is a SerialNumber field in tblSerialAndSDN (which is the source of the
> sub-form), and yes, the Filters are applied in the outer form. The outer form
> data now comes from qrySDNHeader which has fields SDNNumber, SDNDate,
> PartNumber, Quantity, ID (ID is just an autonumber field used as the primary
> key).
>
> SOrry, I have to ask 'what next' as I am totally confused!?
>
> Nige
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Nige-
> >
> > Is there a SerialNumber field in tblSerialAndSDN? If this is a filter applied
> > to the outer form that uses tblSDNHeader as its Record Source, then I think it
> > should be:
> >
> > mFilter = mFilter & "[Number] IN (SELECT [Number] FROM tblSerialNumbers" & _
> > " WHERE [SerialNumber]= '" & Me.SerialNumberFilter & "')"
> >
> >
> > 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: Monday, October 24, 2011 3:09 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Tables split, now how do I update my filters?
> >
> > Hi John,
> >
> > I've been away so am just looking at the filters again.
> >
> > Your suggested code for the last line (where SerialNumber is in another table)
> > doesn't work
> >
> > mFilter = mFilter & "[SerialNumber] IN (SELECT [SerialNumber] FROM
> > tblSerialAndSDN " & _
> > " WHERE [SerialNumber]= '" & Me.SerialNumberFilter & "')"
> >
> > (I've replaced your Number with SerialNumber, and your tblSerialNumbers with
> > tblSerialAndSDN)
> >
> >
> > When I run the filter using the SerialNumberFilter (on its own for testing...I
> > haven't tried combined with the other filters yet!) I get a pop up 'Enter
> > Parameter Value' asking me for SerialNumber.
> >
> > Can you help again please?
> >
> > Cheers, Nige
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "nigelhjackson" <nigel@>
> > wrote:
> > >
> > > 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
> > > >
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>