ADO - Strange Behaviour

[Ben Johnson]

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

 

[Dirk Goldgar]

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.

 

[Ben Johnson]

On the main form, the relevant field is tblReports.ReportID.
The linking table (tblLink_InspectorsToReports) contains
the fields InspectorID, Category, ReportID.
The subform (recorsource below) contains each of the fields
from tblLink_InspectorsToReports and the record displayed
is determined by the tblLink_InspectorsToReports.ReportID
field matching the tblReports.ReportID field on the main form.

There are actually three sub-forms all with the same
recordsource SQL with the only variation being the
tblLink_InspectorsToReports.Category changing for each
(either "Build", "Pest" or "Other"). The other sub-forms
currently don't use the code shown below because I can't
get it to work on the first one.

I've also changed the JOIN type (sub-form recordsource) a
number of times to see if that changed the behaviour. It
was originally "1" with Ref Integrity set. I think it's
currently "3" without Ref integrity. Doesn't appear to
have made any difference to the behaviour in the linking
table, described in my earlier post.

This is the recordsource for the main form:

SELECT tblAgents.*, tblClients.*, tblJobAddress.*,
tblReports.*, tblSolicitors.*
FROM tblJobAddress INNER JOIN ((tblSolicitors RIGHT JOIN
tblClients ON tblSolicitors.SolicitorID =
tblClients.SolicitorID) INNER JOIN (tblAgents RIGHT JOIN
tblReports ON tblAgents.AgentID =
tblReports.AgentID) ON tblClients.ClientID =
tblReports.ClientID) ON tblJobAddress.JobID =
tblReports.JobID;

This is the recordsource for the subform:

SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category,
tblLink_InspectorsToReports.InspectorID
FROM tblInspectors RIGHT JOIN tblLink_InspectorsToReports
ON tblInspectors.InspectorID =
tblLink_InspectorsToReports.InspectorID
WHERE (((tblLink_InspectorsToReports.Category)="Build"));


After the InspectorID control on the subform is updated:
****************************
Private Sub InspectorID_AfterUpdate()

Dim ReportNum As Integer
Dim Inspector As Integer
Dim frmName As String

frmName = Me.Name 'eg. frmSubQryInspectorBuild

If IsNull(Me.InspectorID) = False Then
Inspector = Me.InspectorID
End If

ReportNum = Forms!frmMaster!ReportID

Call UpdateLinkingTable(ReportNum, Inspector, frmName)

End Sub
****************************

The "UpdateLinkingTable" procedure:
****************************
Public Sub UpdateLinkingTable(RepID, InspID As Integer, frm
As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Ctg, strSQL As String

'Open an ADO connection into the data

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Ctg = Mid(frm, 19) 'Set the Category

'Construct the SQL string that will populate the Recordset

strSQL = "SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category, " _
& "tblLink_InspectorsToReports.InspectorID FROM
tblLink_InspectorsToReports " _
& "WHERE tblLink_InspectorsToReports.ReportID=" & RepID & " " _
& "AND tblLink_InspectorsToReports.Category='" & Ctg & "';"



With rs
.Source = strSQL 'Open the recordset
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

'Test for the current state of the record:
' 0 means there was no pre-existing record therefore one
needs to be
' created
' If InspID = 0 then the Inspector name has been deleted
from the control
' If InspID <> rs("InspectorID") then the Inspector name
has been changed by the user


Select Case rs.RecordCount

Case 0

rs.AddNew 'Add the new record
rs("ReportID") = RepID 'Populate each
field
rs("Category") = Ctg '
rs("InspectorID") = InspID '
rs.Update 'Write the
record to the table

Case Else

If InspID = 0 Then

rs.Delete adAffectCurrent 'It makes no
difference whether adAffectCurrent is
rs.Update 'specified or not, same
result in linking table


ElseIf rs("InspectorID") <> InspID Then

rs("InspectorID") = InspID
rs.Update


End If

End Select


rs.Close
cn.Close


Set rs = Nothing
Set cn = Nothing


End Sub
*********************************

As advised, the above code does what I think it's supposed
to do, but it's what happens after that

seems to be the problem. I hope this is enough information
to help me determine where I'm going

wrong.

Thanks heaps for any advice.

Ben Johnson

-----Original Message-----

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)


.

 

[Dirk Goldgar]

