I have program in VB.NET 2008 where I want to update the premamt cell value in the tblPrem Access table, where the premno is the maximum (autogenerated as 1,2,3...), but only within a specified set of common numbers in a column (contno) I am currently saving. This contno is from another table (tblMain) linked in this table (tblPrem) where it is the primary key, and just repeatedly generated after the clicking save button. Here is my current code which saves the data but does not do anything on the textbox1 + textbox2, and has an error message "At most one record can be returned by this subquery":
sqlQrySave = "UPDATE tblPrem SET premamt = " & textbox1 & " + " & textbox2 & " WHERE premno=(SELECT MAX(premno) FROM tblPrem GROUP BY contno);"
What I expect to happen is like this:
contno | premno | premamt
1................1.............45
1................2.............45
1................3.............45
1................4.............52 <----- textbox1 + textbox2
2................1.............12
2................2.............12
2................3.............12
2................4.............12
2................5.............18 <----- textbox1 + textbox2
3................1.............33
3................2.............33
3................3.............33
3................4.............33
3................5.............40 <----- textbox1 + textbox2
Anyone who can help me fix my code? Any help will be greatly appreaciated. Thanks!