G
Gordon
I have a combo box with values chosen from a table field
(fldDepartmentsAffected). If the value selected does not already
exist, I want to allow the entry (even though limit to list set to
yes) but before doing that I want to "alert" the user so that he can
think twice before adding a new entry. I am using the following code:
Private Sub cboActionBy_NotInList(NewData As String, Response As
Integer)
' 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
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String, strSQL As String
strMsg = "'" & NewData & "' is not an available Department or
person " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new name to the current
list?"
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
strSQL = "Select * from tblMaster100"
Set rs = db.OpenRecordset(strSQL)
'Set rs = db.OpenRecordset("tblMaster100", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldDepartmentsAffected = 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
Two problems with this - First I had to rem out the rs.close line
because it always gave me an error "Object variable not set" on that
line of code.
Second, with the line rem'd out, the code then works Ok but it adds a
new blank record.
Help. Confused.com
Gordon
(fldDepartmentsAffected). If the value selected does not already
exist, I want to allow the entry (even though limit to list set to
yes) but before doing that I want to "alert" the user so that he can
think twice before adding a new entry. I am using the following code:
Private Sub cboActionBy_NotInList(NewData As String, Response As
Integer)
' 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
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String, strSQL As String
strMsg = "'" & NewData & "' is not an available Department or
person " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new name to the current
list?"
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
strSQL = "Select * from tblMaster100"
Set rs = db.OpenRecordset(strSQL)
'Set rs = db.OpenRecordset("tblMaster100", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!fldDepartmentsAffected = 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
Two problems with this - First I had to rem out the rs.close line
because it always gave me an error "Object variable not set" on that
line of code.
Second, with the line rem'd out, the code then works Ok but it adds a
new blank record.
Help. Confused.com
Gordon