Solved

access form2

Posted on 2011-03-12
10
221 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
  • 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 84
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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 84
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 84
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now