Combo Box "Not In List" VBA code to Add item to table

I have a drop down combo box that gives the user to enter an item not in
the list by adding it to the list. The list is a table.
It works fine on Access2003 but fails on Access2002/XP. ON XP, it seems
to work, shows the message that the entry has been added, then I get an
Access error saying the item is not in the list.

Here is my code:

Private Sub Combo10_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Outside Referral " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "")

If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Outside_Referrals " & _
"VALUES ('999','" & NewData & "','');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Addition completed." _
, vbInformation, ""
Response = acDataErrAdded
Else
MsgBox "Please choose an Outside Referral from the list." _
, vbInformation, ""
Response = acDataErrContinue
End If
End Sub


Suggestions??

Bob

re: Combo Box "Not In List" VBA code to Add item to table

Bob:

I cannot determine whether the following KB article is the source of the
issue you are seeing, however, it deals with the same error message so I am
passing it along as a potential reference.

http://support.microsoft.com/default...b;en-us;824176

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Bob Alston" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:n97af.47518$fE5.31180@fed1read06...
I have a drop down combo box that gives the user to enter an item not in
the list by adding it to the list. The list is a table.
It works fine on Access2003 but fails on Access2002/XP. ON XP, it seems
to work, shows the message that the entry has been added, then I get an
Access error saying the item is not in the list.

Here is my code:

Private Sub Combo10_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Outside Referral " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "")

If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Outside_Referrals " & _
"VALUES ('999','" & NewData & "','');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Addition completed." _
, vbInformation, ""
Response = acDataErrAdded
Else
MsgBox "Please choose an Outside Referral from the list." _
, vbInformation, ""
Response = acDataErrContinue
End If
End Sub


Suggestions??

Bob

re: Combo Box "Not In List" VBA code to Add item to table

David Lloyd wrote:[color=blue]
> Bob:
>
> I cannot determine whether the following KB article is the source of the
> issue you are seeing, however, it deals with the same error message so I am
> passing it along as a potential reference.
>
> http://support.microsoft.com/default...b;en-us;824176
>[/color]
Thanks for the article link. Unfortunately, that wasn't it.

I am hopefull that it will turn out to be an options setting. I have
looked but haven't seen anything that looks like it could be the cause.

Thanks again.

Bob

re: Combo Box "Not In List" VBA code to Add item to table

Bob:

It would be helpful to know the structure of your tbl_Outside_Referrals in
terms of fields, data types, etc. Also, knowing the property settings for
the combobox would be beneficial (Format, Column Count, Column Widths, Row
Source Type, Row Source, Bound Column, and Limit to List).

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Bob Alston" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:0wmaf.54211$fE5.23739@fed1read06...
David Lloyd wrote:[color=blue]
> Bob:
>
> I cannot determine whether the following KB article is the source of the
> issue you are seeing, however, it deals with the same error message so I
> am
> passing it along as a potential reference.
>
> http://support.microsoft.com/default...b;en-us;824176
>[/color]
Thanks for the article link. Unfortunately, that wasn't it.

I am hopefull that it will turn out to be an options setting. I have
looked but haven't seen anything that looks like it could be the cause.

Thanks again.

Bob

re: Combo Box "Not In List" VBA code to Add item to table

David Lloyd wrote:[color=blue]
> Bob:
>
> It would be helpful to know the structure of your tbl_Outside_Referrals in
> terms of fields, data types, etc. Also, knowing the property settings for
> the combobox would be beneficial (Format, Column Count, Column Widths, Row
> Source Type, Row Source, Bound Column, and Limit to List).
>[/color]
tbl_Outside_Referrals

seq Autonumber
OutsideReferral Text - 50 characters
Email Referral to Text - 50 characters



combo Box:

Combo10


Format Data Event Other All
Name . . . . . . . . . . . . .combo

Control Source . . . . . . . .OutsideReferral
Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Decimal Places . . . . . . . .Auto
Input Mask . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . .

