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

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