Basic MS Access with VBA project guidance please (1 Viewer)

Hi everyone,

First off sorry for jumping in and asking questions on my first post. I have been searching the net templates and answers and while it has got me so far I still cant find specifics I'm stuck on and would appreciate any help.

I have an access design project to do for uni. To give you some background I've done some VBA programming in excel and have a good grasp of office and windows but haven't really used access before.

I have got Ms Access Inside out (bit over my head) and Ms Access Step by Step and have been working through their tutorials which have helped my understanding of access but dont arnt really specific enough to my project (or maybe i'm missing something)

The Brief is as follows:

"You have been instructed to design and develop and information system to capture students module choices for the business school. The system will run on MS Access and be supported by programs written in visual basic. Spec is as follows:

The system should be designed so that students can automatically select their options for semester 1 and 2 from a screen which will indicate to them whether they have broken any of the following constraints:
-> choose 30 credits in both first and second semester
-> business 2 cannot be selected unless Business 1 is
-> Programming 2 cannot be selected unless programming 1 is
-> Decision making and Data analysis share topics so students cant do both
-> Bus Finance and Corp Finance share topics so students cant do both

There is a table with 7 module options for each semester and their credit weight, either 10 or 20.
The outline is deliberately vague, 20% of marks will be for supplementary features that will enhance the systems functionality and usability.

------------------
Based on the examples I have worked through, I see it like a basic ordering system:

3 tables

Customers (Students)
Products (Modules)
Orders (Chosen Modules)

I need a database that will let me input customer information (that I can do)

Then allow me to Create an order for the customer Using a form that lists the products available from the products table

When Creating the order, with the form, rules need to be enforced to ensure the order is valid (ie certain products are not chosen together, the products value adds up to 30 for semester 1 and 2).

Once the order is complete a report is generated to show the products ordered (modules chosen)


Is this the right way to look at the database design?

I see the module choice form as having all the modules listed and tick boxes, however the only order forms I have managed to find have drop downs, any pointers here?

Am I right in thinking it would be VBA that will allow me to set and enforce the rules on module selection? If so any pointers on how?

Is there a way to get VBA to validate the inputs on the order form in real time, eg grey out one selection if another is selected, not allow more module to be ticked than 30 credits. I guess the code can be looped to do this? or is it easier to check the inputs after ok is pressed then give a warning box if its incorrect.

Again apologies for coming in on my first post and asking a torrent of probably basic questions, but I have tried my books and the net and would appreciate any guidance

Kind Regards, tom999

I think you are on the right track, likening the project to an ordering system. It seems that the crux of the project is in the validation, though.
Yes, VBA will probably be your best friend when it comes to validating the choices. You can do it however you'd like, but I would be reluctant to hard-code these validation rules.
OK, I'll play the professor and you play the student...
You: "Here's my project"
Me: "Very nice, but I've changed my mind about allowing people to take Bus Finance and Corp Finance in the same term. How do I, as a user, change that rule?"
You: "Hmmm...well, umm...first you press Alt+F11..."

Maybe your professor is not as much of an A55Hole as me...if he is, you might want to give that some thought.

For selecting courses, I think I would favor list boxes over check boxes. Look at some of the wizards in Access...they offer all the fields in a listbox on the left and buttons that allow you to push or pull selections to/from the listbox on the right. You could have two listboxes on the right to represent each semester.
Alternatively, you might want to use a single multi-select listbox for each semester and allow users to select all the things they want for each semester. You could validate after each selection, and/or in the 'before update' event of the form.
For unbound listboxes, check out the 'additem' method in VBA.

Everthing depends on the intended scope of the project, and your desire to do very well or just OK.

Just food for thought. Keep in mind that it is your project, not mine or anyone elses. Once you get rolling, it will be a piece of cake...you can learn a little or a lot along the way.