You are here:

Using MS Access/Access 2010 - simple database


Access Objects/Tables
Access Objects/Tables  
First of all, I want to thank you for your patience.  For the most part, I feel I am pretty good at Access, but for some reason, this DB just does not want to work.  I am pretty sure it is basic, which is bothering me why I cannot figure it out.  
So this is what I want to create:  A DB that will make it really easy for the end users to enter in the data with minimal errors (if that is possible)
1.   The end user needs a form that will allow them to enter in the student data (fTransferCreditMain), and then be able to enter the transfer review information data (fTransferCreditSub).
2.   On the Sub form, I would like the end user to enter in the old course code.  From this entry, I would like the field for the new course code to lookup the data from the tblCourseCodes and autofill the new course code.  I also would like the same function to be applied to the course name.  Would I use a dLookup function for this?
3.   The tblCourseCodes lists ALL of the old and new course codes with the actual names respectively.  
I have tried creating queries (as you can see from the screenshot#1), but when I add the dLookup Expression, it goes into Read Only mode and I cannot add/edit records, which then effects the Forms.
Your assistance on this would be greatly appreciated.

ANSWER: 1. This part is pretty straightforward and I think you have this pretty much working. The user can either select or enter a student in the main form, then fill in the details of the transferred credit in the subform.

2. You do not need the course code or title information in tblReviewInformation. Since you already have it tblCourseCodes, its redundant. The way I would do this is to have a Combobox bound to the CourseID field. The RowSource of that combo would be:

SELECT CourseID, HumberCourseName, HumberCourseCodeOld, HumberCourseCodeNew,
FROM tblCourseCodes
ORDER BY HumberCourseName;

Set the Column Count to 4 and the Column Widths to 0";3",0",0"

Then add 2 unbound text boxes to your subform. Set the Controlsources to:


where cboCourse is the name of the comobo. So now the combo will display the course name and the two unbound text boxes the old and new codes.

3. Doing as I suggest in 2. should solve that problem.

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

---------- FOLLOW-UP ----------


I have completed as you suggested, and it works.  Thank you.

However, when I select the course code and it automatically displays the new course code and course name, it does not permanently write anywhere.  

So when I add a new student and a new record, the student is added, but the course information is not added to the table.

Any suggestions?


Again, you don't need the info anywhere else. One of the principals of a relational database is that data exists in one place ONLY. So, since the new code and course name already exist in tblCourseCodes you do NOT need them in tblReviewInformation.

You have the old course code in tblReviewInformation whihc provides a link to to tblCourseCode. You use that link to get at the name and new code. You can join the tables on cousecode in a query, or use a DLookup or other methods to pull the data from tblCourseCode.

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

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.