my database objects :
==============================
table = color
field
ID = autonumber, Primary Key
field
color = Short Text 255
Required = Yes
Allow Zero Length = No
Indexed = No
==============================
table = clothing
field
ID = autonumber, Primary Key
field
type = Short Text 255
Required = Yes
Allow Zero Length = No
Indexed = No
==============================
table = main
field
ID = autonmber, Primary Key
field
ColorID = Number, Long Integer
field
ClothingID = Number, Long Integer
==============================
INSERT INTO clothing ( type )
SELECT "pants" AS type
INSERT INTO clothing ( type )
SELECT "shoes" AS type
INSERT INTO clothing ( type )
SELECT "shirt" AS type
INSERT INTO clothing ( type )
SELECT "dress" AS type
==============================
INSERT INTO color ( color )
SELECT "red" AS color
INSERT INTO color ( color )
SELECT "green" AS color
INSERT INTO color ( color )
SELECT "blue" AS color
==============================
form = main
Row Source = SELECT ID, ColorID, ClothingID FROM main;
contains 2 combo boxes
1) cmbColor
Control Source = ColorID
Row Source = SELECT ID, color FROM color;
Bound Column = 1
Column Count = 2
Column Widths = 0";1"
2) cmbClothing
Control Source = ClothingID
Row Source = SELECT ID, type FROM clothing;
Bound Column = 1
Column Count = 2
Column Widths = 0";1"
============================================================================
my code for the NotInList event for the two comboboxes
notice I trim the NewData in the sql
VBA Code:
Option Compare Database
Option Explicit
'**************************************************************************
Private Sub cmbClothing_NotInList(NewData As String, Response As Integer)
' from http://www.databasedev.co.uk/not_in_list.html
Dim strSQL As String
Dim i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
strSQL = "Insert Into clothing ([type]) " & _
"values ('" & Trim(NewData) & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
'**************************************************************************
Private Sub cmbColor_NotInList(NewData As String, Response As Integer)
' from http://www.databasedev.co.uk/not_in_list.html
Dim strSQL As String
Dim i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
strSQL = "Insert Into color ([color]) " & _
"values ('" & Trim(NewData) & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
'**************************************************************************
the the color table contains red, green, blue
the user enters ____________green in the color combobox
and the NotInList event triggers
I click Yes and "green"
and IMMEDIATELY this pops up
I click OK and look at the color table and see red, green, blue. green
green is in there twice
and it is the trimmed green
so the insert worked
but the combobox is still showing ___________green and I get that nasty 2nd popup message
if I set the index of the color field to "No Duplicates" then I still get the 2nd pop up, but the message changes to "Run Time error 3022: the changes were not successful, duplicate values blah blah blah", and the combobox is still showing ___________green
(obviously green was not inserted because of the no duplicates error)
if I set the index of the color field to "No Duplicates" and add an error handler in the NotInList event to catch err.number 3022 then I'm back to getting the pop up in the 2nd image "The text you entered isn't an item in the list" and the combobox still shows __________green
ideally what I would like is for the color field to be indexed, no duplicates
and if the user enters _________green the combobox just knows its green and the NotInList never fires
or if the NotInList event fires I attempt to insert a trimmed(______green) and catch the 3022 error and ignore it and do not get any 2nd pop up message
ok, I think that sums it up
is there a place where I can upload an access database that you would feel safe to download and see what's going on ?
