I am now using a combo as per your earlier reply, I guess I was wondering how
your approach using a combobox with a rowsource would work to filter the data
compared to my version of coding with no rowsource.
TonyT..
:
I haven't had time to look at your code in detail, but I still think the
combo box is the best way, but I can't remember if you said why you can't use
a combo.
--
Dave Hargis, Microsoft Access MVP
:
Was formed out of trying many different approacheas at getting round my
original trailing space problem, now I can't see the wood for the trees :/
What approach would you suggest / consider more normal for this type of
application?
TonyT..
:
Thanks, Tony.
Interesting approach.
--
Dave Hargis, Microsoft Access MVP
:
Excuse the poor coding, only at mock up stage!!
Private Sub cboPtFilt_Change()
Dim strFilt1 As String
Dim strOldVal As String
On Error Resume Next
If Not IsNull(Me![cboPtFilt].Text) Then
If Not IsNull(Me![cboPtFilt].OldValue) Then
strOldVal = Me![cboPtFilt].OldValue
Else: strOldVal = ""
End If
sglStrCnt = Len(Me![cboPtFilt].Text)
strFilt1 = Me![cboPtFilt].Text
'Me.FilterOn = False
Me.Filter = "left(PtTbl.PtNum, " & sglStrCnt & ") = '" & strFilt1 &
"'"
strPtFilt = "left(PtTbl.PtNum, " & sglStrCnt & ") = '" & strFilt1 &
"'"
Me.FilterOn = True
If Me.RecordsetClone.RecordCount < 1 Then
If strOldVal <> "" Then
Me![cboPtFilt] = strOldVal
sglStrCnt = Len(strOldVal)
Me.Filter = "left(PtTbl.PtNum, " & sglStrCnt & ") = '" &
strOldVal & "'"
strPtFilt = "left(PtTbl.PtNum, " & sglStrCnt & ") = '" &
strOldVal & "'"
Beep
Me![Text34] = ""
Me![Text39] = ""
Me.Refresh
Else:
Me![cboPtFilt] = strOldVal
Me.FilterOn = False
Me![Text34] = ""
Me![Text39] = ""
strPtFilt = "pttbl.ptnum = '*'"
Beep
Me.Refresh
End If
End If
End If
Me![Text34] = ""
Me![Text39] = ""
strDesc = "*"
strDesc2 = "*"
Me![cboPtFilt].SetFocus
Me![cboPtFilt].SelStart = sglStrCnt
On Error GoTo 0
End Sub
Text34 & text39 are further search fields based on description rather than
part numbers.
Now it works time to sit down and code it properley (and *alot* more
efficiently)
thanks again,
TonyT..
:
Great!
Would you do us a favor and post the code. I would like to see how that
works.
Thanks,
--
Dave Hargis, Microsoft Access MVP
:
I have it working exactly as I want, reducing the 8000 or so parts in the
continuous part of the form down as I type each character into the combobox,
without a rowsource & unbound. The code is identical to how I had it with the
textbox, but by virtue of being a combobox (I guess) the focus never leaves
the cbo so the trailing spaces don't get trimmed.
Inspired! (even if it wasn't what you had in mind)
TonyT..
:
The cbo does need a row source. It should be a query based on the part
number. By bound, I meant bound to the form's recordset.
--
Dave Hargis, Microsoft Access MVP
:
:
The native behavior of a combo box is what you are writing code to do with a
text box. It would be less code and, I think (without actually testing) it
would be faster. I don't really see any reason a combo box would not be a
better solution.
The only "gotcha" I can think of is if there are more than 65,536 parts in the
list (the limit for the number of records displayed in a combo).
John W. Vinson [MVP]
Seems to work fine with exactly the same code as I had originally with a
textbox, whilst leaving the cbo unbound with no rowsource at all.
Surely this will nullify the 65,536 limit too! Or am I missing something?
thanks to both you and mostly Klatuu :/
TonyT..
