Not in List Event Error

[Edward]

Hi, All...

I have an Access 2000 database that has a bound form with bound combo boxes.
I want the user to have the ability to update a city after a prompt asking
to do so.

The code below was copied from another form that is unbound, and the
combobox is unbound. It updates just fine and moves to the next field on
the unbound form. However, when I use the same code on the bound form (with
the bound combobox), it DOES update the field, but it changes to the next
record.

Is there a way to force it to stay on the current record, or at the very
least move BACK to the same record from which it originated (I'd prefer the
first one)? I've never tried RecordSetClone or Bookmark properties before,
and what I've tried has failed--even from what was I copied directly.
Probably a problem with me. I would just like this to update the combobox
and move to the next field. Ultimately there will be at least three of
these comboboxes on the form, and I don't want to have too much overhead on
the programming side to do this.

I would appreciate any help you might offer on this.

Many thanks in advance

Edward


********CODE START********

Private Sub City_ID_NotInList(strNewData As String, intResponse As Integer)

Dim db As Database
Dim intStyle As Integer
Set db = CurrentDb

intStyle = vbYesNo + vbDefaultButton2 + vbQuestion

DoCmd.Beep
intResponse = MsgBox("The city " & Chr(34) & strNewData & Chr(34) & "
is not in the list." & Chr(13) & "Do you wish to add it?", intStyle, "Add
City?")

If intResponse = vbNo Then
Me.Undo
Me.City_ID = Null
Close
Else
db.Execute "Insert into tbl_Cities(City) Values (" & Chr(34) &
strNewData & Chr(34) & ")"
intResponse = acDataErrAdded
End If

Exit_NotInList:
Exit Sub
End Sub

********CODE END********

 

[Edward]

Thanks, I appreciate the input.

However, like I said, this identical code works fine on an unbound form with
unbound fields. The only time that it fails is with the bound form and
fields.

The "close" that you are referring to would only apply to the portion of the
IF statement where the the response was NO. If YES, then it is bypassed.

The field IS updating. I can see the results in the table. The only
problem is that the next record appears immediately after the insert. This
is true whether or not I step through the processes.

Any other suggestions?

 

[Keith Wilby]

This is how you should use the NotInList event:

http://support.microsoft.com/kb/197526

I would suggest your use of the Response argument is problematic.

"If the user chose not to add a customer, set the Response argument to
suppress an error message and undo changes."

Keith.
www.keithwilby.co.uk

 

[Edward]

My bad. You were right. I had had the close within a recordset With/End
With statement. I had commented out the other stuff in testing, but I had
neglected to remove the close. The close was supposed to be for the closing
of the recordset that I was originally using, even though it doesn't show
the period in front of it. I see what you were saying now. Good catch.

The answer to your question is NO, there are no other events firing either
with this control or the form itself that should be moving to the next
record. Mostly, the screen is straight data entry into one table, but City,
State, and Postal Codes are stored in a normalized table, which leads to the
need for the combo box.

I cannot figure it out. Any thoughts? Thanks again for your help.

Edward

 

[Edward]

Keith,

I'm basically doing what the first method shows, except that the db.execute
statement is much faster than looping through a recordset. All this does is
add to the underlying normalized table. I cannot invoke the requery for the
combo box at this step. I get an error saying that the record needs to be
saved.

What gets me is that this code works perfectly FINE as is in an unbound form
with unbound fields. The same table structure, etc exists. The ONLY
difference is the fact that for the form where the errors are occurs, the
form is bound to a table (VENDORS), but the city/state/postal codes are
normalized in respective tables bearing their names. This is what the combo
boxes are tied to, but they are linked through relationships to VENDORS.

Any thoughts? I feel sure that it has something to do with the fact that
the underlying cause is because of the bound form. Unbound works fine.

 

[Edward]

The table is in 4th normal form. The Cities table contains only the City_ID
and the City. They are linked in tbl_Vendors on City_ID and do have RI and
cascade update. That's not the problem. That's the same structure
basically for the form that is unbound, and it works fine.

After doing some more testing, I've managed to narrow down that it IS
because the form is bound to tbl_Vendors. I'm not sure how to get around
this because I need to update most of the vendor information along with the
city/state/zip.

Like I said, it is updating the table on the execute. It also does the same
for acErrDataAdd and for moving through the underlying recordset with
..Update features. That means that it isn't the Execute statement per se.
It is the form itself.

It is updating the city table, but it then moves to a new record on the
form.

 

[Edward]

I guess I do not know what you are getting at here. My relationship is not
many to many or one to one.
It is one to many, and there is only one instance of city id that is being
associated with each vendor. After inserting a new
city, it does refresh correctly.

RI shouldn't be the problem, and cascade updates is just an override to RI
of sorts. It just keeps me from having to refresh each
field if I make a change in the parent table. RI is pretty much Programming
101, and I have never had problems working with it in the past
From all of the research that I have been reading, there is nothing
mentioned about a form having to be unbound.
The research simply says that the combo box will update on code that I've
provided in the example. I have never had this problem before
with the inserting of combo box information before. The one I'm using from
both bound and unbound forms has the underlying recordset pulling
from tbl_cities.

Well Edward,

If the form is unbound to any record source, it will definitely work.
However,
not if it bound to a table or query.

I don't know why you were thinking that by insert a record in the Cities
table would not move the record in the form. This is an insert record
event
for tbl_Vendor of the form. When you insert a new City_ID into tbl_Vendor,
the form automatically moves to a new record because of the "Enforce
Referential Integrity" enabled with cascade updates. Meaning, that you
have a
Vendor with two or more City_ID's. The form is also known to cause the
tbl_Vendor or Cities index properties to change.

It appears that you're creating a one to one relationship but ended up a
many
to one or even many to many?

The table is in 4th normal form. The Cities table contains only the
City_ID
and the City. They are linked in tbl_Vendors on City_ID and do have RI
and
cascade update. That's not the problem. That's the same structure
basically for the form that is unbound, and it works fine.

After doing some more testing, I've managed to narrow down that it IS
because the form is bound to tbl_Vendors. I'm not sure how to get around
this because I need to update most of the vendor information along with
the
city/state/zip.

Like I said, it is updating the table on the execute. It also does the
same
for acErrDataAdd and for moving through the underlying recordset with
.Update features. That means that it isn't the Execute statement per se.
It is the form itself.

It is updating the city table, but it then moves to a new record on the
form.

My guess is that your tables are not correctly normalized. Between the
form's

[quoted text clipped - 27 lines]

Any thoughts? I feel sure that it has something to do with the fact
that
the underlying cause is because of the bound form. Unbound works fine.

 

[Edward]

With all respect, that is incorrect.

RI simply prevents you from deleting the data from a parent table when there
are related records in child tables. The cascade update is simply a smaller
override that allows for automatic refreshing of the child tables without
having to systematically refreshing each field where the reference is found.
Cascade delete overrides the safeties and allows for the deletions of the
child tables' records first before removing the primary record in the parent
table.

In reality, one to one relationships are rare, but they do exist. Many to
many are almost non-existent, and I think IF you get a many-to-many it is
probably because of bad database design. I think the same about one-to-one
relationships UNLESS there are special circumstances where they are needed.
In all of my years of programming, only one program required tables based on
one-to-one relationships. If the database is normalized correctly, the most
common WILL be the one to many relationships. I know because I've been
using this structure for years, and I teach this at the college level. I'm
one of those who actually programs in fourth normal form and sometimes
fifth, depending on the way that the table structure is needed.

Like I said before, I have been updating records in a one-to-many
relationship for years without incident, which is the nomal way of
programming. This same code works fine with unbound tables and to some
degree with the bound table, but it is simply going to another record, which
it shouldn't. I am now leaning toward a corrupt table, which I've seen
Access do before.

In the future, I would appreciate it if you would please refrain from
writing as an expert if you truly are not aware of correct data structure.
While I appreciate your enthusiam to offer assistance, your advice for the
RI is absolutely incorrect on the basis of a one-to-one relationship, and
had I been a programming novice, your advice would have had me chasing down
trails that were incorrect. If your table structures are one-to-one, then
may I suggest that you revist them and evalutate whether or not they have
been normalized correctly.

 

[Edward]

Thanks. Perhaps you misunderstood what I posted, but what I posted does in
fact support one-to-many relationships. The problem lies with a corrupted
database, not with the relationship structure. FYI, I have read up on RI.
I teach it, and have for many years. I'm not accusing anyone, and I
apologize if it came across that way. I'm saying that what you offered as a
solution was not correct. In my database, the city table is the one and the
vendors is the many. I'm simply using the vendors as vehicle to update the
one side, which is possible.

Best regards

Well, I'll leave it to you to do what ever you want to do with your
database.


I would suggest you read up more on RI before you accuse someone. I'm not
saying you can't have one to many with RI. As according to the details you
have given, it's a one to one relationship not possible to have a one to
many.

With all respect, that is incorrect.

RI simply prevents you from deleting the data from a parent table when
there
are related records in child tables. The cascade update is simply a
smaller
override that allows for automatic refreshing of the child tables without
having to systematically refreshing each field where the reference is
found.
Cascade delete overrides the safeties and allows for the deletions of the
child tables' records first before removing the primary record in the
parent
table.

In reality, one to one relationships are rare, but they do exist. Many to
many are almost non-existent, and I think IF you get a many-to-many it is
probably because of bad database design. I think the same about
one-to-one
relationships UNLESS there are special circumstances where they are
needed.
In all of my years of programming, only one program required tables based
on
one-to-one relationships. If the database is normalized correctly, the
most
common WILL be the one to many relationships. I know because I've been
using this structure for years, and I teach this at the college level.
I'm
one of those who actually programs in fourth normal form and sometimes
fifth, depending on the way that the table structure is needed.

Like I said before, I have been updating records in a one-to-many
relationship for years without incident, which is the nomal way of
programming. This same code works fine with unbound tables and to some
degree with the bound table, but it is simply going to another record,
which
it shouldn't. I am now leaning toward a corrupt table, which I've seen
Access do before.

In the future, I would appreciate it if you would please refrain from
writing as an expert if you truly are not aware of correct data structure.
While I appreciate your enthusiam to offer assistance, your advice for the
RI is absolutely incorrect on the basis of a one-to-one relationship, and
had I been a programming novice, your advice would have had me chasing
down
trails that were incorrect. If your table structures are one-to-one, then
may I suggest that you revist them and evalutate whether or not they have
been normalized correctly.

If you want to maintain RI, you must use one to one relationship. You
can't
use one to many. To maintain RI, a child record cannot be an orphan. It

[quoted text clipped - 35 lines]

both bound and unbound forms has the underlying recordset pulling
from tbl_cities.

 

[Edward]

I cannot see where I may have posted this, as it would not have been true.
My apologies for any misunderstandings. Thanks again for your willingness
to help.