Autofill a subform based on the record selected in Main form (1 Viewer)

aladinabox · Dec 29, 2012

Hi,

I'm fairly new to access and have been trying to figure this out for days now and cannot come up with a solution, someone will probably know how to do this straight away! I'm hoping anyway.

I have a database with a main form that displays all details relating to a particular item. On the subform, I want to be able to populate fields based on the code number that is chosen in the main form.

I'm not sure if that makes complete sense so please come back with any questions as to what I'm looking for.

Thanks in advance

bob fitz · Dec 29, 2012

It sound as though you need to set "Link Child Fields" and the "Link Master Fields" properties of the SubForm control.

aladinabox · Dec 29, 2012

Hi Bob,

Thanks for the quick response. I have the Link Child Fields and the Link Master Fields properties set up, I have them linking through Contract Number, then through the Course Code. What I want then is the rest of the fields to be autofilled from the values that are in the course code, do this sound possible?

Thanks again.

bob fitz · Dec 29, 2012

Can you post a copy of your DB. It would need to be in A2003 mdb format for me to open it.

aladinabox · Dec 29, 2012

I have attached the db in 2003 format. You will see the Record of Common Awards AIS form, this is the form I'm referring to. I would like to be able to make the subform look up the tblAssessmentTitleAndCode, and based on the Course Code field, to autofill the Module Title and Module Code with the appropriate records.

Thanks very much Bob for your time. This has been wrecking my head for days!

bob fitz · Dec 29, 2012

Your subform is bound to a table that has no records, so what do you expect to see in the subform?

aladinabox · Dec 29, 2012

I have the module title and module code from tblAssessmentTitleAndCode appearing in this subform, I was hoping I could perform some kind of lookup against the course code in the main form, that would look for the same course code in the tblAssessmentTitleAndCode and fill the module titles and module codes, I have probably gone about it in a totally incorrect way have I?

bob fitz · Dec 29, 2012

As I do not know what your db is to be used for, it is difficult to say if you have gone about things in the wrong way or not, but I think that may be the case.
Can you explain the purpose of the db, and what entity each of the tables is used for?

aladinabox · Dec 29, 2012

The database is being used to record all training courses run in a company, the tblAssessmentInformation is to store the Modules and Assessment that are linked to each training course and needs to be updated with dates of tests, but I wanted this to pull the Module Title and module code from another table once the course codes were matching.

The tblAssessmentTitleAndCodes contains the Module Titles and Codes and the course code they correspond to.

The tblAuditinformation I have set up to autofill dates when certain documents will be due, with dropdowns needed later for reporting

The tblCommonAwards, I wanted to pull the Module Title and Code again from the tblAssessmentTitleAndCode table and the rest are dates that need to be added

The tblF11 db again I wanted to populate with module title etc from the tblAssessmentTitleAndCode table, and the rest of the details to be manually added

The tblGeneralCourseInformation contains all of the main course details, and appears as the main form part of all the forms, with the rest of the tables being the subforms.

Hope I have explained that correctly, thanks again.

bob fitz · Dec 30, 2012

I think your proplems stem from poor table design.
Perhaps the following link will help:http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

aladinabox · Dec 30, 2012

Thanks for that link Bob, I appreciate your time. I'm not sure I still fully understand the best way to go about it, is it that I should have a one to many relationship to the tblAssessmentTitleAndCode?

aladinabox · Jan 1, 2013

Hi Bob, one last question, do you think I should start the database over? I'm still messing around with it, but not getting anywhere.
Thanks.

bob fitz · Jan 1, 2013

I think so. Getting the table structure and the relationships between thee tables right is key.

aladinabox · Jan 1, 2013

Ok, thanks Bob, will do.

aladinabox · Jan 6, 2013

I did as you advised, and seperated out the tables after reading in more detail the website link you had sent me. I'm just wondering could I have your assistance one more time. I have most of my tables set up and I'm trying to create the form with subform, and I'm wondering on the best way to link the tables to get the correct result?

bob fitz · Jan 6, 2013

Can you post a copy of the db in A2003 mdb format.

Autofill a subform based on the record selected in Main form (1 Viewer)

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

aladinabox

Registered User.

bob fitz

AWF VIP

aladinabox

Registered User.

aladinabox

Registered User.

bob fitz

AWF VIP

Similar threads

Users who are viewing this thread