Populating a text field with selections from ListBox

[MattLongmuir]

Hi Team,
Here's the setup for my Access2K DB:

Courses (table)
- CourseID (autonumber field)
- CourseName (text field)

Trainee (table)
- CourseID (number; linked to CourseID in Course table)

Add Trainee (form, based on Trainee table)
- lbCourses (list box)

Basically here, i type in the name, contact details, select the company they are from (or add a company to my combo box) and this is all fine. Here's the trouble:

I have a List Box, with properties:

Name: lbCourses
Control Source: CoursesID
Row Source: SELECT [Course].[CourseID], [Course].[CourseName] FROM Course;
Multi Select: Simple

What i want to do, is be able to select the courses the trainee has already attended from the list, and then have that saved into the Trainee table under a new text field i'll create, called for example, "CoursesAttended".

What's the best way to go about this and HOW do i go about doing this?

My VB skills are very low at the moment, something i intend to rectify over the next month or 2.

Any help would be greatly appreciated!

 

[Eupher]

Take a look at Thread702-670645, it might help you get started. Let me know if you'd like a copy of the sample DB.

Ken S.

 

[MattLongmuir]

Ken,

I've had a look over that thread, and it looks to be exactly what i'm after! I'm a bit surprised my search didn't turn it up, but alls well that ends well - and i'm sure it will!

I'll have a crack at converting it into my db tomorrow, when i'm back at work and post here to let you know how it goes (plus give you a star!)

Rgds,
Matt

 

[MattLongmuir]

Hi Ken,

Ok, i've implemented the code from that thread you sent me to. As i'm sure you'd expect, i've hit a snag - probably the first of a few.

When i click the command button i get a compile error: User-defined type not defined.

This line of code is highlighted: "Dim CurDB As Database"
Any ideas on how to resolve this?

I'm using MS Access 2000 if that helps at all.
Thanks in advance for your help!

 

[MattLongmuir]

Ok, i fixed that. Didn't realise Access2K uses ADO by default. So i've added the DAO libraries now. I'm having another problem though, but i'll have a little play around and see if i can resolve it.

In the meantime, if you could send me that sample db, it'd be muchly appreciated. Email: mlongmuir@hotmail.com thanks!

=)

 

[Eupher]

It's a references problem. The newer versions of Access use the ADO object library by default. Open any code window, select Tools->References, and select "Microsoft DAO 3.x Object Library" from the list. You'll also need to move the DAO reference above the ADO reference in the list. Re-compile and you should be off and running.

Ken S.

 

[MattLongmuir]

Ken,

As soon as it reaches the line: "Rs.Update", i get this error, which i don't understand.

"" Run-time error '3022':

The changes you requested to the table were not successful because they would create duplicate valures in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entires and try again. ""

Any ideas?

 

[Eupher]

Matt,

This happens to me sometimes when I'm debugging a routine and data gets written to a field set for no duplicates, and I forget to delete the data before running the function again. I think this can also happen when trying to force an entry into an auto-number field, but not sure about that. Check the properties of your table fields.

Anyway, the sample DB is on the way. Referential integrity is not set in the table relationships; if you want to add that, make sure you include some method of saving the record before trying to add classes, or you'll get an error. Maybe in the AfterUpdate event of the LastName and FirstName fields put a DoCmd.RunCommand acCmdSaveRecord

Good luck, HTH...

Ken S.