Re: ADO - Strange Behaviour
From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 06/28/04
- Next message: Dirk Goldgar: "Re: Enumerating Table Fields in ADO?"
- Previous message: Justin VN: "search engine - embedded file"
- In reply to: Ben Johnson: "ADO - Strange Behaviour"
- Next in thread: Ben Johnson: "Re: ADO - Strange Behaviour"
- Reply: Ben Johnson: "Re: ADO - Strange Behaviour"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Jun 2004 01:18:05 -0400
"Ben Johnson" <anonymous@discussions.microsoft.com> wrote in message
news:222c701c45cc3$277c4450$a501280a@phx.gbl
> 1. I have a many-to-many relationship between two tables
> that is working via a third, linking, table.
> 2. My main data entry form is based on a query as there
> are numerous tables that data is drawn from to populate the
> form.
> 3. To associate an entity (Inspector) with each job
> record, a sub-form is used, based on a query that relates
> the current job record to the linking table mentioned in
> Point 1.
>
> The linking table holds three fields:
> - InspectorID, Category, ReportID
>
> The query that populates the sub-form in Point 3 uses the
> ReportID, which is contained in the main data entry form,
> as the Master and Child field. So far, all of this works fine.
>
> PROBLEM: When I need to add/change/delete an Inspector
> associated with a job the linking table is doing strange
> things. At the moment it's set up as follows:
> - the AfterUpdate event on the sub-form causes code to run
> that uses ADO to open a recordset (very similar to the
> query that the sub-form is based on) and alter the current
> record based on the current contents of the ComboBox
> control that triggered the event.
> - adding a new record works as expected, and changing an
> existing record works as expected
> - deleting a record will cause the current record to be
> deleted (which I want it to do), showing #Deleted# in each
> field of the record - BUT depending on what you do next,
> part of the record, the InspectorID and ReportID, will
> reappear in the table!!!
>
> I've tried everything I can think of but cannot fix this.
> Sometimes even when you move off the record, another one of
> these phantom records is created in the linking table.
>
> Is the problem because the control is based on a query
> already, and the ADO recordset manipulation is causing this
> weird behaviour??
>
> I know the code does what I intended it to do because I've
> put msgBoxes in between all of the steps in the code to
> report the current state of each variable as the code
> executes. The problem seems to occur after the Recordset
> object has been closed and control of the ComboBox has been
> handed back to the sub-form. That seems to be when these
> incomplete entries are appearing in the linking table.
>
> All help most appreciated!
>
> Many thanks in advance,
> Ben Johnson
> Private email:
> b<x-remove-this-x>johnson[at]netspace dot net dot au
I don't think there's enough information in your post to identify the
cause of the problem. Could you post the relevant fields from each of
the three tables, the recordsources of the form and subform, and all
code relevant to the question? I agree that it probably has something
to do with the way you're updating a table independently of the form
that is based on it, but I'd need more detailed information to see the
exact problem.
-- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
- Next message: Dirk Goldgar: "Re: Enumerating Table Fields in ADO?"
- Previous message: Justin VN: "search engine - embedded file"
- In reply to: Ben Johnson: "ADO - Strange Behaviour"
- Next in thread: Ben Johnson: "Re: ADO - Strange Behaviour"
- Reply: Ben Johnson: "Re: ADO - Strange Behaviour"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
