Solved

VB advices

Posted on 2011-09-02
12
293 Views
Last Modified: 2012-05-12
Hello,

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.



bd2.mdb
0
Comment
Question by:Noero
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36474358
i suggest that you break the many to many relationship of your tables, see how to do it

http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36474461
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 350 total points
ID: 36475330
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::
http://www.xoc.net/standards/default.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention

2. DO NOT use "Lookup fields" in your tables.
http://access.mvps.org/access/lookupfields.htm
...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.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36475433
try this as a starting point
bd2.mdb
0
 

Author Comment

by:Noero
ID: 36483214
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.

@boag2000:
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.

0
 
LVL 12

Expert Comment

by:telyni19
ID: 36484280
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36484485
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.

JeffCoachman
0
 

Author Comment

by:Noero
ID: 36489580
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.

Example:
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36526933
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)
RowSources
RecordSources
ControlSources
...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"
...to first select the class, then assign a student.

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

JeffCoachman

bd2-3-.mdb
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36529960
<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?
0
 

Author Comment

by:Noero
ID: 36551363
cause problem I didnot pursue the project, but you have partially answer to my problem.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36551880
ok
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

757 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

21 Experts available now in Live!

Get 1:1 Help Now