Update query inside for loop

[novice]

Hi,

I have a query as shown below which gets called when a button is clicked. For each student i have subjects upto 12, which can be entered on form inside textboxes (txtSubj*).

stSql1 = "UPDATE [STUDENT] SET [STUDENT].[SUBJ 1] = (" & txtSubj1.Value & ") WHERE [STUDENT].StudentID = " & txtStudentId.Value & " "

I would like to make a single call for update query and iterated inside a for loop so that same query can be used for 12 subjects.

Is it possible to do this. I have tried something like this but not getting with it. Can any one please let me know on how to go about with this.

for subjIndex = 1 to 12
stSql1 = "UPDATE [STUDENT] SET [STUDENT].[SUBJ & subjIndex] = (" & (txtSubj & subjIndex.Value & ") WHERE [STUDENT].StudentID = " & txtStudentId.Value & " "
next subjIndex

 

[DCrake]

Why not just have one sql statement that updates all 12 at the same time even if there is nothing to update?

David

 

[gemma-the-husky]

this looks about right

HOWVER

after the strsql = statement you need

docmd.runsql sqlstrg
(or)
currentdb.execute sqlstrg

to actually make the action happen

do you have one of those?

 

[novice]

@DCrake: I dont want to do like that as the number of subjects will be increasing in future, and i know the total number of subjects before hand. So, i think it will be simpler to do in 'for' loop.

@gemma-the-husky: I do have those statements which you mentioned.

 

[DCrake]

It seams to me that your tables are not normailsed. For a start you should have a seperate table for student subjects with the student id as the parent child link. In your layout each student has been allocated the same amount of physical space for all possible subjects. You are going to get into difficulty when you want to analyse this data. For example counting how many students are studing maths. As maths could be in any column for any student.

By having a one to many relationship between students and subjects, not only are you normalising the data you are making it more efficient in the space it is saving in the tables. Don't forget if you carry on the way you are going and you need to add further subjects you are going to have to modify your form to accommodate these extra subjects.

David

 

[novice]

Hi David,

Thanks for your reply. I understand your concern and I'm able to figure this out. Any ways, i'm going to change the layout of my tables with proper normalisation added. But right now for my current version i have to make sure that my current code is working fine.

Continuing with my current question, is it possible to call update query inside a for statement as shown above.

 

[DCrake]

Yes it is your syntax was wrong

stSql1 = "UPDATE [STUDENT] SET [STUDENT].[SUBJ & subjIndex] = (" & (txtSubj & subjIndex.Value & ") WHERE [STUDENT].StudentID = " & txtStudentId.Value & " "

Should be

stSql1 = "UPDATE STUDENT SET SUBJ " & subjIndex & " = " & Me(txtSubj & subjIndex & ") WHERE StudentID = " & txtStudentId

 

[novice]

Thanks a lot. Its working perfectly fine. But, using the same idea, when i want to populate the entries to the form from the table as below

For subjIndex = 1 To 21
Me("txtAge" & subjIndex) = rs![SUBJ " & ageIndex & "] (rs is the record set)
Next ageIndex

I'm getting the following error. "Items cannot be found in the collection corresponding to the requested name or ordinal"

if i write as: txtAge1 = rs![SUBJ 1] then it is working fine. Is it not possible to do this with elements in rs?. Please let me know. Thanks.

 

[DCrake]

Before applying the syntax it would make it clearer if you passed the names to variables

StrFormField = "txtAge" & subjIndex
StrRstfield = "SUBJ " & ageindex

then

Me(StrformField) = rs(StrRstField)


David

 

[novice]

Hi David,

I guess we cannot give in that way as the statement should be like

Me(StrformField) = rs(SUBJ 1)

but if we give Me(StrformField) = rs(StrRstField), It will be interpreted as

Me(StrformField) = rs("SUBJ 1").

 

[DCrake]

If you look at correctly

Me(StrFromField) = Rs(StrRstfield)

will be interpreted as

Me("txtAge1") = Rs("SUBJ 1")

Where 1 is the index counter on the loop

To prove this

Debug.Print Me(StrFormField)
Debug.Print Rs(StrRstField)

David

 

[novice]

It is working absolutely fine. Thanks for your help.