Row Source Type . . . . . . . Table/Query
Row Source . . . . . . . . . .SELECT
[tbl_Outside_Referrals].[outsidereferral],
[tbl_Outside_Referrals].[OutsideReferral] FROM [tbl_Outside_Referrals
Column Count . . . . . . . . .2. . . . . . . . . . . . . . . . . . . .
.. . . . .

Column Heads . . . . . . . . .No . . . . . . . . . . . . . . . . . . .
.. . . . .
Column Widths . . . . . . . . 0";3.5209"
Bound Column . . . . . . . . .2. . . . . . . . . . . . . . . . . . . .
.. . . . .
List Rows . . . . . . . . . . 12. . . . . . . . . . . . . . . . . . .
.. . . .

List Width . . . . . . . . . .3.5208"
Status Bar Text . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Limit To List . . . . . . . . Yes
Auto Expand . . . . . . . . . Yes

Default Value . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . .
IME Hold . . . . . . . . . . .No . . . . . . . . . . . . . . . . . . .
.. . .
IME Mode . . . . . . . . . . .No Control

IME Sentence Mode . . . . . . None
Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Validation Text . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Visible . . . . . . . . . . . Yes

Display When . . . . . . . . .Always
Enabled . . . . . . . . . . . Yes
Locked . . . . . . . . . . . .No . . . . . . . . . . . . . . . . . . . . .
Allow AutoCorrect . . . . . . Yes

Tab Stop . . . . . . . . . . .Yes
Tab Index . . . . . . . . . . 2 . . . . . . . . . . . . . . . . . . .
.. . . .
Left . . . . . . . . . . . . .0.0417"

re: Combo Box "Not In List" VBA code to Add item to table

In message <n97af.47518$fE5.31180@fed1read06>, Bob Alston
<tulsaalstonsNOSPAM@cox.net> writes[color=blue]
>I have a drop down combo box that gives the user to enter an item not
>in the list by adding it to the list. The list is a table.
>It works fine on Access2003 but fails on Access2002/XP. ON XP, it
>seems to work, shows the message that the entry has been added, then I
>get an Access error saying the item is not in the list.[/color]

Try refreshing the data on your form. Add me.refresh at the end of your
code.




--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

re: Combo Box "Not In List" VBA code to Add item to table

Bob:

I believe the reason your encountering the error is that you are trying to
insert a value into an autonumber field in your SQL statement. Autonumber
fields generate their own value and cannot have duplicates. Therefore,
after the first insert the value 999 is already used, and any subsequent
insertions will fail and generate the error message you are receiving. I
was able to add to the combobox with the following modification to the SQL
statement.

strSQL = "INSERT INTO tbl_Outside_Referrals (OutsideReferral,
EmailReferralTo) " & _
"VALUES ('" & NewData & "','');"

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Bob Alston" <tulsaalstonsNOSPAM@cox.net> wrote in message
news:ycpaf.56557$fE5.6669@fed1read06...
David Lloyd wrote:[color=blue]
> Bob:
>
> It would be helpful to know the structure of your tbl_Outside_Referrals in
> terms of fields, data types, etc. Also, knowing the property settings for
> the combobox would be beneficial (Format, Column Count, Column Widths, Row
> Source Type, Row Source, Bound Column, and Limit to List).
>[/color]
tbl_Outside_Referrals

seq Autonumber
OutsideReferral Text - 50 characters
Email Referral to Text - 50 characters



combo Box:

Combo10


Format Data Event Other All
Name . . . . . . . . . . . . .combo

Control Source . . . . . . . .OutsideReferral
Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Decimal Places . . . . . . . .Auto
Input Mask . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . .

