NotInList help please 1

[colbertsm]

I have a form for entering employee contributions (frmContributions) that contains an unbound combo box to search for employee names (from tblContributors). If the user enters a name that is not already part of the list, I want to give the option of adding a new employee directly on this form. Current code:

Private Sub cmbFindEmployee_NotInList(NewData As String, Response As Integer)
'Ask the user if they want to add a new Employee

Dim strMessage As String

strMessage = "Do you want to add a new Employee?"

If Confirm(strMessage) Then
'Act like the New Employee button was clicked; open a blank record for user to enter new employee
DoCmd.GoToRecord acDataForm, "frmContributions", acNewRec
Else
Response = acDataErrDisplay 'Display the error
End If

End Sub

The Confirm function code is:
Public Function Confirm(strMessage As String) As Boolean
'Ask the user to confirm an action, returning True or False

Dim bytChoice As Byte

bytChoice = MsgBox(strMessage, vbQuestion + vbOKCancel, conAppName)

If bytChoice = vbOK Then
Confirm = True
Else
Confirm = False
End If

End Function

The code brings up the Confirm msgbox with the appropriate question, but gets into a loop when the user clicks OK then gets Run-Time error 2105 when user clicks Cancel with the message "You can't go to the specified record"

Any assistance or a point in the right direction is appreciated!

 

[kjv1611]

Any reason you're trying to go to a new record just when adding a new record to your combo box? I think that's the manin culprit. Try commenting out the DoCmd.GoToNewRecord line and see how it runs.. then report back.

Otherwise, I might would use the Yes/No setup, and say If Msgbox(MyMesssageBoxOptions) = vbYes Then ..do stuff

So give it a try, post back, and let us know your progress.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

I normally do not use the notinlist event, because of this reason. It gets very cumbersome to control the order of events. I would just search for the record. If not found then provide code for what to do. A lot easier to control the events.

dim  rs as dao.recordset
dim emp as long
set rs = me.recordset

'get value from combo. I will assume it is an ID. Modify as needed
emp = cmbFindEmployee
'go to that employee if they are present
rs.findfirst "employeeID = " & emp
'or if text rs.findfirst "employeeID = '" & emp & "'"

'See if found
if rs.notMatch then
  'code here to add new record
  ' you have the new value saved in emp
else
  'additional code here if needed when record found
end if

 

[colbertsm]

Thank you for both suggestions, but they're not quite getting me where I'd like to be.

The cmbFindEmployee combo box is sorted by last name, first name - so the user can tell pretty quickly after beginning to type the last name if the employee is in the list or not. The form is based on a table who's primary key is an auto-generated ContributorID number since the last names are not necessarily unique. The new employee data (name, department, supervisor) can be added directly in the form the combo box is located on, so I'd just like to find a way to automatically go to a new record on this form if the employee the user is looking for is not in the list. Right now, after receiving the error message that the item is not in the list, the user has to backspace over what they've typed in the combo box (or hit ESC twice), then click a "New Employee" button I've placed on the form. Is there anything more streamlined?

Thanks in advance

 

[kjv1611]

Maybe something like this? Not sure, though, b/c I do not recall whether this event will hang if you try to move to another control... but worth a try..

Private Sub cmbFindEmployee_NotInList(NewData As String, Response As Integer)
  [green]'If this runs, we KNOW we want to add a new record[/green]
  cmbFindEmployee.Undo
  Call cmdNewEmployee
End Sub

By the way, if you're keeping a certain naming scheme, might I suggest you use cbo for combo box, and cmd for command button. I used to use cmb and cmd before, but surely you can see how difficult it can be to differentiate when looking at much code.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[colbertsm]

I KNEW there had to be a simple solution - here is my final code:

Private Sub cmbFindEmployee_NotInList(NewData As String, Response As Integer)
'If this runs, we KNOW we want to add a new record

Dim strMessage As String

strMessage = "That employee is not in the list." & vbCrLf & "Would you like to add?"

If Confirm(strMessage) Then
cmbFindEmployee.Undo
Call cmdNewEmployee_Click
Response = acDataErrContinue
End If

End Sub

Thanks to all!