VBA Code & Relationship setup to populate form fields on After Update event

Copper Contributor

Hi, Thanks in advance for any help. This involves a little VBA code and three MS Access tables. 1) SR. Technology Officer (tblSTO), 2) tblProjects, 3) tblDeliverables. Among other fields,the STO table has an autonumber STO_ID (primary index) and the STO_Name (text). The tblProject table has ProjectNo (text) and STO_ID. These two combined are unique and make the primary key. The tblDeliverables table has ProjectNo, STO_ID, ServiceName (text). These three combined are unique and make the primary key. I have a qryDeliverable query which includes all three tables and all the fields from each table. I have a form frmDeliverable which uses the qryDeliverables as the record source and has a combo box field to select the ProjectNo from the Project table. The ProjectNo, STOName (from STO table) are displayed in the combobox drop down. Once ProjectNo is selected,  I want the ProjectNo, STOName, and a couple other fields from the qryDeliverables displayed, and a field (DesignerPlanner) I try to populate using some VBA in an "After Update" event.. Depending on how I play with the relationships and the VBA code, it either doesn't populate the fields, or the add record controls for the form disappear, or I get some relationship error.

A little history....I had this working perfectly when using an autonumber ID field (primary index) in both the Project and Deliverable tables. However, a new requirement came in to be able to be able to pre-populate (one time) the Deliverables table via import from a spreadsheet of records. With the Project file already populated, the spreadsheet to be imported into the Deliverables table has unique project numbers (ProjectNo) and a common STO_ID across all the records (just as a starting point), but can't possibly have each project ID number, so the index requirement wasn't met. I had to delete the project ID field from the Deliverables table and use the ProjectNo and STO_ID combined as the primary index. This was the start of all my problems.

 

Here is the VBA code (SQL Statements)

 

Private Sub Project_No_AfterUpdate()
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
If Not IsNull(ProjectNo) Then
Set rs = CurrentDb.OpenRecordset("qryProjects")
Me.STO_ID = rs!STO_ID
Me.DesignerPlanner = "Chiu, Derek;"
End If
End Sub

 

I hope this is clear and any help is appreciated. Thanks so much!

3 Replies

@hfreedman1957 

Compound PKs are never a good idea. You should have a PK on each table that is a single, Autonumber column. And it should be meaningless to the user.

 

But that doesn't mean that you can't have a composite unique index as well that can be used to enforce constraints.

 

Managing relationships with compound PKs is near impossible. So let's see if we can simplify this.

 

tblSTO has a good PK - STO_ID but tblProject should have an Autonumber PK, let's call it Project_ID. 

It can also have the natural key 'ProjectNo' which should not be the PK but it can have a unique index  to prevent two instances of the same ProjectNo.

 

tblProject already has a column STO_ID - this is the FK that relates to tblSTO.STO_ID.

 

Is this a One (STO) to Many Projects? or are you trying to create a Many To Many relationship? ie can one project have more than one STO?

 

If it's the latter, then you would be better off with 3 tables not 2 - tblSTO, tblProject and tblSTOProject. Each with a single column PK.

tblSTOProject would then have STOProject_ID as its PK and two FK columns - STO_ID and Project_ID.

 

You can then put a composite unique index on these two FK columns in tblSTOProject to apply the constraint of having only one instance of a STO for a Project.

 

You can then use the autonumber PK of tblSTOProject to join to tblDeliverable.STOProject_ID knowing that the unique composite index ensures that STO combined with Project is unique.

 

Then you could put another composite unique index on tblDeliverable including STOProject_ID and Service_Name to ensure you can only have one instance of a Service for each combination or STO and Project.

 

So flick the Compound PKs, use single column Autonumber PKs and use composite unique indexes to enforce the business rules.

 

In the attached ACCDB - check out the Relationship Diagram, Indexes on each table, the query and the form.

 

@hfreedman1957 

when you look at the indexes - you will at first see that I haven't set the composite indexes to be unique. So currently you can create duplicate records.

 

Try that first, then delete the duplicates, set the Indexes to be unique and then try to create duplicates. You shouldn't be able to.

 

So the main points are -

Get your table structures and relationships right

Use indexes, not compound PKs, to enforce business rules

Thanks so much for the response. Originally, I had an autonumber ID field for each of the three tables and everything worked great. They then wanted to import the records into the deliverables table, but had no way to know the project ID value which is why we removed it and used the project number as the key. However, incorporating both is worth a shot. I'll play with that and report back. Unfortunately, I won't be back at work for over a month so this will have to wait. Thanks again!