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

 

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

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (5)

Recent Activity:

• New Members 10

Visit Your Group

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___