Not in list

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

Realistically, there isn't a "perfect example", since how you get the
information to add to the table depends on the situation.

http://www.mvps.org/access/forms/frm0015.htm at "The Access Web" shows how
to use the event when all your underlying table contains is a text field and
an AutoNumber field.

For more complex examples, you probably need to replace the code in the Else
side of the If statement to open an update form modally so that execution of
the NotInList event halts until your update is finished. If you want to give
the user the option of not saving anything in that form, you'll need to set
up communication between the two forms so that the NotInList event knows to
set Response to acDataErrContinue rather than acDataErrAdded.
 
Bob

While not the nessarily perfect example, here is one I have used in an
application

'===============================================================================
Private Sub LocationID_NotInList(NewData As String, Response As Integer)
'-------------------------------------------------------------------------------
' Comments :
' Author : Jeff Date : 18/03/2007
' Parameters :
' Returns :
' Usuage :
'
Dim ldbs As Database, lrst As Recordset, llngLocationID As Long

On Error GoTo PROC_Error

If MsgBox(NewData & " is not in list. Do you wish to add it?", _
vbQuestion + vbOKCancel, "New Venue Location?") = vbOK Then
Set ldbs = CurrentDb
Set lrst = ldbs.OpenRecordset("tblMain_CourseLocations",
dbOpenDynaset)
lrst.AddNew
lrst!Location = NewData
lrst!EnteredBy = GetUserName
lrst!EnteredDate = Date
lrst.Update
lrst.Bookmark = lrst.LastModified
llngLocationID = lrst!LocationID
lrst.Close
Call fnAuditLogUpdate(Me.Name, Me.SchedCourseID, "Location " &
NewData & " Added from Scheduled Course Maintenance")
Set lrst = Nothing
Set ldbs = Nothing

DoCmd.OpenForm "frmMaintain_CourseLocations_Edit", , , , , acDialog,
_
OpenMode.Edit & "," & llngLocationID
Response = acDataErrAdded
Me.LocationID.Requery
Else
Response = acDataErrContinue
Me.LocationID.Undo
End If

PROC_Exit:
Exit Sub

PROC_Error:
Resume PROC_Exit

End Sub


hope it helps...
cheers
jeff
 
Back
Top