Solved Help with dynamic search on form with read only recordset (1 Viewer)
- Thread starter slharman1
- Start date
I had this problem before, when the query is read only the code does not work.
As soon as I remove the subquery everything works as it should.
- Local time
- Yesterday, 17:40
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. Pardon me for jumping in, but is this related to your other thread?
(1) Please help with code and ability to edit records on form | Access World Forums (access-programmers.co.uk)
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
With this code you can get a very frustrating and difficult to resolve error. When you change the textbox and filter the form even though you are typing in the textbox, if no records are returned the control loses focus. Seems impossible, but it does. Even if you try to set focus you cannot and thus the error.
There are a couple work arounds Depending on how the form is set up. Can you give a screen shot?
There are a couple work arounds Depending on how the form is set up. Can you give a screen shot?
- Local time
- Yesterday, 17:40
- Joined
- Oct 29, 2018
- Messages
- 21,454
Hi. Perhaps, for now, you could just simply ignore that specific error. For example:
Just a thought...
Code:
...
ErrHandler:
If Err.Number <> 2185 Then
MsgBox Err.Description, vbExclamation
End If
End Sub
- Local time
- Yesterday, 17:40
- Joined
- Aug 30, 2003
- Messages
- 36,124
Interesting, and I suppose this would explain why it worked when the query was editable (the new record).
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
I do not fully understand this, not sure if it is a bug. But it is the most confusing error.
One simple work around is to make your form as a main form with a subform. You can make that look the same as a Header, detail section.
The way I do it in my Find as you type form is check the record count after the change event. If say the user Types Bal then z and there is no Balz* I do not try to set the sel or read the .text. I pop up a message "No records with Balz" and then roll it back to "bal".
One simple work around is to make your form as a main form with a subform. You can make that look the same as a Header, detail section.
The way I do it in my Find as you type form is check the record count after the change event. If say the user Types Bal then z and there is no Balz* I do not try to set the sel or read the .text. I pop up a message "No records with Balz" and then roll it back to "bal".
Find as we typed
Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...
www.access-programmers.co.uk
Last edited:
- Local time
- Yesterday, 17:40
- Joined
- Oct 29, 2018
- Messages
- 21,454
Thanks for this. You said a couple, though. What was the other one? Just curious...
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
You can look at my FAYT form class that turns any form into a FAYT. You could save a ton of code using this. It would remove probably a hundred lines of code per form or more.
In the FAYT form example I purposely check for 0 records returned for this exact reason. I could not defeat the error by trying to reset the focus.
In the FAYT form example I purposely check for 0 records returned for this exact reason. I could not defeat the error by trying to reset the focus.
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
That was two
1. Instead of the search box in the header with a detail section, make it a main form with a tabular subform. For some reason this will work. You can format it to look almost the same.
2. Check that records are returned by the search. If not roll the search back.. If no records returned by BALZ strip it back to BAL which will return records. Do not try to search for BALZW
You can try to set the focus before calling the .text method, and it will still tell you that you have to set the focus. It is as if you cannot set the focus if no records exist in the detail section. However, that is not completely true which makes it more confusing.
Code:
Private Sub txtContactFilter_Change()
Dim sText As String
Dim strFilter As String
On Error GoTo ErrHandler
sText = Me!txtContactFilter.Text
If sText <> "" Then
strFilter = "[Contact] Like '*" & sText & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.txtContactFilter
.SetFocus
.Value = sText
.SelStart = Len(sText)
.SelLength = 0
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End Subassume it fails at the line telling you that you must set the focus to txtContactFilter
sText = Me!txtContactFilter.Text
You can add this before
me.TxtContactFilter.Setfocus
msgbox me.activeControl
You have set the focus again and it will show that txtcontactfilter is the activecontrol. Which means it has focus. Still the next line will fail saying it needs the focus.
So this code will fail when no records are returned, and I am guessing it depends if the query is editable or not.
- Local time
- Yesterday, 17:40
- Joined
- Oct 29, 2018
- Messages
- 21,454
Excellent! Thanks for the clarification. Cheers!
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
Code:
mTextBox.SetFocus
If Not Trim(mTextBox.Text & " ") = "" Then
mTextBox.Value = mTextBox.Text
mForm.Filter = getFilter(mTextBox.Text)
mForm.FilterOn = True
If mForm.Recordset.RecordCount = 0 Then
MsgBox "No items matched filter " & vbCrLf & mForm.Filter, vbInformation, "No Items Found"
mForm.FilterOn = False 'needed to set focus on textbox
DoEvents
mTextBox.SetFocus
mTextBox.Value = Left(mTextBox.Text, Len(mTextBox.Text) - 1)
FilterForm
End If
Else
Call unFilterForm
End IfHere is the code out of the class module, but you can replicate similar steps. So I check the record count and if zero records I have to first remove the filter. This allows me to reference the text box text. Then I can roll it back one character, then I can reapply the filter.
Most people say they like this interface. If you return 0 records it sends you back to the last string where you had records and you can continue searching from there.
So Balz automatically rolls back to Bal and then you can type Bald
Find as we typed
Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...
www.access-programmers.co.uk
Last edited:
Yes I had a similar problem before just getting it to work, so when I went to find that thread, I accidently posted their.
not sure how to post a screen shot.
I am still a little new to all of this, are you saying that your code here will replace my code (with the correct field names of course)?
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
Personally I would write one procedure so you do not have to update each and every one. But if you want to try one. Try this
Code:
Private Sub txtNumberFilter_Change()
Dim sText As String
Dim strFilter As String
' On Error GoTo ErrHandler
sText = Me!txtNumberFilter.Text
If sText <> "" Then
strFilter = "[OrderNumber] Like '*" & sText & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records match " & Me.Filter, vbInformation, "No Match"
Me.FilterOn = False
DoEvents
Me.txtNumberFilter.SetFocus
sText = Left(Me.txtNumberFilter.Text, Len(txtNumberFilter.Text) - 1)
Me.txtNumberFilter.Value = sText
Me.Filter = "[OrderNumber] Like '*" & sText & "*'"
Me.FilterOn = True
End If
With Me.txtNumberFilter
.SetFocus
.Value = sText
.SetFocus
.SelStart = Len(sText)
.SelLength = 0
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End SubWhen you say write one procedure , are you referring to adding the functions and subs you are calling for in the previous code you posted?
I will try this out, thank you again!
MajP
You've got your good things, and you've got mine.
- Local time
- Yesterday, 20:40
- Joined
- May 21, 2018
- Messages
- 8,525
You have lots of these procedures.
you could make one procedure
then call it like
Code:
Private Sub txtNumberFilter_Change()
Dim sText As String
Dim strFilter As String
' On Error GoTo ErrHandler
sText = Me!txtNumberFilter.Text
If sText <> "" Then
strFilter = "[OrderNumber] Like '*" & sText & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records match " & Me.Filter, vbInformation, "No Match"
Me.FilterOn = False
DoEvents
Me.txtNumberFilter.SetFocus
sText = Left(Me.txtNumberFilter.Text, Len(txtNumberFilter.Text) - 1)
Me.txtNumberFilter.Value = sText
Me.Filter = "[OrderNumber] Like '*" & sText & "*'"
Me.FilterOn = True
End If
With Me.txtNumberFilter
.SetFocus
.Value = sText
.SetFocus
.SelStart = Len(sText)
.SelLength = 0
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End Subyou could make one procedure
Code:
Public Sub FilterByControl(ctrl as access.control, filterField as string)
Dim sText As String
Dim strFilter As String
' On Error GoTo ErrHandler
sText = ctrl.text
If sText <> "" Then
strFilter = "[" & FilterField & "] Like '*" & sText & "*'"
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
If Me.Recordset.RecordCount = 0 Then
MsgBox "No records match " & Me.Filter, vbInformation, "No Match"
Me.FilterOn = False
DoEvents
ctrl.SetFocus
sText = Left(ctrl.Text, Len(ctrl.Text) - 1)
ctrl.Value = sText
Me.Filter = "[" & FilterField & "] Like '*" & sText & "*'"
Me.FilterOn = True
End If
With ctrl
.SetFocus
.Value = sText
.SetFocus
.SelStart = Len(sText)
.SelLength = 0
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End Subthen call it like
Code:
Private Sub txtNumberFilter_Change()
FilterByControl me.txtNumberFilter, "OrderNumber"
end sub
