By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,214 Members | 1,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,214 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <tu****************@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
Nov 13 '05 #2

P: n/a
David Lloyd wrote:
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

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
Nov 13 '05 #3

P: n/a
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" <tu****************@cox.net> wrote in message
news:0wmaf.54211$fE5.23739@fed1read06...
David Lloyd wrote:
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

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
Nov 13 '05 #4

P: n/a
David Lloyd wrote:
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).

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"

Nov 13 '05 #5

P: n/a
In message <n97af.47518$fE5.31180@fed1read06>, Bob Alston
<tu****************@cox.net> writes
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.


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


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

Nov 13 '05 #6

P: n/a
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" <tu****************@cox.net> wrote in message
news:ycpaf.56557$fE5.6669@fed1read06...
David Lloyd wrote:
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).

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"
Nov 13 '05 #7

P: n/a
David Lloyd wrote:
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 & "','');"

Aha. Now that makes sense.

Thank you!!!!!!!
Nov 13 '05 #8

P: n/a
Bob Alston wrote:
David Lloyd wrote:
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 & "','');"

Aha. Now that makes sense.

Thank you!!!!!!!

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
Nov 13 '05 #9

P: n/a
Bob Alston wrote:
Bob Alston wrote:
David Lloyd wrote:
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 & "','');"

Aha. Now that makes sense.

Thank you!!!!!!!


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

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
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.