You are here:

Using MS Access/Populate subform w/Information from Main Form


I have a main form where audit numbers(PK)are entered i.e. AA1001

On the subform the audit finding number (PK) that is entered is always the audit number (FK in subform) followed by a dash and then a two digit number i.e. AA1001-01

I would like the field in the subform to be populated with the audit number and a dash so all the user has to do is enter the last two digits. Also, there could be more than one audit finding per audit, so there could be several records in the subform (related to one audit number) with addition audit finding numbers i.e. AA1001-02, AA1001-03, AA1001-04 etc.

Any suggestions would be greatly appreciated.

This is unnecessary and goes against good design.

I'm going to assume that Audit numbers are generated outside the application and entered manually. I'm also going to assume that the audit finding numbers are also entered manually.

If the subform is linked to the main form on the Audit Number, then the Audit Number FK is automatically populated. To include that as part of the PK of the subform record is redundant. What I would do is use an Autonumber PK for the subform table. Enter the sequence number manually and then have an unbound control to display the Audit Finding Number:

=[AuditNumber] & "-" & Format([AuditFinding], "00")

The above expression (using actual field names) will display the Audit Finding Number. you seek.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

I apologize if you felt my opening remark was less than polite. It wasn't meant to be. I assumed you weren't aware of the design techniques and was just trying to explain why my solution was different from the way you were asking to do it.


Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.