Row Source Type . . . . . . . Table/Query
Row Source . . . . . . . . . .SELECT
[tbl_Outside_Referrals].[outsidereferral],
[tbl_Outside_Referrals].[OutsideReferral] FROM [tbl_Outside_Referrals
Column Count . . . . . . . . .2. . . . . . . . . . . . . . . . . . . .
.. . . . .

Column Heads . . . . . . . . .No . . . . . . . . . . . . . . . . . . .
.. . . . .
Column Widths . . . . . . . . 0";3.5209"
Bound Column . . . . . . . . .2. . . . . . . . . . . . . . . . . . . .
.. . . . .
List Rows . . . . . . . . . . 12. . . . . . . . . . . . . . . . . . .
.. . . .

List Width . . . . . . . . . .3.5208"
Status Bar Text . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Limit To List . . . . . . . . Yes
Auto Expand . . . . . . . . . Yes

Default Value . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . .
IME Hold . . . . . . . . . . .No . . . . . . . . . . . . . . . . . . .
.. . .
IME Mode . . . . . . . . . . .No Control

IME Sentence Mode . . . . . . None
Validation Rule . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Validation Text . . . . . . . . . . . . . . . . . . . . . . . . . . .
.. . . . . . .
Visible . . . . . . . . . . . Yes

Display When . . . . . . . . .Always
Enabled . . . . . . . . . . . Yes
Locked . . . . . . . . . . . .No . . . . . . . . . . . . . . . . . . . . .
Allow AutoCorrect . . . . . . Yes

Tab Stop . . . . . . . . . . .Yes
Tab Index . . . . . . . . . . 2 . . . . . . . . . . . . . . . . . . .
.. . . .
Left . . . . . . . . . . . . .0.0417"

re: Combo Box "Not In List" VBA code to Add item to table

David Lloyd wrote:[color=blue]
> Bob:
>
> I believe the reason your encountering the error is that you are trying to
> insert a value into an autonumber field in your SQL statement. Autonumber
> fields generate their own value and cannot have duplicates. Therefore,
> after the first insert the value 999 is already used, and any subsequent
> insertions will fail and generate the error message you are receiving. I
> was able to add to the combobox with the following modification to the SQL
> statement.
>
> strSQL = "INSERT INTO tbl_Outside_Referrals (OutsideReferral,
> EmailReferralTo) " & _
> "VALUES ('" & NewData & "','');"
>[/color]
Aha. Now that makes sense.

Thank you!!!!!!!

re: Combo Box "Not In List" VBA code to Add item to table

Bob Alston wrote:[color=blue]
> David Lloyd wrote:
>[color=green]
>> Bob:
>>
>> I believe the reason your encountering the error is that you are
>> trying to insert a value into an autonumber field in your SQL
>> statement. Autonumber fields generate their own value and cannot have
>> duplicates. Therefore, after the first insert the value 999 is
>> already used, and any subsequent insertions will fail and generate the
>> error message you are receiving. I was able to add to the combobox
>> with the following modification to the SQL statement.
>>
>> strSQL = "INSERT INTO tbl_Outside_Referrals (OutsideReferral,
>> EmailReferralTo) " & _
>> "VALUES ('" & NewData & "','');"
>>[/color]
> Aha. Now that makes sense.
>
> Thank you!!!!!!![/color]
I thought that was it. Made sense. The new code works great on my
Access 2003. However, when I took the updated code to my client's
Access XP, I got the same problem. Weird.

At first I wondered if there was some issue on when the refresh occurred
vs. finishing the code??? But the update is not in the table.

Does XP treat the table as being locked differently than 2003?
Would it be better to use a query set as a snapshot?
Other ideas?

Bob

Bob

re: Combo Box "Not In List" VBA code to Add item to table

Bob Alston wrote:[color=blue]
> Bob Alston wrote:
>[color=green]
>> David Lloyd wrote:
>>[color=darkred]
>>> Bob:
>>>
>>> I believe the reason your encountering the error is that you are
>>> trying to insert a value into an autonumber field in your SQL
>>> statement. Autonumber fields generate their own value and cannot
>>> have duplicates. Therefore, after the first insert the value 999 is
>>> already used, and any subsequent insertions will fail and generate
>>> the error message you are receiving. I was able to add to the
>>> combobox with the following modification to the SQL statement.
>>>
>>> strSQL = "INSERT INTO tbl_Outside_Referrals (OutsideReferral,
>>> EmailReferralTo) " & _
>>> "VALUES ('" & NewData & "','');"
>>>[/color]
>> Aha. Now that makes sense.
>>
>> Thank you!!!!!!![/color]
>
> I thought that was it. Made sense. The new code works great on my
> Access 2003. However, when I took the updated code to my client's
> Access XP, I got the same problem. Weird.
>
> At first I wondered if there was some issue on when the refresh occurred
> vs. finishing the code??? But the update is not in the table.
>
> Does XP treat the table as being locked differently than 2003?
> Would it be better to use a query set as a snapshot?
> Other ideas?
>
> Bob
>
> Bob[/color]
It may be a bug in 2002. This post mentions a similar problem except
that in my case the record addition does not happen.

But using a before update event instead was one of the alternatives I
was considering anyway.

http://64.233.167.104/search?q=cache...ert+fail&hl=en

Bob