Not In List

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a field [Profression] on a form which is filled from a combo
box " cboProfression "

The table is [Professions]

I am attempting to add a new profession to the table using the " Not
In list " function:

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

Dim sMsg As String, iValid As Integer

sMsg = "'" & NewData & "' is not an existing profession in the
database" & vbCrLf _
& vbCrLf & "If you wish to add it, click OK" _
& vbCrLf & "Otherwise click Cancel"

iValid = MsgBox(sMsg, vbOKCancel, "Unknown Profession")
If iValid = vbCancel Then 'Cancel was clicked'
Response = acDataErrContinue
Else
CurrentDb.Execute "Insert into tblProfessions (Profession)" _
& "values ( '" & NewData & "')"
Response = acDataErrAdded
End If

End Sub

The new data is not added to my table and i do not get an error
message.

What have I missed?

Robin Chapple
 
It's very possible that you are doing nothing wrong, but i
can suggest two things.
1. I'm not familiar with the Insert Into syntax, if that
continues to not work for you, you can try the recordset
method:
Dim DB as DAO.Database
Dim RS as DAO.Recordset

Set DB = CurrentDB()
Set RS = DB.OpenRecordset("ProfessionsTbl") 'your table or
query name
RS.AddNew
RS![ProffessionName] = NewData
RS.Update
RS.Close
DB.Close
Response = acDataErrorAdded

2. if nothing else helps, you can repeat the rowSource at
the end of the event, like so:
Me![cmbProffession].RowSource = "TblProffession"

thsi forces a requery on the row source.

3. it is unadvisdable to use the same name for a table, a
fild in the table, a control and a form... this can only
cause trouble. consider using type-specific starts and
endings to the names (ProffessionsTbl, ProffessionsFrm,
ProffessionCmb etc.).and while on the subject, names that
are used by the system, like "Name", are also an
exteremely bad idia.
HTH,
Ayelet
 
What you have looks ok, but I think a space is need:

CurrentDb.Execute "Insert into tblProfessions (Profession)" _
& " values ( '" & NewData & "')"

^^- you need a space
 
Back
Top