"Not in list" event code

M

Mercadogs

Hello everyone:

I'm having problems with the below code for adding combo box input if input
is not'
in the list.

What are the variables here that I must change the names of. I've tried a
couple things but was not successful


********Code Start******************


Option Compare Database
Private Sub Combo54_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 Document Number 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("Document Number", 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

Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************
'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'


Private Sub Document_Number_ID_BeforeUpdate(Cancel As Integer)

End Sub
 
A

Allan Murphy

Here is some sample code that I used to add a new City Code to the combo
box.

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

Dim strmsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strmsg = "'" & UCase(NewData) & "' is not in the list. "
strmsg = strmsg & "Would you like to add this City ? "

If vbNo = MsgBox(strmsg, vbYesNo + vbQuestion, " New City Code") Then
resposne = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_cities")
rst.AddNew
rst("city_code") = UCase(NewData)
rst.Update
Response = acDataErrAdded
End If

End Sub

HTH

Allan
 
M

Mercadogs

Thanks a lot Allan! Your code works perfectly!

Allan Murphy said:
Here is some sample code that I used to add a new City Code to the combo
box.

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

Dim strmsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strmsg = "'" & UCase(NewData) & "' is not in the list. "
strmsg = strmsg & "Would you like to add this City ? "

If vbNo = MsgBox(strmsg, vbYesNo + vbQuestion, " New City Code") Then
resposne = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_cities")
rst.AddNew
rst("city_code") = UCase(NewData)
rst.Update
Response = acDataErrAdded
End If

End Sub

HTH

Allan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top