Find as you type Combo issue (1 Viewer)

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I volunteered to edit a database to add/edit the wish list from the user since the person who developed the db has moved on. The db was set up differently than I would have done it. May not be the right approach, but have decided to pull out the relevant objects into a new file and go from there. I wanted to use the FAYT as found at https://www.access-programmers.co.uk/forums/showthread.php?t=304997&highlight=fayt
It works well for finding items already there. I tried to add a not in list event and when I did, I wasn't sure how to refresh/requery the combo, as after the entry form was closed, the combo box was empty. I then removed the not inlist code and instead added a button to open the entry form, seeing if it would do any different and same result. In the code on the entry form at the end I put in the following.
Code:
Forms!frm_dt_employees.Requery
Forms!frm_dt_employees.cboNameSearch.Requery

I tried both to see if that would cover it and neither allows the display of the new data until I either close/reopen or go to design view and back to form view. Is there something different I need to do?

Employees form is bound, form to Add New Records is unbound and uses a recordset (AddNew/Update) to append the data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious... Are you setting the Response variable in your NotInList event?
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I tried this that I found on the web and changed the part under the vbYes to open the form rather than run an sql statement.
Code:
Private Sub cboNameSearch_NotInList(NewData As String, Response As Integer)
'http://www.databasedev.co.uk/not_in_list.html
'20190618 ss
    Dim strSQL As String
    Dim i As Integer
    Dim Msg As String

    'Exit this sub if the combo box is cleared
    If NewData = "" Then Exit Sub

    Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"

    i = MsgBox(Msg, vbQuestion + vbYesNo, "New Employee")
    If i = vbYes Then
        DoCmd.OpenForm "frm_dt_EmployeesNew", , , , , acDialog
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If
End Sub

If I use this code, then I also get an error when it hits the code to requery the combobox when closing the pop up form. "error 2118 You must save the current field before you run the Requery action."
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
Just saw Jdraw's link. I'll review that and post back.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:44
Joined
Oct 29, 2018
Messages
21,358
If I use this code, then I also get an error when it hits the code to requery the combobox when closing the pop up form. "error 2118 You must save the current field before you run the Requery action."
Hmm, I don't seen anywhere in the above code to "Requery" the combobox. The Response line should take care of it. So, if you have added your own Requery line, what happens if you remove it?
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I tried it out jdraws suggestion and it didn't work, there must be another event or code I need.

To clarify,
jdraws suggestion works with a regular combobox, unfortunately, it doesn't seem to work when using it along with the FAYT code, or at least for me that is what happened. It opens the entry form, but doesn't bring it back into the combobox. Using it without FAYT, it works great just as in the video.
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
DBguy I tried it there too and got the same 2118 error that the record had to be saved first. Somehow need to clear out the text that was typed in the combo when the not in list gets activated, not sure how best to do that. I tried undo command, but then the combobox was completely blank.
 

jdraw

Super Moderator
Staff member
Local time
Today, 13:44
Joined
Jan 23, 2006
Messages
15,364
Sorry, I focused on the Not in List ---not FAYT.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:44
Joined
Oct 29, 2018
Messages
21,358
DBguy I tried it there too and got the same 2118 error that the record had to be saved first. Somehow need to clear out the text that was typed in the combo when the not in list gets activated, not sure how best to do that. I tried undo command, but then the combobox was completely blank.
Can you post a small demo file, so we can give it a try? Thanks.
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I'll put something together. As I was coming to work this morning, I thought of a workaround. Not pretty. I'll have the edit form close when the user chooses to add a new record not in list and then have the add form open the edit form on the close event. If I go that way, would I use open args to pass back the new entry or go the easy route and have them select from the combobox.
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
Here is a demo file. I'm reworking the default form, so isn't quite polished. I deleted most of stuff that thought didn't need but left a few extra objects.
 

Attachments

  • TrainingDBv2.0Copy.accdb
    1.2 MB · Views: 91

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
Another issue which I ignored before as it seemed intermittent. I am using this code to either add a new record or go to the last record and set the focus to a specific field textbox on the form. After further testing, is consistent. Turns out that if I use the keyboard, up/down arrows, enter key, the code runs smoothly, if I click using the mouse (redundant wording?) I get error

2185 You can't reference a property or method for a control unless the control has the focus

Code:
Private Sub cboNameSearch_AfterUpdate()
'Locate Employee Training Data and
'Set Focus to new record for data entry
'20190610 ss
'Set Focus to btnAddCommon if it is visible
'so that can use enter key rather than having
'to click it because the cursor is in the subform,
'otherwise continue as before and go to the subform
'20190624 ss
    Dim sID As Single
    
    If Not IsNull(Me.cboNameSearch.Column(0)) Then
        sID = Me.cboNameSearch.Column(0)
        Me.Recordset.FindFirst "[ID_Employee] = " & sID
    End If
    
    Me.frmhldr_ListItems.SetFocus
    Forms!frm_dt_employees.frmhldr_ListItems.Form.tb_TrainingType.SetFocus
    If Me.cboTrainingType.Visible = False Then
        DoCmd.GoToRecord , , acNewRec
    Else
        DoCmd.GoToRecord , , acLast
        Me.btnAddCommon.SetFocus
    End If
End Sub

Should a mouse click behave differently than the keyboard. Do I need to set focus differently if they use the mouse?
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I think I figured it out...so far this seems to work:
Code:
Private Sub cboNameSearch_Click()
'Keep the focus on cboNameSearch in order
'to avoid the 2185 property has focus error
'20190702
    Me.cboNameSearch.SetFocus
End Sub
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
Problem has continued. I have stepped through the code and it doesn't seem to have errors until after it has left the sub. There are no events being called once the AfterUpdate sub has completed. Until now, I thought it was random, however, after further testing, it seems that if the user selects the person from the combobox by clicking the mouse, the error happens. If the selection is made via the keyboard (typing letters, using the up/down arrows, pressing the enter key) it works without error 2185 showing. I have subsequently removed the click event code of my previous post since it does not seem to have helped.
 

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
Hmm, so I find you need to undo the entry in Combo Box before you open the Form for the new entry, i.e.

Code:
        [B]DoCmd.RunCommand acCmdUndo[/B]
        DoCmd.OpenForm "frm_dt_EmployeesNew", , , , , acDialog
        Response = acDataErrAdded

If you want to carry what the User has entered then you could use Open Args but you will probably still need to clear the Combo Box first.
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
Hi Gina, I tried your suggestion and assumed it goes in the code for not in list event? If so, it still produces the error and doesn't show the newly added record when the add new employee form is closed.
 

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
Not sure I understand, did you just add the one line or replace something?
 

sxschech

Registered User.
Local time
Today, 10:44
Joined
Mar 2, 2010
Messages
791
I tried both ways adding the undo to existing code and literally copy paste your code to replace what I had.
 

GinaWhipp

AWF VIP
Local time
Today, 13:44
Joined
Jun 21, 2011
Messages
5,901
You only needed to add the one line. Okay, so you say you did that, when you did, did you notice if the control blanked out?
 

Users who are viewing this thread

Top Bottom