Adding new data to a cbo

[Guest]

I am using a database to track new tasks in our office and I am using a cbo
to allocate each new task to an office staff member. This is based on
tblAllocation which is linked to tblTask. I wish to be able to enter new
names to the cbo from the form. I have used the following code on other
databases for this same purpose but on this occasion I am stuck. It is
NotInList.


Private Sub Combo55_NotInList(NewData As String, Response As Integer)
Dim db As Database
Set db = CurrentDb

'Ask the user if they want to add to the list
If MsgBox("Do you want to add this entity to the list?", vbYesNo +
vbQuestion, "Add new value?") = vbYes Then

'The user clicked Yes - add the new value
db.Execute "INSERT INTO tblAllocation (Allocated Person) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the new value
Response = acDataErrAdded

Else

'The user clicked No - discard the new value
Me.Combo55.Undo
'Tell Access you've discarded the new value
Response = acDataErrContinue

End If

I keep getting a compile error highlighting second line of code, stating
User-defined type not defined.
any assistance appreciated. Apologies if this is not too clear but am very
new to Access.
thanks
Steven

 

[Ed Robichaud]

Sounds like you don't have a DAO reference set. Open any form/report in
design mode, select view code, then the menu Tools- References. Select
"Microsoft DAO 3.6 Library" or an earlier version depending on your version
of Access. The Database statement should then compile.
-Ed

 

[Guest]

Ed
Many thanks - this solved one problem, but I now have another (related?)
When I try to enter data, the box asks whether I wish to add new entry, as
expected. When click yes, I get 'Run-time Error 3201. Cannot add new or
changea record because a related record is required in tblTask'. Also, I know
the AllocationID should be saved into tblTask, but no data is being copied
into the main table (tblTask)
thanks for any help
steven

 

[Ed Robichaud]

The error msg indicates that the recordset for your form is a table/query on
the many/child side of a relationship and that you're attempting to add a
new child record without first having a related parent.

If I understand your setup, you'd need to enter a [Task] before you allocate
it to any person. If you've made any of those related fields "required",
you may need to change that, rethink the data sequence and/or redesign you
form.
-Ed