listbox, subform insert help

[Joe]

Hi all,

I haven't worked with Access in a LONG time and was hoping someone can
help me with the following issue:

I have 3 tables (STUDENTS, LANGUAGES, and STUDENTS_LANGUAGES).

The schemas are:

STUDENTS
STUDENT_ID - PK
NAME

LANGUAGES
LANG_ID - PK
LANGUAGE

STUDENTS_LANGUAGES
STU_LANG_ID PK
STUDENT_ID - FK
LANG_ID - FK

I have a form to insert data for students. This form contains a list
box containing all the values for LANGUAGE in the LANGUAGES table.

I want to insert the record into students and create record(s) in
STUDENTS_LANGUAGES for each language chosen in the list box. There
could be no language chosen or 1-n language chose.
Any suggestions on how to accomplish this?

Thanks,
Joe

[Rich P]

You could try something like this (for learning purposes)

Create a new table. In the table design view, add fields, studname,
Lang. In the properties area for each field there are 2 options,
General and Lookup. For the Lang field click on Lookup. In the Display
Control window select List Box. For the Row Source you can add a Select
statement like this:

Select Lang_ID, Language From Languages

In the Column Count field enter 2. For column widths add 0, 1. This
makes the Lang_ID field not visible and only the Language field is
visible. This places a dropdown box in the Lang field when you open the
table. So you could add a student Fred Smith, then when you enter the
Lang field you will get a dropdown arrow with a list of Languages from
the Languages table. Pick a language. The Name of the Language is what
you will see in that field, but when you query the table, you will get
the Lang_ID field. The Lang_ID field is really what gets entered. If
you change the column widths to 1, 1, then the Lang_ID field will also
be visible. If you switch the Select statement to

Select Language, Lang_ID...

The Language field will be the what actually gets written to the Lang
field. You could also just do

Select Language From Languages

If you base a data entry form on this table, you will also have the
dropdown box in the Lang field.

Just some ideas.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[jv]

' first save the student data with something like:
RunCommand acCmdSaveRecord

' next write to the STUDENTS_LANGUAGES table with something like this:
Dim varItm As Variant
For Each varItm In Me!lstLanguages.ItemsSelected
DoCmd.RunSQL "INSERT INTO STUDENTS_LANGUAGES " & _
"(STUDENT_ID, LANG_ID) " & _
"VALUES (" & Me!StudentID & ", " &
Me!lstLanguages.ItemData(varItm) & " )"
Next varItm

'this assumes that STU_LANG_ID is an autonumber field and that
STUDENT_ID and LANG_ID are both numeric fields.
Good Luck

Julie Vazquez

[Joe]

Thanks to all for replying. It makes sense now and was able to code it
correctly due to the help I received here.

My problems now are:

How to I clear out the list boxes when I move to a new record?

How do I populate the list boxes with the saved results when I come
back to records I previously entered/saved?

Are there any good books on Access 2003/VBA someone can recommend?
Thanks again,
Joe

[jv]

To clear the list box:
For x = 0 To lstLanguages.ListCount - 1
lstLanguages.Selected(x) = False
Next x

To the repopulate the box:
For x = 0 To lstLanguages.ListCount - 1
If lstLanguages.ItemData(x)= DLookup("LANG_ID",
"STUDENTS_LANGUAGES", "STUDENT_ID=" & me!StudentID)
Then
lstLanguages.Selected(x) = True
End If
Next x

It is probably better to implenent this with a subform.