Hi guys,
I have an annoying issue with the Me.Filter command and NEXT. I don't know if there is a quick fix or whether I need to code something up explicitly to solve the problem.
Consider this: It's a button which when pressed, you enter a search clue to an input box and relevant results of the search are output to the form.
Private Sub SearchStkID_Click()
Dim S As String
S = InputBox("Enter Stock ID", "Stock ID")
If S = "" Then Exit Sub
Me.Filter = "STK_ID LIKE ""*" & S & "*"""
' *** Me.OrderBy = "STK_ID"
Me.FilterOn = True
' *** Me.OrderBy = True
End Sub
Forget the ' *** entries for the minute... The filter code above (without the extra bits) does indeed filter a correct group based on the input and I can cycle through the entries with next and previous buttons.
However, the filter outputs the records in record order - not a sorted group order. Therefore an entry with a STK_ID (it's a unique stock code) of say, VA1005 can come out before a record of VA1001 if its record number happens to be lower. It becomes a bit confusing when you next, next, next through the records and the stock codes appear to be displayed randomly. Sure, it works ok but I think users are a little confused by it.
I'd like the filter to first sort the entries by STK_ID (Stock Code ID) rather than record number so that the first one appears first then pressing next next would yield VA1001, VA1002, VA1003 etc...
I tried adding the code marked with '*** above to try an achieve a sort of the data but that doesn't really work. I'm not sure if the code is valid or not for the purpose I'm requiring or I'm going about this in the wrong way completely. Documentation of "OrderBy" seems to indicate that it would work.
I'm new at this and although I've learnt a lot over the last 6 months (loads actually with some help from the guys here) this one has me going round in circles. I would expect the code (with the extra bits) to give me the first record (sorted) but it doesn't. Maybe I can't use the two commands together? Or is there a special way they must be nested?
How do I filter (group) entries as above but arrange it so that it always shows the first entry in the form in order? Am I being stupid here?
Also, I guess, a standard NEXT finds the next record number as well - not the next grouped record but I can deal with that.
Can I achieve the result I need without a large pile of code? There are several such search filters on my form and I'd like them all to behave as above if possible. If I can get this one to work then I can apply the method to the others as necessary. I'm sure there must be an easy way to do this as I'd expect everyone would want this behaviour.
Or should I be using SQL commands instead? I think I need a nudge to "see the wood for the trees" !
Any feedback or advice would help... thanks.
I have an annoying issue with the Me.Filter command and NEXT. I don't know if there is a quick fix or whether I need to code something up explicitly to solve the problem.
Consider this: It's a button which when pressed, you enter a search clue to an input box and relevant results of the search are output to the form.
Private Sub SearchStkID_Click()
Dim S As String
S = InputBox("Enter Stock ID", "Stock ID")
If S = "" Then Exit Sub
Me.Filter = "STK_ID LIKE ""*" & S & "*"""
' *** Me.OrderBy = "STK_ID"
Me.FilterOn = True
' *** Me.OrderBy = True
End Sub
Forget the ' *** entries for the minute... The filter code above (without the extra bits) does indeed filter a correct group based on the input and I can cycle through the entries with next and previous buttons.
However, the filter outputs the records in record order - not a sorted group order. Therefore an entry with a STK_ID (it's a unique stock code) of say, VA1005 can come out before a record of VA1001 if its record number happens to be lower. It becomes a bit confusing when you next, next, next through the records and the stock codes appear to be displayed randomly. Sure, it works ok but I think users are a little confused by it.
I'd like the filter to first sort the entries by STK_ID (Stock Code ID) rather than record number so that the first one appears first then pressing next next would yield VA1001, VA1002, VA1003 etc...
I tried adding the code marked with '*** above to try an achieve a sort of the data but that doesn't really work. I'm not sure if the code is valid or not for the purpose I'm requiring or I'm going about this in the wrong way completely. Documentation of "OrderBy" seems to indicate that it would work.
I'm new at this and although I've learnt a lot over the last 6 months (loads actually with some help from the guys here) this one has me going round in circles. I would expect the code (with the extra bits) to give me the first record (sorted) but it doesn't. Maybe I can't use the two commands together? Or is there a special way they must be nested?
How do I filter (group) entries as above but arrange it so that it always shows the first entry in the form in order? Am I being stupid here?
Also, I guess, a standard NEXT finds the next record number as well - not the next grouped record but I can deal with that.
Can I achieve the result I need without a large pile of code? There are several such search filters on my form and I'd like them all to behave as above if possible. If I can get this one to work then I can apply the method to the others as necessary. I'm sure there must be an easy way to do this as I'd expect everyone would want this behaviour.
Or should I be using SQL commands instead? I think I need a nudge to "see the wood for the trees" !
Any feedback or advice would help... thanks.
