Auto fill form fields in MS Access using VBA

Question

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. It's not working. 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:

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!

I also changed my description of the issue to be more accurate regarding displaying the fields from the qryDeliverable, not from the projects table. Sorry and thanks! — user1914490
A form should do data entry/edit for only one table. You should probably use form/subform arrangement, not include all tables in one form RecordSource. — June7
What are you really trying to accomplish? Do you just need to apply filter to existing records or do you need to populate tblProjects with new data from a deliverables import? — June7
I'm not trying to edit/add data to more than one table, I just want to display data from more than one table. The form has a source of the Deliverables table which stores the Project Name. The Project name is a cmbx lookup into the Projects table, so on the form, I want to display other fields from the Pelroject record. The project record contains an STO_ID field which is a link to the STO table. On the same form, I also want to display fields from the STO table. So, on the Deliverables form, I want to select a Project which is linked to an STO and display fields from both tables. — user1914490
Really shouldn't need code to display data from tables. If you have tables in form RecordSource then bind textboxes to those fields. However, only allow edits to fields from table the form is intended for, not the related 'lookup' tables. Dependent tables should be in a subform. — June7

Sola Oshinowo Sola Oshinowo · Accepted Answer · 2020-01-09T23:20:19

I will suggest you do the following

Create a combo box control on a form, in its control source, use the select query that will show all column names across the various tables that you want to retrieve records

2.Under Column count property, this will be the number of columns been retrieved by the select statement

Create text fields for all the columns been retrieved except the first column(this is because the first column would have been handled by the combo box)
Under the after update event of the combo box, do something like text7.Value=combo13.Column(1) where text7 is the textbox name, and column (1) is the second column in the select statement of the combo box. text8.Value=combo13.Column(2) where text8 is the textbox name, and column (2) is the third column in the select statement of the combo box.

etc.

Auto fill form fields in MS Access using VBA

1 Answers