Many to Many relationship with bound Subform

[BBM]

I'm trying to implement a "keyword" search capability for
a database I'm coding. A many to many relationship exists
between keywords and the primary database records. Each
primary database record can have many keywords, and each
keyword can be associated with many primary database
records. There is a simple cross reference file that
stores the relationship between primary records and
keywords.

The list of keywords for the primary database record is
displayed to the user on a continuous subform bound to the
primary record form on the primary record key. The
datasource of the subform is an updatable query that joins
the cross reference file to the keyword file so that all
the keywords associated with the primary record can be
displayed.

When creating keyword associations, the user types in a
new value and hits <Tab> or <Enter>. There are two
possible cases 1) the user typed in a new keyword, in
which case the system should update both the keyword file
and the cross reference (this works OK), or 2) the user
keys in an existing keyword, in which case only the cross-
reference file needs to be updated.

I'm trying to implement this by using an event procedure
tied to the BeforeUpdate event of the textbox on the
continuous subform. This procedure tests the keyword file
to see if the entered keyword already exists, and if it
does, it explicitly opens the cross reference, writes a
new cross reference record to the existing keyword, and
then cancels the BeforeUpdate event, which tells Access
not to attempt to update the main bound recordset. At
this point the files are right, I have one keyword record
with two references to it in the cross reference.

But...
On my form, presumably because I cancelled the record
update, the cursor is still in the same textbox field at
the end of the keyword string just typed in. If I try to
tab off the field, it tries to update again, and crashes
on the attempt to create a duplicate cross reference
record.

What I would like is for the cursor to advance to the next
field in the list, which is probably the new row (but it
doesn't have to be - the user may have just edited an
existing value).

I've tried to requery the main datasource at various
points (in the BeforeUpdate procedure, in the form
OnCurrent event), thinking that since the cross reference
is updated, the data will be re-populated correctly, but
no luck with this Access won't allow it.

So how do I tell the control on the subform to accept the
input without trying to perform a database update, and
then advance the cursor - even if the next row is the new
row.

Sorry for the long question. Thanks.

BBM

 

[Jason]

BBM

Don't know if this going to help, you're question/problem seems very elaborate, but here'goes

1> I'm thinking that you might be using the wrong event for this action. Like focusing on something new, when the old isn't complete. Loosely speaking, event procedures can be used at will, although each has its prescribed usage, around which your programming logic should be implemented

Good luck
Jason

 

[BBM]

Jason,

Thanks for your response. There are a limited number of
events to use in this sequence, and I think I've tried
them all without success. Control BeforeUpdate,
ControlAfterUpdate, Form Current.

In a nutshell my question is, is there a way to cancel the
database update for a control on a bound form, and still
have the control on the form act as if the update had
occurred? I'll post this as a separate question on this
board.

I've been trying to avoid the errors, I think I'll try
just handling them next.

Thanks again.

BBM

-----Original Message-----
BBM,

Don't know if this going to help, you're question/problem

seems very elaborate, but here'goes:

1> I'm thinking that you might be using the wrong event

for this action. Like focusing on something new, when the
old isn't complete. Loosely speaking, event procedures can
be used at will, although each has its prescribed usage,
around which your programming logic should be implemented.

 

[Michael Cheng [MSFT]]

Hi BBM,

I noticed that another post of you titled "Cancel Database Update on a
Bound Form" discussing the same topic as this one. I have added my reply to
that post, if you have follow up questions, would you please add your reply
to that post? I will be glad to work with you

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.