Adding new data to a cbo

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
Back
Top