Solved

access form2

Posted on 2011-03-12
10
222 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 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