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
>
Tidak ada komentar:
Posting Komentar