I'm not sure what tblMaster100 is, but if it has more fields than
fldDepartmentsAffected, it is probably not the table you want to append a new
record to.
What is the RowSource for cboActionBy? If this were my application, I would
probably have a table (tblDepartments) which would be the rowsource for this
combo box, and that is the table I would be using in creating strSQL.
Once you have added the new record to that table, you will also need to
requery cboActionBy.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
"Gordon" wrote:
[Quoted Text] > 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
>
|