473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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
Nov 13 '05 #1
9 19412
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Rolf Brauser | last post by:
Hi, I want to have a table with a border of 1px arround it but table="1" is more than a pixel because this 3d effect is attached. Values below are not accepted How can I get this border with...
2
by: mfilion | last post by:
Hey all, I have a problem with my the Form I built, hopefully someone here can help me out! I'm still a beginner, so bear with me! Here goes... The form contains many boxes, each of which...
5
by: krwill | last post by:
I'm trying to automate a combo box to add a record to the source table if it's "Not In List". I've tried many different examples and none have worked. Combo Box Name = Combo24 Source Table...
5
by: sensreview | last post by:
Hello, I need help in selecting a value from combo list thru one lookup table and update different table on MS access form. For eg; I have a lookup table of usernames, I need to use this...
0
by: parag100 | last post by:
hello how to populist list item thriogh sql query in forms 6i. see there is a form .on that one list item,stud_list . i have sat it combo box. but how to populate it by query.
4
by: belfallas | last post by:
Hi, ive got this problem with one of my databeses. My question is, how do i create a sort of a shopping list on access? Im doing a ficitional pizza shop, and what i want to do is to have an...
26
by: pouj | last post by:
Hey i need help with setting up this on ACCESS 2007 i need step by step i need a form... i select a name (drop list)...then gives me more choice (in next drop list) ....then when i select that...
4
by: mistyblu | last post by:
I wrote an Access97 application using VBA for a company many moons ago and they have approximately 20 users. All has been hunky dory for a long time but suddenly they have a combo box error on 3 of...
6
by: vanlanjl | last post by:
I cannot get the not in list event to work right. On my form I have a combo box named "cboChargeCode" I have a table named "tblChargeCode" I would like it so when you enter a value into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.