Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

VB advices


I am working on a database on access which have three tabels: Course, Student, joined by Link (many to many relationship).

My purpose is to put a mark to the student concerned for special course and add information to course.

I can import and link them but i have to not modify them, so my first step is to create a new database with a table listchoice, which store the selected course and student.
I would like to make this process in a formular.

The user select the course, push the button select course, he processes for the student in the
same way.

The problem is that i don't know how to program in VB: i am familiar with C, but here i don't know where to put function, create variables etc.. So if you could show me somes clues i should be very happy!

Thanks in advance.

  • 5
  • 3
  • 3
  • +1
1 Solution
Rey Obrero (Capricorn1)Commented:
i suggest that you break the many to many relationship of your tables, see how to do it
To start, let me say that this isn't going to be just a single question if you have never programmed in Visual Basic before. We can give you some steps to get started, but programming when you are learning is usually an interactive experience where you try what you know, see what the result is, and adjust the code if it doesn't work the way you want, asking questions or searching for answers along the way if you don't understand why or how something works.

For an application as complex as Access, it would be worth buying a book or going through some of tutorials available online in various places just to get a sense of what is available and how the pieces fit together. VBA is great for adding custom functionality to forms and reports when you have them set up correctly, but if you aren't familiar with how to set up the built-in pieces and how to use the functionality Access already offers without code, then it will be much harder to get the results you want. It's important to set up your data in your tables correctly before you put too much work into your forms, and it's important to set up your forms with your data input before you put too much work into your reports and other methods of data output.

It looks like you've put an object on your form to act as the button trigger for your "Add course" function once you build it, but the object you used is actually a toggle button rather than a command button. For triggering functions and actions, you want a command button because a toggle button is used for toggling an option on or off.

Once you create a command button, then you can press Alt+F11 or press the "Visual Basic" button to open the code window for your form. There is actually some code in there for a command button to move to the last record, although I don't see the button on the form anymore. Anyway, it will show you the structure for a piece of code that attaches to an object and makes it do something. For a command button, the most common function is the Click function, which will trigger when the user of the application clicks on the button. Most objects have several different event triggers that can run code automatically when various things happen.

You can create a placeholder function automatically from the form design view by opening the properties for the object, going to the Event tab, choosing the event (such as "On Click" for a command button), clicking the "..." button, and choosing "Code Builder". That will take you to an empty function set up to trigger when that event happens, where you can add more code.

I hope this helps get you started. Access will take some time to learn well, but it's very rewarding to be able to build something that does tasks automatically and handles your data for you the way you want.
Jeffrey CoachmanMIS LiasonCommented:
If I were you, I would first endeavor to fix your table designs here, *Before* ever worrying about VBA or Forms.

If the tables design is flawed, you forms will never work properly anyway....
(Note the way your Main form does not display all of the students enrolled in a Course...)

You have many issue here that seem to be a bit confusing, that will cause nothing but frustration when you do create forms and VBA.

1. Please adopt a standard naming convention::

2. DO NOT use "Lookup fields" in your tables.
...use them in your forms only...

3. Your table names are a bit confusing.
You have a "Person" table and a "Students".
As I see it the Person table should be named "Students" and the "Junction table should be called StudentCourses.

Your Junction table does not have a consistent matching naming structure to the related table keys.

You have tables with fields just as "ID", again, this will cause confusion later...
(Use StudentID, CourseID, ElevatorID, ...etc , ...for more clarity...)

It is not clear what a "List Choice" is or what purpose it serves...
You only stated that:
"My purpose is to put a mark to the student concerned for special course and add information to course."
...but it is unclear of what this means in the context of a Student/Courses design.

I am modifying your db slightly to reflect these issues and I will post it shortly.

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Jeffrey CoachmanMIS LiasonCommented:
try this as a starting point
NoeroAuthor Commented:
Thanks guys for all your quick answers,
@capricorn1: my many to many relationship is  broken, i have put it badly in the description of my problem.

@telyni: thank you for your advices, especially the one about button, the fact is I can identify the code about event, but the fact is I need to know where i could create global variables.

Thank you for your corrective support: great job! I thought i had to use loppkup field in order to make the relationship between Courses and Student possible but the predetermined integrity seems to run it.
This database is an exemple on what I am working. In reality, I CANT modify these tables. I could just access them.

My objective is to make a formular that allow to fill the table listchoice from selection of course and student. Thereafter i could create new tables linked with listchoice and refering to the proper courses and students. The purpose is to add property (mark, comment, community information) to course and eventually student.

Global variables can be created at the top of any module, where a module is a file that contains code. If you create an event procedure for a form, a module is automatically created for that form to hold the code for it, and then global variables can be placed at the top of it and will be available to all the procedures for that form.
Jeffrey CoachmanMIS LiasonCommented:
This is still unclear:
<My objective is to make a formular that allow to fill the table listchoice from selection of course and student. Thereafter i could create new tables linked with listchoice and refering to the proper courses and students. The purpose is to add property (mark, comment, community information) to course and eventually student.>

Here you are phrasing your question in terms of the interface, when instead, it would be clearer if you stated them in terms of what you are actually trying to accomplish
(You are also using the term "ListChoice" without it being clearly defined.)

In other words, just say something like:
 "I want to select a student from a list and be able to assign them to a class"

If you truly cannot modify the tables (and the designer is unwilling to make the recommendations we listed), then you will be at a distinct disadvantage while trying to code this.

NoeroAuthor Commented:
Well I will try to express my problem better:

Above all: I want to select a course and add specific information for it and the student registered.
Example: I select course, I add information: this course is boring, and i can add a status "hero" for the student registered.
That mean, in my opinion, to fill the table listchoice through a formular.
the formular allow me to click on a course to select it, that copy the id of course and the sudent concerned.

I select course id 1, the listchoice is filled like that:

Course chosen   student chosen
1                                     1
1                                     3

With that information I could access to the proper course and student and had new property.
I have changed the file in consequence. I am aware that, like telyni said, my question appeals anothers, so i am currently progressing by myself and i go back to you soon, even if you still deserve  points!
Jeffrey CoachmanMIS LiasonCommented:
I am still a bit lost on what you are calling a "listchoice "
In Access there are :
ListBoxes (I thing this is what you mean, but is is not applicable because a Listbox will not allow you to "assign" anything into it)
Comboboxes (This is probably what you mean, you will need one for the student and one for the class)
...but nothing called a "listchoice"

In any event it seems like you are describing three totally seperate operations:
1. <I select course, I add information: this course is boring>
This should be done when the course is first entered into the Course table
(unless you mean this is the "Students" comment about the course, Then, ...again, this would happen *after* the student is assigned to the class and the class is over.)

2. <and i can add a status "hero" for the student registered.>
I have no idea of what this in the context of Students and Clesses

3. If the goal is to select a student and assign them to a class, you need a Junction table and two comboboxes (Not a "listchoice")
 You can use the form I created in my sample: "frmCourseMain" first select the class, then assign a student.

Or use a form like I have in this new sample: "frmAssignStudentClass"


<and i can add a status "hero" for the student registered.>

Maybe this was a typo for "here"? Are you trying to track attendance for classes on specific dates or overall?

Or was it a reference to a grade or result in the course?
NoeroAuthor Commented:
cause problem I didnot pursue the project, but you have partially answer to my problem.
Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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