NotInList Event

R

Renee

Is there anyway to get have an event procedure for this event so that I can
add a new record to a table without having to close the form I am entering
into?
 
J

John Vinson

Is there anyway to get have an event procedure for this event so that I can
add a new record to a table without having to close the form I am entering
into?
Click to expand...

There's nothing whatsoever about NotInList which requires you to close
the form!

What's entailed in adding a new record? Do you need to open another
Form to do so? If so, simply open it in Dialog mode and it will "pop"
in front of the main form; allow you to add the new record; and the
code will resume after you close the dialog form.

John W. Vinson[MVP]
 
R

ruralguy via AccessMonster.com

Don't forget to set Response = acDataErrAdded so the ComboBox will requery.
 
R

Renee

So I put that event into my form but once I say yes to the question 'Add new
name?' it gives me a msg box that says 'An error occurre. Please try again."
I know that this msg box is set up in the event so I cannot figure out where
the error is. Any ideas on how I can figure out where the error is?
This is how the event looks:
Private Sub VENDOR_ID_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "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("PARTS VENDOR T", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!VENDOR_ID = NewData
rs.Update

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

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 
B

Bouba

Renee said:
So I put that event into my form but once I say yes to the question 'Add new
name?' it gives me a msg box that says 'An error occurre. Please try again."
I know that this msg box is set up in the event so I cannot figure out where
the error is. Any ideas on how I can figure out where the error is?
Click to expand...

Hi,

First thing add a description of the error in your code like this:

If Err Then
MsgBox "An error occurred. Please try again."
MsgBox Err.Description
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

I had a similar problem, so if your error is like:
"You cannot add or change a record because a related record is required in
table 'TblSuppliers'."

TblSuppliers

SupplierID
SupplierName

TblProducts

ProductID
SupplierID

Where (to simplify) I have 2 tables with a one-to-many-relationship between
my table TblSuppliers and my table TblProducts, with checkbox for "Enforce
Referential integrity" set to True. So when I add a new product thanks to the
combo box the recordset need to know to which SupplierID link the new
ProductName like this (entire code):

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

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Product Name " & vbCrLf
& vbCrLf
strMsg = strMsg & "Do you want to associate the new Name?"
strMsg = strMsg & vbCrLf & vbCrLf & "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("TblProducts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![SupplierID] = SupplierIDDropdownList.Value
rs![ProductName] = NewData
rs.Update

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

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
 
Top