Writing to an Access DB with an SQL ...

HELP .. I'm really Stuck ....

I have a form that I'm using a SQL Select statement to get information from a table called Transcripts ... After I get the information I'm using it to populate a Combo Box with the students courses taken (Ex. ENG101) ... I have approx 25 combo boxes on the form and I'm using this SQL in each of the "on click" for the combo's. That is all fine a dandy.

The problem I'm having is trying to figure out how to syntax another SQL .. so that when you click add ... it will write all the info that is selected in the combo boxes (all 25) to a table called Checksheet ... which has a field called courses ... I need it to be able to list ALL the courses that have been selected ...

Feel free to send me an e-mail ...
[email protected]


Thanks in advance

Shawn

Hi,

Are all your combo boxes in a collection of combo boxes?

If they are then what you could do is:

db = connection to database

For each combobox in comboxcollection
ssql = "INSERT INTO Checksheet (courses) VALUES ('" & combobox.text & "')"
db.execute ssql
next combobox

If they are not in a collection then for each combobox you would need a seperate insert statement and an execute statement.

Hope this helps,

Preeti

Ok .. how would I put them into a "collection of combo boxes" ?


Thanks for your time .. it is really appreciated :)

Shawn

------------------
Shawn

Hi,

You could give all the comboboxes the same name, then use the Index property to reference them, but if this is not suitable, you can declare a collection object:

Dim x As New Collection

then in a subroutine that you perform at the load event, you add all your combo boxes to the collection:

Private sub AddCombos()
with x
.add combobox1
.add combobox2
' etc...
end with
end sub

Then to access the comboboxes values in one shot:

Dim ctr as control

for each ctr in x
ssql = "Insert into table (field) value('" & ctr.text & "'"
db.execute ssql
next ctr

That's all. If you want you could declare a combo box instread of a general control, but then you have to stick to combo boxes. this leaves the door open if you want to add anything else.

HTH,

Preeti