NotInList Event Handler

[Scott A Kirby]

Access97, Win95:
My application is being used by clerical staff as well as the knowledge
masters, so I have a combo box displaying the 'code' concatenated with
the code's description. Problem is that they also want to be able to
enter a new item on the fly.

So, I followed the NotInList event handler help, queried the user if
they really wanted to add a new item, then built a new special code with
the new description as entered and added both to the underlying table.
Problems come next. If I return acErrDataAdded (I think that's the
constant), the combo query is updated, but because what they entered is
not exactly what I put in the combo query, they still get a not in list
error. I noticed that the entered data is not passed into the routine
as a ByVal, so I tried updating that variable and/or the combo box value
itself, all to no avail.

Following the help again, I returned acErrDataContinue after changing
their entry - changes the entry, but won't update the combo query.
Tried doing so myself - can't, the field hasn't been saved.

I played with this thing for a couple of hours last night, and feel
caught in a Catch-22. Has anyone tried something like this and made it
work?

TIA,

Scott

[George J. Shears]

Try the following. I designed it specifically to handle "NEW" values added
to the ComboBox (values differing from what the user originally typed).

Private Sub cboYours_NotInList(NewData As String, Response As Integer)

'--- declare STATIC busy flag
Static im_busy as Integer
Dim YourNewValue

'-- traps second spurious NotInList Event and FORCE requery
If im_busy = 1 Then
im_busy = 0
Response = acDataErrAdded
Exit sub
End If

' === VERIFY INTENT TO ADD NEW VALUE (you have)
' === GENERATE YOUR NEW VALUE (you have)

YourNewValue = whatever(........)
' === ADD YOUR NEW VALUE TO TABLE (you have)

' THE FOLLOWING MAKES IT ALL WORK
' ---- setting the text property may trigger a second NotInList event.
' ---- This second event is handled by the initial code in this
' ---- function which tests the im_busy flag and acts accordingly.

im_busy= 1
cboYours.SetFocus
cboYours.Text = YourNewValue
im_busy = 0

'--- it is OK to return CONTINUE here
Response = acDataErrContinue
End Sub

George.

Scott A Kirby wrote in message <35C5BDC7...@entergy.com>...

[George J. Shears]

Access is totally anal about the value added to the recordset matching the
original NewData value. Change your message box to an InputBox which lets
you change the NewData value and _then_ add the Altered NewData value to the
recordset. You will see the problem. :{

George.

[Dev Ashish]

Sample code would've been much easier. But, are you trying to do it this
way?

'************ Code Start **********
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available AE Name"
strMsg = strMsg & "@Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & "@Click Yes to link or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub
'*********** Code End **************

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

: