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
> >
>
Senin, 24 Oktober 2011
[MS_AccessPros] Re: Tables split, now how do I update my filters?
__._,_.___
MARKETPLACE
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar