[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

access form2

I have 3 tables in Access 2007.

I have a basic question.

I have a student, subject and an enrol form where the Enrol table is related to the other forms.
StudentID,SubjectID are the fileds that link Enrol to Student and Subject tables respectively.

To create a form for Student,Enrol and Subject is easy.
To add a new entry on form Enrol what can I do because I want the user to select from existing studentID/SubjectID.
0
jagguy
Asked:
jagguy
  • 4
  • 3
  • 3
1 Solution
 
JVWCCommented:
Without knowing the structure of your DB, I would start with a Many to Many relationship between the Students and the Subjects.
This would allow many students to be enrolled in many subjects.
The table between these would hold StudentID and SubjectID

See:
http://office.microsoft.com/en-us/access-help/types-of-relationships-RZ101772998.aspx?section=3

Cheers
JC
0
 
jagguyAuthor Commented:
HI,
no I need  1 to many relationship with enrol and I am not changing the design as it is simply a test case

students ->(PK)stID,name
subjects->(PK)subID,SubNam
Enrol->(PK)stID,(PK)SubID,Date

the stID,subID are the fields that join the tables of course.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You've already got a Join table (Enrol). That allows Many students to enroll in Many Subjects, and allows Many Subjects to be associated with Many Students.

So for your Enroll form, you'd need to choose a Student and a Subject. That's fairly straight forward - just add combo boxes that are based on the appropriate source tables, add a field for the Date, and you're done. If you've setup a UniqueIndex on the Enroll table with both Student and Subject (and it seems you have), then Access won't allow you to store the same Student for the same Subject more than one.

Or perhaps there is more to this question. If so, please provide further details.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jagguyAuthor Commented:
If I add a combo box for fields StID from student table and SubID from subject table and add date field from enrol table I cant scroll through records for enrol table if i wanted to view records.. IS this form just meant to be a add new record for enrol form ?

In this case I would need a button to click so i get to this form. How do i link a button to another form?
0
 
JVWCCommented:
Attached is a copy of something I did some time ago, along these lines. Have a look at the table relationships and the two forms, Students & Courses.
I used a multiple field primary index in the junction table, this would be your Enrol table.
LSMs suggestion of the date field can go in there too, or as I have done, in the Courses table.

Hope this helps explain.

Cheers
JC

EE26882805.zip
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you want to "scroll through" the Student and Subject records, you can use a Listbox or a Subform. These would show all the records at one time. Of course the Combo does as well, you just have to dropdown the list.

You may have set your form to DataEntry = True, which means you can ONLY add new records. This setting is on the Data tab in the Properties dialog.
0
 
jagguyAuthor Commented:
i used a lookup wizard on design of the required table.

this is what i want as you get to chose the fields on add new . not as good as new record button .
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<i used a lookup wizard on design of the required table.>

This is a bad-bad idea for many reasons, but it's your data.
0
 
JVWCCommented:
FWIW: and for anyone searching this solution, I agree with LSMConsulting.
There are much more robust solutions, especially in terms of data integrity...

Cheers
JC
0
 
jagguyAuthor Commented:
i fouund solution last post
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now