Access 2000 Onnotinlist exact 'add entry to source table' setup

[Guest]

Created a combo box that looksup a table. Works fine.
Can add entries by selecting 'No' in Limittolist. Works fine.
I seem incapable of making Access add the new entry into the source table!
Please explain the exact code/ [event procedure] that is required, along
with the exact setup in both tables' field properties (just to make sure I
get it absolutely right).
Thank you!

 

[Wolfgang Kais]

SPBmrmusic.

Created a combo box that looksup a table. Works fine. Can add
entries by selecting 'No' in Limittolist. Works fine. I seem incapable
of making Access add the new entry into the source table!

Using ado or dao?

Please explain the exact code/ [event procedure] that is required,
along with the exact setup in both tables' field properties
(just to make sure I get it absolutely right).

The tables were set up by you, weren't they?
You should explain exactly, how the combobox has been set up.

 

[Guest]

Having read some other posts its obvious I'm way out of my depth here, but
I'd still like to have a go.
My main table is called 'Recordings' and includes a field labeled 'Composer'.
A second table called 'Composers' contains just two fields, a unique ID and
a text field called 'Composer Name'. There is no relationship between the
tables.
I created a new form and followed the Access help in creating a combo box
(used the toolbar icon, created the field in the form, and answered the
wizard questions)
The combo box was setup with the following wizard properties:
1. I want the combo box to look up the values in a table or query.
2. Selected Table: Composers
3. Selected Field: Composer Name
4. Selected column width: Best Fit
5. Store that value in this field: Composer
6. Combo box label: Combo21
7. Changed Combo21 Property box 'Limittolist' to YES
8. Changed Combo21 Property box 'Onnotinlist' to [Event procedure]. Access
Help says Access will automatically add the entry into the source table, but
it doesn't. So I opened the [Event procedure] VB window.
Now what?
Almost every page in the Access Help files says that code must be entered,
but I'll be darned if I can find what code to enter! I hope you have enough
patience to help, Wolfgang! Thank you

SPBmrmusic.

Created a combo box that looksup a table. Works fine. Can add
entries by selecting 'No' in Limittolist. Works fine. I seem incapable
of making Access add the new entry into the source table!

Using ado or dao?

Please explain the exact code/ [event procedure] that is required,
along with the exact setup in both tables' field properties
(just to make sure I get it absolutely right).

The tables were set up by you, weren't they?
You should explain exactly, how the combobox has been set up.

 

[Guest]

PS. I tried this code from the website that's often cited in these posts:

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

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblComposers (ComposerName) VALUES (""" & NewData &
""")", dbFailOnError

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

db.Close
Set db = Nothing

End Sub

But get an error: "db As Database... User-defined type not defined"
What does that mean?
I even tried replacing the word 'Database' with the name of the file
'Recordings' and get the same error.

SPBmrmusic.

Created a combo box that looksup a table. Works fine. Can add
entries by selecting 'No' in Limittolist. Works fine. I seem incapable
of making Access add the new entry into the source table!

Using ado or dao?

Please explain the exact code/ [event procedure] that is required,
along with the exact setup in both tables' field properties
(just to make sure I get it absolutely right).

The tables were set up by you, weren't they?
You should explain exactly, how the combobox has been set up.

 

[Wolfgang Kais]

SPBmrmusic.

PS. I tried this code from the website that's often cited in these posts:

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

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblComposers (ComposerName) VALUES (""" & _ NewData & """)", dbFailOnError

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

db.Close
Set db = Nothing

End Sub

But get an error: "db As Database... User-defined type not defined"
What does that mean?
[...]

"Database" is unknown, because a reference to DAO is missing.
When you have the opened the module, click Tools, References, and
check the "Microsoft DAO x.y Object Library".
That will solve your problem.

 

[Guest]

MARVELLOUS!
Thank you, Wolfgang.

SPBmrmusic.

PS. I tried this code from the website that's often cited in these posts:

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

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblComposers (ComposerName) VALUES (""" & _ NewData & """)", dbFailOnError

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

db.Close
Set db = Nothing

End Sub

But get an error: "db As Database... User-defined type not defined"
What does that mean?
[...]

"Database" is unknown, because a reference to DAO is missing.
When you have the opened the module, click Tools, References, and
check the "Microsoft DAO x.y Object Library".
That will solve your problem.

 

[Guest]

I spoke too soon.
In setting up an additional combo box using the same properties and VB
language, I now get an error 3192: Could not find output table 'tblComposers'
Any ideas?

SPBmrmusic.

PS. I tried this code from the website that's often cited in these posts:

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

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblComposers (ComposerName) VALUES (""" & _ NewData & """)", dbFailOnError

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

db.Close
Set db = Nothing

End Sub

But get an error: "db As Database... User-defined type not defined"
What does that mean?
[...]

"Database" is unknown, because a reference to DAO is missing.
When you have the opened the module, click Tools, References, and
check the "Microsoft DAO x.y Object Library".
That will solve your problem.

 

[Wolfgang Kais]

Hallo SPBmrmusic.

I spoke too soon.
In setting up an additional combo box using the same properties
and VB language, I now get an error 3192: Could not find output
table tbllComposers'. Any ideas?

Wasn't it tblComposers (delete the second L)?
Verify that tbllComposers exists. Pretend to rename the table,
copy the name to the clipboard (Ctrl+C) but then press ESC.
Paste the name from the clipboard into your code.

 

[Guest]

I was tired (2am EST) - the second L was a typo.
Followed your instructions to no avail. The same 'could not find output
table' error message appears.
It is happening with all my combo boxes. I checked to make sure the DAO
library is selected (it is), the LimittoList properties are set to Yes (they
are), and carefully checked the spellings of table and field names.

I even deleted one combo box and created a new one to replace it, but the
same error appears.