On the main form, the relevant field is tblReports.ReportID.
The linking table (tblLink_InspectorsToReports) contains
the fields InspectorID, Category, ReportID.
The subform (recorsource below) contains each of the fields
from tblLink_InspectorsToReports and the record displayed
is determined by the tblLink_InspectorsToReports.ReportID
field matching the tblReports.ReportID field on the main form.

There are actually three sub-forms all with the same
recordsource SQL with the only variation being the
tblLink_InspectorsToReports.Category changing for each
(either "Build", "Pest" or "Other"). The other sub-forms
currently don't use the code shown below because I can't
get it to work on the first one.

I've also changed the JOIN type (sub-form recordsource) a
number of times to see if that changed the behaviour. It
was originally "1" with Ref Integrity set. I think it's
currently "3" without Ref integrity. Doesn't appear to
have made any difference to the behaviour in the linking
table, described in my earlier post.

This is the recordsource for the main form:

SELECT tblAgents.*, tblClients.*, tblJobAddress.*,
tblReports.*, tblSolicitors.*
FROM tblJobAddress INNER JOIN ((tblSolicitors RIGHT JOIN
tblClients ON tblSolicitors.SolicitorID =
tblClients.SolicitorID) INNER JOIN (tblAgents RIGHT JOIN
tblReports ON tblAgents.AgentID =
tblReports.AgentID) ON tblClients.ClientID =
tblReports.ClientID) ON tblJobAddress.JobID =
tblReports.JobID;

This is the recordsource for the subform:

SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category,
tblLink_InspectorsToReports.InspectorID
FROM tblInspectors RIGHT JOIN tblLink_InspectorsToReports
ON tblInspectors.InspectorID =
tblLink_InspectorsToReports.InspectorID
WHERE (((tblLink_InspectorsToReports.Category)="Build"));


After the InspectorID control on the subform is updated:
****************************
Private Sub InspectorID_AfterUpdate()

Dim ReportNum As Integer
Dim Inspector As Integer
Dim frmName As String

frmName = Me.Name 'eg. frmSubQryInspectorBuild

If IsNull(Me.InspectorID) = False Then
Inspector = Me.InspectorID
End If

ReportNum = Forms!frmMaster!ReportID

Call UpdateLinkingTable(ReportNum, Inspector, frmName)

End Sub
****************************

