If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keeping Records in Linking Table Accurate (Long Post)
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 |
Thread Tools | |
Display Modes | |
|
|