4 Replies - 1344 Views - Last Post: 17 January 2013 - 05:48 AM Rate Topic: -----

#1 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

How to insert multiple values into access from a vb6 in a single click

Posted 16 January 2013 - 11:41 AM

I am using a Recordset named "rs" and displayed the value of rs in a datagrid named "datagrid1" and the values of rs is as follows:

rs.Open "SELECT subjectcode.Year1, subjectcode.Year2, subjectcode.Subjectcode, subjectcode.Subjectname, subjectcode.Theory_Practical, q.Fee FROM subjectcode INNER JOIN ( SELECT fees.Year1, fees.Year2, 'Theory' As FeeType, fees.Theoryfee As Fee FROM fees UNION ALL SELECT fees.Year1, fees.Year2, 'Practical' As FeeType, fees.Practicalfee As Fee FROM fees)  AS q ON (subjectcode.Theory_Practical = q.FeeType) AND (subjectcode.Year2 = q.Year2) AND (subjectcode.Year1 = q.Year1)", con, 1, 3



I can't attach file here and it show server error and so please take a look into the screenshot of the file here http://tinypic.com/r/dbsjdf/6 and in that image you can see a button named "save". When i click save button then all the values in that datagrid should be saved in the table named "studentexam" in 2007 Access in a "single click" of save button. I have tried the following code but it saves only the first value for many times (more than 100 times a same value and no primary key set)

Do Until rs.EOF
rs3.Open "select * from studentexam", con, 1, 3
rs3.AddNew
rs3!AdmnNo = admno.Caption
rs3!semester = semester.Caption
rs3!Subjectcode = rs.Fields("Subjectcode")
rs3!Regular_Arrear = "Regular"
rs3!Fee = rs.Fields("Fee")
rs3.Update
MsgBox "Record Saved!"
rs3.MoveNext
rs3.Close
Loop



could anyone give me a solution for this and if you need more info ask me...

Is This A Good Question/Topic? 0
  • +

Replies To: How to insert multiple values into access from a vb6 in a single click

#2 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3619
  • View blog
  • Posts: 12,477
  • Joined: 12-December 12

Re: How to insert multiple values into access from a vb6 in a single click

Posted 16 January 2013 - 12:05 PM

A brief looks suggest that your MoveNext should apply to the outer recordset (rs):

rs.MoveNext

You could also move the Open and Close for rs3 outside the loop.

This post has been edited by andrewsw: 16 January 2013 - 12:08 PM

Was This Post Helpful? 1
  • +
  • -

#3 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

Re: How to insert multiple values into access from a vb6 in a single click

Posted 16 January 2013 - 04:54 PM

View Postandrewsw, on 17 January 2013 - 12:35 AM, said:

A brief looks suggest that your MoveNext should apply to the outer recordset (rs):

rs.MoveNext

You could also move the Open and Close for rs3 outside the loop.

I have tried both movenext and close outside of the loop but it doesn't work and accidentally i replaced two values. Means, i placed the rs3.close first and then i placed rs.movenext and now it works fine friend... before i used "rs3.movenext" and thanks for your info friend !
Do Until rs.EOF
rs3.Open "select * from studentexamdetail", con, 1, 3
rs3.AddNew
rs3!AdmnNo = admno.Caption
rs3!semester = semester.Caption
rs3!Subjectcode = rs.Fields("Subjectcode")
rs3!Regular_Arrear = "Regular"
rs3!Fee = rs.Fields("Fee")
rs3.Update
rs3.Close
rs.MoveNext
Loop
MsgBox "Record Saved!"


Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3619
  • View blog
  • Posts: 12,477
  • Joined: 12-December 12

Re: How to insert multiple values into access from a vb6 in a single click

Posted 16 January 2013 - 05:22 PM

No worries; but you could still move rs3.Open before the Do Until and rs3.Close after the Loop; there is no reason to keep opening and closing the recordset, and it is inefficient to do so.
Was This Post Helpful? 1
  • +
  • -

#5 anandrosan1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 23
  • Joined: 20-December 12

Re: How to insert multiple values into access from a vb6 in a single click

Posted 17 January 2013 - 05:48 AM

View Postandrewsw, on 17 January 2013 - 05:52 AM, said:

No worries; but you could still move rs3.Open before the Do Until and rs3.Close after the Loop; there is no reason to keep opening and closing the recordset, and it is inefficient to do so.

Yes friend you are logically right and now i got it that i wrongly placed the open recordset after the do condition. Thanks for correcting me friend :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1