Automate append query

[jmj]

I'm pretty weak with vb, but think I need it for a particular issue I have. I made a little db that will combine test score data with the student data. The user imports an excel sheet, runs checks on student data, then spits out a report. There is one very cumbersome step for the user that I would like to eliminate if possible.
Each test score sheet is different- some have 12 questions, with a max of 100. Currently the test sheet is imported, then the user has to design and run an append query to put the data into the masterTestScore table (which has all possible column names).
I would like the user to click a button and an insert command run.
The column names are logical (TestID, StudentID, Instructor, Question1, Question2, Question3, Answer1, Answer2, Answer3, etc)
I'd like to make something that will check to see how many questions/answers are in the imported table and append them to the master table.
Is this possible?
thanks,
J

 

[Zion7]

You have over 200 columns on the table????

If so, here's a quick idea....

Private Sub cmdInsertMasterTable_Click()

dim ctl as control

For each ctl in Me.Controls
If Not IsNull(ctl.Value) Then
CurrentProject.Connection.Execute _
"InSERT INTO tblTestScore(" & ctl.Name & ")VALUES" & _
"('" & ctl.Value & "')"
Next

...this is very rough and will not work entirely as you'd like. ie, after you'r first insertion, your next statement should be an UPDATE Query.
Secondly, you should check for autonumber fields, you don't want to Predefine that value, in your master table.


Good luck.