B
Ben Johnson
I have a relational database that uses a main Data Entry
form for the user to input data. The data entry form is
based on a query as it contains fields from multiple
related tables. For the most part it works fine.
I have one Many-to-Many relationship in my database, which
is facilitated by a linking table. Basically it goes
something like this:
tblInspectors is related to tblReports via
tblLink_InspectorstoReports. As the same inspector's name
could appear on the one report a number of times there is
also a Category field in the linking table to keep each
record unique (Category relates to the specific type of
inspection carried out - eg. Pest or Building). The fields
in the linking table are InspectorID, Category, ReportID.
In my Data Entry form the user is meant to select an
Inspector from a drop-down list for each category of
inspection required, and it is allowed that one inspector
could carry out more than one category of inspection in the
one report (hence the need for the Category field to
distinguish records in the linking table).
THE PROBLEM: The Inspector drop-down list is in a Sub-form
linked to the main form by ReportID, and is populated by a
Query that selects all fields (but only binds InspectorID)
from the linking table so that, when an Inspector is
selected for a new report the relationship is created
automatically. This works fine, except if the user makes a
mistake and needs to change the Inspector, or they selected
the Inspector in the wrong Category. I've written VBA code
to automatically populate the Category field once an
Inspector is selected, which works. It also blanks the
Category field if the InspectorID field is made Null - this
works too. But, if the user makes a mistake and blanks the
InspectorID field and then decides to repopulate it, the
linking table then creates another record. This leaves a
record in the linking table with the ReportID field filled
in but nothing else, and another record with the same
ReportID plus the Category and the InspectorID. I've
adapted my VBA to also blank the ReportID field in the
event the InspectorID gets blanked, but this then leaves a
record in the linking table with zero information in it.
What am I doing wrong?
Sample VBA code that I'm using:
Private Sub InspectorID_AfterUpdate()
If Me.InspectorID <> "" Then
Me.Category = "Build"
Else
With Me
.Category = ""
.ReportID = ""
.InspectorID = ""
End With
End If
Me.Requery
Me.Refresh
End Sub
Sorry about the long post - any help appreciated.
Cheers,
Ben.
email (remove the bits in parentheses):
b(x-discard-x)johnson(-at-)netspace.net.au
form for the user to input data. The data entry form is
based on a query as it contains fields from multiple
related tables. For the most part it works fine.
I have one Many-to-Many relationship in my database, which
is facilitated by a linking table. Basically it goes
something like this:
tblInspectors is related to tblReports via
tblLink_InspectorstoReports. As the same inspector's name
could appear on the one report a number of times there is
also a Category field in the linking table to keep each
record unique (Category relates to the specific type of
inspection carried out - eg. Pest or Building). The fields
in the linking table are InspectorID, Category, ReportID.
In my Data Entry form the user is meant to select an
Inspector from a drop-down list for each category of
inspection required, and it is allowed that one inspector
could carry out more than one category of inspection in the
one report (hence the need for the Category field to
distinguish records in the linking table).
THE PROBLEM: The Inspector drop-down list is in a Sub-form
linked to the main form by ReportID, and is populated by a
Query that selects all fields (but only binds InspectorID)
from the linking table so that, when an Inspector is
selected for a new report the relationship is created
automatically. This works fine, except if the user makes a
mistake and needs to change the Inspector, or they selected
the Inspector in the wrong Category. I've written VBA code
to automatically populate the Category field once an
Inspector is selected, which works. It also blanks the
Category field if the InspectorID field is made Null - this
works too. But, if the user makes a mistake and blanks the
InspectorID field and then decides to repopulate it, the
linking table then creates another record. This leaves a
record in the linking table with the ReportID field filled
in but nothing else, and another record with the same
ReportID plus the Category and the InspectorID. I've
adapted my VBA to also blank the ReportID field in the
event the InspectorID gets blanked, but this then leaves a
record in the linking table with zero information in it.
What am I doing wrong?
Sample VBA code that I'm using:
Private Sub InspectorID_AfterUpdate()
If Me.InspectorID <> "" Then
Me.Category = "Build"
Else
With Me
.Category = ""
.ReportID = ""
.InspectorID = ""
End With
End If
Me.Requery
Me.Refresh
End Sub
Sorry about the long post - any help appreciated.
Cheers,
Ben.
email (remove the bits in parentheses):
b(x-discard-x)johnson(-at-)netspace.net.au