Solved

access form2

Posted on 2011-03-12
10
220 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
i fouund solution last post
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 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

6 Experts available now in Live!

Get 1:1 Help Now