In a database of Students I get the operators to check for duplicate
students before entering them as new.
To smooth this process I have taken advantage of the NotInList event
to take their input, break it into Surname and GivenName and store
in variables for a later event that sets these up as default values
for the new form.
I hate using sendkeys and it is proving unreliable.
The order of events I want is:
1. Operator enters full name of possible new candidate in cboSearch
2. If the new candidate is not already in the table the notinlist
event is triggered
3. The NotInList event asks the user if they would like to add the
new candidate and if yes it takes the new name, splits into Surname
and GivenName and stores in defSurname, defGivenName in readiness to
be used as default values for the new form
4. I set the Response argument so that the normal error message is
suppressed
5. I then want to exit the combo box smoothly and add a record.
The difficulty I had was getting a value in the combo box that would
allow me to leave the combo box and move onto adding a record. As you
can see I have chosen to undo the operator's input of the new name
and send(keys) the last value that was on the combo box which does
work most of the time.
Thanks for your help,
Neil
Private Sub cboSearch_NotInList(NewData As String, Response As
Integer)
Dim bytButtonPressed As Byte
Dim strName As String, posBlank As Byte, strSurname As String,
strGivenName As String
defSurname = ""
defGivenName = ""
strName = Trim(NewData)
posBlank = InStr(strName, " ")
If posBlank >= 2 And Len(strName) > 4 Then
strSurname = Trim(Left(strName, posBlank))
strGivenName = Trim(Mid(strName, posBlank))
strSurname = UCase(Left(strSurname, 1)) & LCase(Mid(strSurname,
2))
strGivenName = UCase(Left(strGivenName, 1)) & LCase(Mid
(strGivenName, 2))
bytButtonPressed = MsgBox("Add " & strSurname & " " &
strGivenName & "?", vbQuestion + vbYesNo, "This student record does
not exist")
If bytButtonPressed = vbYes Then
defSurname = Chr(34) & strSurname & Chr(34)
defGivenName = Chr(34) & strGivenName & Chr(34)
ActiveControl.Undo
SendKeys ActiveControl.SelText
SendKeys "{TAB}"
End If
End If
Response = acDataErrContinue
End Sub
