Assigning Variable from Combo Box

[Rod via AccessMonster.com]

Okay, I'm kind of a newbee to Access. I'll try to explain what I've done
thus far and
what I'm trying to do.
I've put a combo box (named: Artist1) in a form (named: Artist2) and I've
populated this
combo box from a table (named: ArtistBand, field named: ArtistBand).
Everything works fine with that.
Now, I'm using the "On Not in List" event for new data entries in this
combo box. I,ve
set the "Limit To List" data to Yes and I,ve added to following info to the
"On Not in List"
event:
Private Sub Artist1_NotInList(NewData As String, Response As
Integer)
Dim varArtistBand As String
varArtistBand = NewData
Response = acDataErrAdded

End Sub

Now, when I run the form, I get the following Access message:
"The text you entered isn't an item in the list."
So, I'm missing something(s) somewhere. Any suggestion?

Secondly, I'm trying to assign a variable (as shown in the event code above)

from 21 different combo boxes and, once that is done, added those 21
variables into 7 different tables (tables that are all linked to each
other in
some way). At least that's the plan, unless there is a easier
way/method.

I've spent quite a bit of time searching through past threads/subjects, but
couldn't
find anything that addressed what I'm doing.

Thanks for any assistance you could possible provide.
Rod

 

[Dirk Goldgar]

In

Okay, I'm kind of a newbee to Access. I'll try to explain what I've
done thus far and
what I'm trying to do.
I've put a combo box (named: Artist1) in a form (named: Artist2)
and I've populated this
combo box from a table (named: ArtistBand, field named: ArtistBand).
Everything works fine with that.
Now, I'm using the "On Not in List" event for new data entries in
this combo box. I,ve
set the "Limit To List" data to Yes and I,ve added to following info
to the "On Not in List"
event:
Private Sub Artist1_NotInList(NewData As String, Response
As Integer)
Dim varArtistBand As String
varArtistBand = NewData
Response = acDataErrAdded

End Sub

Now, when I run the form, I get the following Access message:
"The text you entered isn't an item in the list."
So, I'm missing something(s) somewhere. Any suggestion?

It's not enough just to tell Access that the data has been added to the
table ("Response = acDataErrAdded"); you have to actually add the new
artist to the table yourself. Something like this:

'----- start of example code -----
Private Sub Artist1_NotInList(NewData As String, Response As Integer)

CurrentDb.Execute _
"INSERT INTO ArtistBand(ArtistBand) " & _
"VALUES(" & Chr(34) & NewData & Chr(34) & ")", _
dbFailOnError

Response = acDataErrAdded

End Sub
'----- end of example code -----

 

[Rod via AccessMonster.com]

Dirk,
Thanks for your reply.
I understand that I need to add code to get the new typed in info to
be stored to the 7 different tables, but first, I want to assign a variable
from all of my 21 combo boxes entries. Then, at the end, have one button
that will open the corresponding tables and add the new data.
I believe "Limit To List" data needs to be set to "No", so I'm going
that
route for now, at least until I find out that the variables are not being
assigned properly. For now it appears to be working.
Once again, thanks for your assistance.

Okay, I'm kind of a newbee to Access. I'll try to explain what I've
done thus far and

[quoted text clipped - 19 lines]

"The text you entered isn't an item in the list."
So, I'm missing something(s) somewhere. Any suggestion?

It's not enough just to tell Access that the data has been added to the
table ("Response = acDataErrAdded"); you have to actually add the new
artist to the table yourself. Something like this:

'----- start of example code -----
Private Sub Artist1_NotInList(NewData As String, Response As Integer)

CurrentDb.Execute _
"INSERT INTO ArtistBand(ArtistBand) " & _
"VALUES(" & Chr(34) & NewData & Chr(34) & ")", _
dbFailOnError

Response = acDataErrAdded

End Sub
'----- end of example code -----