Kamis, 06 Oktober 2011

[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

__._,_.___
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