Urgent Help Needed on Linking a Combo box to other data

[jfielding2000]

I have a major problem. I can figure out. I'm going to give you as much info as possible to see if you can help me. I will be forever grateful if you can. I've been looking at books and tutorials all day and I feel like and idiot because I cant get my head around this.



The business department of a university has set up a database, containing details of the students, and the modules that they study in their final year.

Most students take three final year options, comprising two choices and 301 – Special Management Project. There are some students studying Combined Honours (COM) who may take between one and six options.



I have the following tables:

Students - contains details of students. Students are identified by StudentNo. The course code is a three-letter abbreviation of the course name.

Modules - contains details of the modules available. Each is identified by a module number. Where more than one set is required for any module these are distinguished by a suffix letter e.g. 314A, 314B.

Enrolment - contains details of student choices. Each record contains simply the StudentNo and a GroupNo. There will be three records for most students.

Courses - contains the codes and full names of the student courses



I have created a form that I have attached which I cannot get to work.

I have a combo box, which, when you select and change what's in the options, should change the rest of the data.

The combo box is linked to 'Student No' and when I change it, the others (Surname, Forenames, CourseCode, PAT), do not.

Please help?!

I am trying to design with the following desirable features:


• A well-designed user-interface including: - Visually effective forms, VBA code to automate the system, and Pull-down user menus to replace the Access menus
• Search facilities to find individual students or sets
• A more comprehensive set of reports and forms
• A range of options selectable by the user for viewing the reports
• All queries to be created in SQL
• Useful features of your own design

I am struggling a lot with this, and its really annoying me I can hardly do any of it!!

 

[RuralGuy]

Did you use the ComboBox wizard to create a ComboBox that will "look up a record" for you?

 

[jfielding2000]

Yes I did. When I click on the combobox (in form view) the different StudentNo values list. But when I select one of them, the rest of the data does not change. I would like it so the rest of the data changes.

So when I change the StudentNo, the rest of the details, such as Surname, Forenames and PAT changes too. At the moment they do not.

 

[RuralGuy]

The wizard will create an UnBound ComboBox for this function and put code in the AfterUpdate event of that control. I don't think you made the right selection from the questions asked by the wizard. IIRC it is the third answer. Try again.

 

[jfielding2000]

I've tried all the different options in the wizard and I'm pretty sure I'm choosing the correct options. I dont understand what you mean by IIRC? I am a very VERY basic user of access - I must appologise!

Is there any chance you could download the file I have attached and look for yourself and see what I'm doing wrong?

 

[RuralGuy]

IIRC (If I Remember Correctly) Create another ComboBox with the wizard and choose the third option. What version of Access are you using?

 

[jfielding2000]

Access 2007

 

[RuralGuy]

I just tried it in 2007 and the ComboBox wizard works the same. Select the 3rd option for your new ComboBox.

 

[jfielding2000]

I'm trying that, but all it does is list them all at once! I want to be able to change the StudentNo i.e. from 93001 to 93005, and then all the other fields - which are Surname, Forenames, Coursecode, PAT should change. But they're not.

Oh my God I've been trying all day to get this work and still no success!!!! HELLPP!!! lol

 

[RuralGuy]

And when you use the cboLookup control on the bottom of this form, does it do what you want?

 

[jfielding2000]

Thankyou for your help. You're obviously really trying to help - just gotta say I appreciate it.

I'm afraid that's not what I'm looking to do. I'd like the cbolookup to only display the student numbers - and when you change those values - the surname,forenames etc will all change at the same time.

Does that make sense?

 

[RuralGuy]

Like this db?

 

[jfielding2000]

Wow getting closer but still no . . .

when you click on the drop down sign, and choose a different number, the other values should change to the appropriate value.

What you have done is configure it so when you scroll though they change one after the other in alphabetical order?

 

[RuralGuy]

I'm really not understanding what you are trying to get this to do. You can select *any* Student Number in the ComboBox and the other controls on the form will display the values for that record.

 

[jfielding2000]

On the file you have posted, if i change the value, by clicking on the little arrow on the drop down, and changing it from 93001 to 93010 (for example) nothing happens.

 

[jfielding2000]

Ok, for some really reallly Odd strange reason it's just started working on my PC. Probably because I didnt enable local content on the security settings when I initially started it up.

Many thanks for your help!

Any suggestions and what I do next?

 

[RuralGuy]

Any suggestions and what I do next?

That is your option. If you have a specific problem then ask the question and someone will probably offer a solution. I would start a new thread if I were you.