Nigel-
Consider splitting Batch number into two parts. How many digits are the batch and how many are the serial? In AfterUpdate of Part, filter both the batch and serial combos. In AfterUpdate of batch, filter serial. Your two Row Sources will look something like:
SELECT DISTINCT Left(BatchNumber, 3) As Batch FROM tblRoutecard ORDER BY BatchNumber;
and
SELECT DISTINCT Right(BatchNumber, 5) As Serial FROM tblRoutecard ORDER BY BatchNumber;
To filter the serial combo, do something like:
Me.SerialNumberFilter.RowSource = "SELECT DISTINCT Right(BatchNumber, 5) As Serial " & _
"FROM tblRoutecard " & _
"WHERE BatchNumber LIKE '" & Me.BatchNumberFilter & "*' " & _
"ORDER BY BatchNumber;"
For the filter for the form build, do
mFilter = "BatchNumber LIKE '" & Me.BatchNumberFilter & "*'"
and
mFilter = "BatchNumber LIKE '*" & Me.SerialNumberFilter & "*"
Does that make sense?
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Nov 16, 2016, at 12:49 PM, nigel@pegasusconsulting.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John,
Here's a screenshot of the form...
https://xa.yimg.com/df/MS_Access_Professionals/route_card_form_LI.jpg?token=I54UAmUvprCosAKj1dSthvtVbHCmywDv0wKa6p_1BfetIU8DXh7sXvFbajPBgEbOUHaB_xCxQPjqpSkbqdSwBr5UzP0Qv1f3C18lw15OHwZEWbWZ0Q&type=download Row sources...
When the form opens the rowsources are set with the form's OnCurrent event...
Me.CustomerFilter.RowSource = "SELECT DISTINCT Customer FROM tblRoutecard ORDER BY Customer"
Me.PartNumberFilter.RowSource = "SELECT DISTINCT PartNumber FROM tblRoutecard ORDER BY PartNumber"
Me.BatchNumberFilter.RowSource = "SELECT DISTINCT BatchNumber FROM tblRoutecard ORDER BY BatchNumber"
Customer combo - SELECT DISTINCT Customer FROM tblRoutecard ORDER BY Customer;
Part number combo - row source is code on the AfterUpdate event of the customer combo...
Private Sub CustomerFilter_AfterUpdate()
Me.PartNumberFilter.RowSource = "SELECT DISTINCT PartNumber FROM tblRoutecard " & _
"WHERE Customer = '" & Me.CustomerFilter & "' ORDER BY PartNumber"
End Sub -
Batch number combo - row source is code on the AfterUpdate event of the part number combo...
Private Sub PartNumberFilter_AfterUpdate()
Me.BatchNumberFilter.RowSource = "SELECT DISTINCT BatchNumber FROM tblRoutecard " & _
"WHERE PartNumber = '" & Me.PartNumberFilter & "' ORDER BY BatchNumber"
End Sub
And here's the code attached to the Apply Filter button...
Private Sub ApplyFiltersButton_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.CustomerFilter) And IsNull(Me.PartNumberFilter) And IsNull(Me.BatchNumberFilter) Then
Me.Requery
End If
If Len(Me.CustomerFilter) > 0 Then
'CustomerSupplier is selected so add it to the string...
mFilter = "[Customer]= '" & Me.CustomerFilter & "'"
End If
If Len(Me.PartNumberFilter) > 0 Then
'Keyword is selected so add it to the string...
If Len(mFilter) > 0 Then
'String already has something in it from CustomeSupplierFilter, so add AND first...
mFilter = mFilter & " AND "
End If
'If CustomerSupplierFilter only following is used, if keyword also the AND bit above is also used
mFilter = mFilter & "[PartNumber]= '" & Me.PartNumberFilter & "'"
End If
If Len(Me.BatchNumberFilter) > 0 Then
'Keyword is selected so add it to the string...
If Len(mFilter) > 0 Then
'String already has something in it from CustomeSupplierFilter and PartNumberFilter, so add AND first...
mFilter = mFilter & " AND "
End If
'If CustomerSupplierFilter and PartNumberFilter only following is used, if keyword also the AND bit above is also used
mFilter = mFilter & "[BatchNumber]= '" & Me.BatchNumberFilter & "'"
End If
I'm sure there will be much tidier/simple ways to do this!!!
Nige
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar