Kamis, 06 Oktober 2011

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

 

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

__._,_.___
Recent Activity:
MARKETPLACE

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

.

__,_._,___

Tidak ada komentar:

Posting Komentar