The "UpdateLinkingTable" procedure:
****************************
Public Sub UpdateLinkingTable(RepID, InspID As Integer, frm
As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Ctg, strSQL As String

'Open an ADO connection into the data

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Ctg = Mid(frm, 19) 'Set the Category

'Construct the SQL string that will populate the Recordset

strSQL = "SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category, " _
& "tblLink_InspectorsToReports.InspectorID FROM
tblLink_InspectorsToReports " _
& "WHERE tblLink_InspectorsToReports.ReportID=" & RepID & " " _
& "AND tblLink_InspectorsToReports.Category='" & Ctg & "';"



With rs
.Source = strSQL 'Open the recordset
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

'Test for the current state of the record:
' 0 means there was no pre-existing record therefore one
needs to be
' created
' If InspID = 0 then the Inspector name has been deleted
from the control
' If InspID <> rs("InspectorID") then the Inspector name
has been changed by the user


Select Case rs.RecordCount

Case 0

rs.AddNew 'Add the new record
rs("ReportID") = RepID 'Populate each
field
rs("Category") = Ctg '
rs("InspectorID") = InspID '
rs.Update 'Write the
record to the table

Case Else

If InspID = 0 Then

rs.Delete adAffectCurrent 'It makes no
difference whether adAffectCurrent is
rs.Update 'specified or not, same
result in linking table


ElseIf rs("InspectorID") <> InspID Then

rs("InspectorID") = InspID
rs.Update


End If

End Select


rs.Close
cn.Close


Set rs = Nothing
Set cn = Nothing


End Sub
*********************************

As advised, the above code does what I think it's supposed
to do, but it's what happens after that

seems to be the problem. I hope this is enough information
to help me determine where I'm going

wrong.

Thanks heaps for any advice.

Ben Johnson

-----Original Message-----

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.

Ben -

I see what look to me like flaws in your logic, but rather than trying
to untangle them, may I suggest a simple, no-code way of doing what I
think you're trying to do? It seems to me that you are trying to use
ADO to do something that your subform can do all by itself.

Let me verify what you're after. As I understand it, you have a subform
that is supposed to add, change, and delete records in the table
tblLink_InspectorsToReports that match the current ReportID on the main
form *and* that have a specific value, "Build", in the Catgory field.
Only records in that category should be displayed by this subform, and
any records created should have "Build" as the category. Is that
correct?

If it is correct, here's how I would set it up.

1. Ditch the query from the subform's recordsource. Instead, set the
recordsource directly to tblLink_InspectorsToReports.

2. Put a hidden (Visible = No) text box on the main form, name it
"txtCategoryBuild", and set its controlsource to

="Build"

3. Set the Link Master Fields and Link Child Fields of the subform as
follows:

Link Master Fields: ReportID, txtCategoryBuild
Link Child Fields: ReportID, Category

4. Ditch all the code from the AfterUpdate event of InspectorID, and
delete your UpdateLinkingTable procedure. You should no longer need
that code, and it's just messing things up.

That ought to do it.

 

[Guest]

Many thanks Dirk. I'll give it a go tonight. Keeping my
fingers crossed as this issue has had me tearing my hair
out for a few weeks!

Yours in humble gratitude,
Ben Johnson

-----Original Message-----

On the main form, the relevant field is tblReports.ReportID.
The linking table (tblLink_InspectorsToReports) contains
the fields InspectorID, Category, ReportID.
The subform (recorsource below) contains each of the fields
from tblLink_InspectorsToReports and the record displayed
is determined by the tblLink_InspectorsToReports.ReportID
field matching the tblReports.ReportID field on the main form.

There are actually three sub-forms all with the same
recordsource SQL with the only variation being the
tblLink_InspectorsToReports.Category changing for each
(either "Build", "Pest" or "Other"). The other sub-forms
currently don't use the code shown below because I can't
get it to work on the first one.

I've also changed the JOIN type (sub-form recordsource) a
number of times to see if that changed the behaviour. It
was originally "1" with Ref Integrity set. I think it's
currently "3" without Ref integrity. Doesn't appear to
have made any difference to the behaviour in the linking
table, described in my earlier post.

This is the recordsource for the main form:

SELECT tblAgents.*, tblClients.*, tblJobAddress.*,
tblReports.*, tblSolicitors.*
FROM tblJobAddress INNER JOIN ((tblSolicitors RIGHT JOIN
tblClients ON tblSolicitors.SolicitorID =
tblClients.SolicitorID) INNER JOIN (tblAgents RIGHT JOIN
tblReports ON tblAgents.AgentID =
tblReports.AgentID) ON tblClients.ClientID =
tblReports.ClientID) ON tblJobAddress.JobID =
tblReports.JobID;

This is the recordsource for the subform:

SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category,
tblLink_InspectorsToReports.InspectorID
FROM tblInspectors RIGHT JOIN tblLink_InspectorsToReports
ON tblInspectors.InspectorID =
tblLink_InspectorsToReports.InspectorID
WHERE (((tblLink_InspectorsToReports.Category)="Build"));


After the InspectorID control on the subform is updated:
****************************
Private Sub InspectorID_AfterUpdate()

Dim ReportNum As Integer
Dim Inspector As Integer
Dim frmName As String

frmName = Me.Name 'eg. frmSubQryInspectorBuild

If IsNull(Me.InspectorID) = False Then
Inspector = Me.InspectorID
End If

ReportNum = Forms!frmMaster!ReportID

Call UpdateLinkingTable(ReportNum, Inspector, frmName)

End Sub
****************************

The "UpdateLinkingTable" procedure:
****************************
Public Sub UpdateLinkingTable(RepID, InspID As Integer, frm
As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Ctg, strSQL As String

'Open an ADO connection into the data

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Ctg = Mid(frm, 19) 'Set the Category

'Construct the SQL string that will populate the Recordset

strSQL = "SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category, " _
& "tblLink_InspectorsToReports.InspectorID FROM
tblLink_InspectorsToReports " _
& "WHERE tblLink_InspectorsToReports.ReportID=" & RepID & " " _
& "AND tblLink_InspectorsToReports.Category='" & Ctg & "';"



With rs
.Source = strSQL 'Open the recordset
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

'Test for the current state of the record:
' 0 means there was no pre-existing record therefore one
needs to be
' created
' If InspID = 0 then the Inspector name has been deleted
from the control
' If InspID <> rs("InspectorID") then the Inspector name
has been changed by the user


Select Case rs.RecordCount

Case 0

rs.AddNew 'Add the new record
rs("ReportID") = RepID 'Populate each
field
rs("Category") = Ctg '
rs("InspectorID") = InspID '
rs.Update 'Write the
record to the table

Case Else

If InspID = 0 Then

rs.Delete adAffectCurrent 'It makes no
difference whether adAffectCurrent is
rs.Update 'specified or not, same
result in linking table


ElseIf rs("InspectorID") <> InspID Then

rs("InspectorID") = InspID
rs.Update


End If

End Select


rs.Close
cn.Close


Set rs = Nothing
Set cn = Nothing


End Sub
*********************************

As advised, the above code does what I think it's supposed
to do, but it's what happens after that

seems to be the problem. I hope this is enough information
to help me determine where I'm going

wrong.

Thanks heaps for any advice.

Ben Johnson

-----Original Message-----
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.

Ben -

I see what look to me like flaws in your logic, but rather than trying
to untangle them, may I suggest a simple, no-code way of doing what I
think you're trying to do? It seems to me that you are trying to use
ADO to do something that your subform can do all by itself.

Let me verify what you're after. As I understand it, you have a subform
that is supposed to add, change, and delete records in the table
tblLink_InspectorsToReports that match the current ReportID on the main
form *and* that have a specific value, "Build", in the Catgory field.
Only records in that category should be displayed by this subform, and
any records created should have "Build" as the category. Is that
correct?

If it is correct, here's how I would set it up.

1. Ditch the query from the subform's recordsource. Instead, set the
recordsource directly to tblLink_InspectorsToReports.

2. Put a hidden (Visible = No) text box on the main form, name it
"txtCategoryBuild", and set its controlsource to

="Build"

3. Set the Link Master Fields and Link Child Fields of the subform as
follows:

Link Master Fields: ReportID, txtCategoryBuild
Link Child Fields: ReportID, Category

4. Ditch all the code from the AfterUpdate event of InspectorID, and
delete your UpdateLinkingTable procedure. You should no longer need
that code, and it's just messing things up.

That ought to do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Ben Johnson]

Hi Dirk,

This solution solves the basic problem of not creating
duplicate redundant records in the linking table, so thank
you for that. However, if you delete the InspectorID (via
the subform) you're still left with an entry in the linking
table that holds the ReportID and the Category. My
original reason for launching into the interesting world of
ADO/VBA was to avoid this very thing!! I wanted the record
to be completely deleted to avoid incomplete records
clogging up the table.

For the time being I think I'll stick to your solution, as
at least I can move on with the rest of the functionality -
but I'd still be curious to know why the duplication issue
occurred and how you'd handle it if you had to avoid
redundant records.

Many, many thanks for your help,
Ben Johnson

-----Original Message-----

On the main form, the relevant field is tblReports.ReportID.
The linking table (tblLink_InspectorsToReports) contains
the fields InspectorID, Category, ReportID.
The subform (recorsource below) contains each of the fields
from tblLink_InspectorsToReports and the record displayed
is determined by the tblLink_InspectorsToReports.ReportID
field matching the tblReports.ReportID field on the main form.

There are actually three sub-forms all with the same
recordsource SQL with the only variation being the
tblLink_InspectorsToReports.Category changing for each
(either "Build", "Pest" or "Other"). The other sub-forms
currently don't use the code shown below because I can't
get it to work on the first one.

I've also changed the JOIN type (sub-form recordsource) a
number of times to see if that changed the behaviour. It
was originally "1" with Ref Integrity set. I think it's
currently "3" without Ref integrity. Doesn't appear to
have made any difference to the behaviour in the linking
table, described in my earlier post.

This is the recordsource for the main form:

SELECT tblAgents.*, tblClients.*, tblJobAddress.*,
tblReports.*, tblSolicitors.*
FROM tblJobAddress INNER JOIN ((tblSolicitors RIGHT JOIN
tblClients ON tblSolicitors.SolicitorID =
tblClients.SolicitorID) INNER JOIN (tblAgents RIGHT JOIN
tblReports ON tblAgents.AgentID =
tblReports.AgentID) ON tblClients.ClientID =
tblReports.ClientID) ON tblJobAddress.JobID =
tblReports.JobID;

This is the recordsource for the subform:

SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category,
tblLink_InspectorsToReports.InspectorID
FROM tblInspectors RIGHT JOIN tblLink_InspectorsToReports
ON tblInspectors.InspectorID =
tblLink_InspectorsToReports.InspectorID
WHERE (((tblLink_InspectorsToReports.Category)="Build"));


After the InspectorID control on the subform is updated:
****************************
Private Sub InspectorID_AfterUpdate()

Dim ReportNum As Integer
Dim Inspector As Integer
Dim frmName As String

frmName = Me.Name 'eg. frmSubQryInspectorBuild

If IsNull(Me.InspectorID) = False Then
Inspector = Me.InspectorID
End If

ReportNum = Forms!frmMaster!ReportID

Call UpdateLinkingTable(ReportNum, Inspector, frmName)

End Sub
****************************

The "UpdateLinkingTable" procedure:
****************************
Public Sub UpdateLinkingTable(RepID, InspID As Integer, frm
As String)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Ctg, strSQL As String

'Open an ADO connection into the data

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

Ctg = Mid(frm, 19) 'Set the Category

'Construct the SQL string that will populate the Recordset

strSQL = "SELECT tblLink_InspectorsToReports.ReportID,
tblLink_InspectorsToReports.Category, " _
& "tblLink_InspectorsToReports.InspectorID FROM
tblLink_InspectorsToReports " _
& "WHERE tblLink_InspectorsToReports.ReportID=" & RepID & " " _
& "AND tblLink_InspectorsToReports.Category='" & Ctg & "';"



With rs
.Source = strSQL 'Open the recordset
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

'Test for the current state of the record:
' 0 means there was no pre-existing record therefore one
needs to be
' created
' If InspID = 0 then the Inspector name has been deleted
from the control
' If InspID <> rs("InspectorID") then the Inspector name
has been changed by the user


Select Case rs.RecordCount

Case 0

rs.AddNew 'Add the new record
rs("ReportID") = RepID 'Populate each
field
rs("Category") = Ctg '
rs("InspectorID") = InspID '
rs.Update 'Write the
record to the table

Case Else

If InspID = 0 Then

rs.Delete adAffectCurrent 'It makes no
difference whether adAffectCurrent is
rs.Update 'specified or not, same
result in linking table


ElseIf rs("InspectorID") <> InspID Then

rs("InspectorID") = InspID
rs.Update


End If

End Select


rs.Close
cn.Close


Set rs = Nothing
Set cn = Nothing


End Sub
*********************************

As advised, the above code does what I think it's supposed
to do, but it's what happens after that

seems to be the problem. I hope this is enough information
to help me determine where I'm going

wrong.

Thanks heaps for any advice.

Ben Johnson

-----Original Message-----
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.

Ben -

I see what look to me like flaws in your logic, but rather than trying
to untangle them, may I suggest a simple, no-code way of doing what I
think you're trying to do? It seems to me that you are trying to use
ADO to do something that your subform can do all by itself.

Let me verify what you're after. As I understand it, you have a subform
that is supposed to add, change, and delete records in the table
tblLink_InspectorsToReports that match the current ReportID on the main
form *and* that have a specific value, "Build", in the Catgory field.
Only records in that category should be displayed by this subform, and
any records created should have "Build" as the category. Is that
correct?

If it is correct, here's how I would set it up.

1. Ditch the query from the subform's recordsource. Instead, set the
recordsource directly to tblLink_InspectorsToReports.

2. Put a hidden (Visible = No) text box on the main form, name it
"txtCategoryBuild", and set its controlsource to

="Build"

3. Set the Link Master Fields and Link Child Fields of the subform as
follows:

Link Master Fields: ReportID, txtCategoryBuild
Link Child Fields: ReportID, Category

4. Ditch all the code from the AfterUpdate event of InspectorID, and
delete your UpdateLinkingTable procedure. You should no longer need
that code, and it's just messing things up.

That ought to do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Hi Dirk,

This solution solves the basic problem of not creating
duplicate redundant records in the linking table, so thank
you for that. However, if you delete the InspectorID (via
the subform) you're still left with an entry in the linking
table that holds the ReportID and the Category. My
original reason for launching into the interesting world of
ADO/VBA was to avoid this very thing!! I wanted the record
to be completely deleted to avoid incomplete records
clogging up the table.

For the time being I think I'll stick to your solution, as
at least I can move on with the rest of the functionality -
but I'd still be curious to know why the duplication issue
occurred and how you'd handle it if you had to avoid
redundant records.

Ben, what is your intent in deleting the InspectorID? Normally, if you
intend to delete the link between Inspector and ReportID+Category, you
would delete the entire *record* in the subform, not just one of the
fields. In fact, I'd expect the link table to have the combination of
the three fields InspectorID, ReportID, and Category serving as its
primary key, and it would not be permitted to have a record with a Null
value in any of those fields. Trying to delete the value in the
InspectorID field, thereby setting its value to Null, would do nothing
but raise an error message, and that would be a correct response on the
part of Access, because -- as you point out -- it makes no sense to have
records in this linking table that are incomplete and thus don't
actually link anything.

If I were working in your database, it would frankly never occur to me
to delete the value of InspectorID in the subform, because I know that
what I want to do is delete the linking *record*, not the value in one
of its fields. But if InspectorID is part of the primary key of the
linking table, I'd get an error message if I tried. If you need to, you
can probably trap that error message in the form's Error event and
display a more meaningful message to the user. It may well be that you
can even ask the user if he intended to delete the linking record, and
do it for him.

 

[Guest]

Hi Dirk,

What you've described below (in deleting the entire record)
was precisely my objective when I attempted the ADO record
manipulation previously.

How it works now:
- User Selects a service (eg. Building Inspection)
- User then selects an inspector to perform the service
(resulting in new entry in linking table containing
ReportID, InspectorID and Category) based on your earlier post

What I was trying to achieve:
- Once the user has selected the Inspector (and thus the
record in the linking table is created), if the user then
decides "Oh dear, I've made a mistake - I meant to choose a
different service and inspector" and subsequently
highlights the currently displayed Inspector and hits the
Delete key, I wanted to ensure the entire record was
deleted - not just the InspectorID portion of the record,
as is currently the case.

Am I correct in interpreting your message that if I make
all three fields the collective Primary Key of the linking
table - then deleting the inspector (as described above)
will delete the entire record?

Forgive my ignorance, but in my education in Access we only
received rudimentary explanations of what happens behind
the scenes - the main focus was just making something work.
I still work with Access because I found databases
fascinating, and I'm continuing my self-education.

Once again, many thanks for your patient assistance thus far.

Kind regards,
Ben Johnson

 

[Dirk Goldgar]

Hi Dirk,

What you've described below (in deleting the entire record)
was precisely my objective when I attempted the ADO record
manipulation previously.

How it works now:
- User Selects a service (eg. Building Inspection)
- User then selects an inspector to perform the service
(resulting in new entry in linking table containing
ReportID, InspectorID and Category) based on your earlier post

What I was trying to achieve:
- Once the user has selected the Inspector (and thus the
record in the linking table is created), if the user then
decides "Oh dear, I've made a mistake - I meant to choose a
different service and inspector" and subsequently
highlights the currently displayed Inspector and hits the
Delete key, I wanted to ensure the entire record was
deleted - not just the InspectorID portion of the record,
as is currently the case.

Am I correct in interpreting your message that if I make
all three fields the collective Primary Key of the linking
table - then deleting the inspector (as described above)
will delete the entire record?

No, that's not what I meant to suggest. If InspectorID is part of the
primary key of the table, then the user will not be *permitted* to
delete it -- attempting to do that will raise an error. What the user
*ought* to be doing is either pressing the Escape key to undo a new
entry, or else deleting the record to remove an existing record.

However, if you really want your application to interpret the user's
deleting the InspectorID as a request to undo or delete the record, you
can trap the error and make it happen, by setting up an event procedure
for the form's Error event, like this:

'----- start of code -----
Private Sub Form_Error(DataErr As Integer, Response As Integer)

' Catch the "field may not be null" error, and interpret it
' as a request to delete/undo this record.
If DataErr = 3314 Then
Me.Undo
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If
Response = acDataErrContinue
End If

End Sub

'----- end of code -----

Note: this event procedure would go in the module for the subform -- the
form object that is bound to the linking table -- not the main form.

Forgive my ignorance, but in my education in Access we only
received rudimentary explanations of what happens behind
the scenes - the main focus was just making something work.
I still work with Access because I found databases
fascinating, and I'm continuing my self-education.

You and me both, Ben.