Solved

access form2

Posted on 2011-03-12
10
225 Views
Last Modified: 2012-06-21
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
Comment
Question by:jagguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 6

Expert Comment

by:JVWC
ID: 35120056
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
 

Author Comment

by:jagguy
ID: 35120150
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
 
LVL 85
ID: 35121223
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:jagguy
ID: 35124184
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
 
LVL 6

Expert Comment

by:JVWC
ID: 35126612
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
 
LVL 85
ID: 35127116
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
 

Accepted Solution

by:
jagguy earned 0 total points
ID: 35137301
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
 
LVL 85
ID: 35139183
<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
 
LVL 6

Expert Comment

by:JVWC
ID: 35146759
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
 

Author Closing Comment

by:jagguy
ID: 35174497
i fouund solution last post
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question