CODEWITHSUNDEEP
ms-accessvba
Patrick Pruneau
Patrick Pruneau

Reputation: 679

Access VBA - Add new row in a subform on

I hope someone here can help me! :) Actually I've run into a wall with my VBA recently. I try to made a search form to find some specific field in the Access database. I successfully receive the information I need (Name, place, etc) from a SQL query but when I try to put all of this into a subform, I fail. I find the code below on internet and, execept the fact that the program don't crash, it does absolutly nothing. My really problem (I think) is that I don't know the right synthax to add value after the "addNewRecord".

Set dbs = CurrentDb

WriteToATextFile (sqlquery)
Set rst = dbs.OpenRecordset(sqlquery)


rst.MoveFirst
Do While Not rst.EOF

   If Me!sfrm_recherche_cours.Form.Dirty Then
        Me!sfrm_recherche_cours.Form.Dirty = False
    End If

    Me!sfrm_recherche_cours.SetFocus
    MsgBox rst!titre_cours
    DoCmd.GoToRecord , , acNewRec
    txt_sigle_cours = rst!sigle_cours
    txt_titre_cours = rst!titre_cours
    txt_nrc_cours = rst!titre_cours
    txt_faculte_cours = rst!faculte_cours


   rst.MoveNext
Loop

I try to find something on this site that looks like my problem but I've got nothing...so, if you can put me on the right way, I will be very happy:)

Upvotes: 1

Views: 12496

Answers (3)

Fionnuala
Fionnuala

Reputation: 91376

You appear to be running from the main form, but you are not adding a refernce to the subform to your controls, it should be:

Set dbs = CurrentDb

WriteToATextFile (sqlquery)
Set rst = dbs.OpenRecordset(sqlquery)


rst.MoveFirst
Do While Not rst.EOF

   If Me!sfrm_recherche_cours.Form.Dirty Then
        Me!sfrm_recherche_cours.Form.Dirty = False
    End If

    Me!sfrm_recherche_cours.SetFocus
    MsgBox rst!titre_cours
    DoCmd.GoToRecord , , acNewRec
    Me!sfrm_recherche_cours.Form.txt_sigle_cours = rst!sigle_cours
    Me!sfrm_recherche_cours.Form.txt_titre_cours = rst!titre_cours
    Me!sfrm_recherche_cours.Form.txt_nrc_cours = rst!titre_cours
    Me!sfrm_recherche_cours.Form.txt_faculte_cours = rst!faculte_cours


   rst.MoveNext
Loop

However, it is often easier to simply run an append query and the requery the subform.

 sSQL="INSERT INTO MySubformTable " _
   & "(sigle_cours, titre_cours, nrc_cours, faculte_cours) " _
   & "SELECT sigle_cours, titre_cours, nrc_cours, faculte_cours " _
   & "FROM SomeOtherOrEvenTheSameTable " _
   & "WHERE SomeField=SomeNumber"

 dbs.Execute sSQL, dbFailOnError
 Me!sfrm_recherche_cours.Form.Requery

Upvotes: 3

BIBD
BIBD

Reputation: 15414

I think what you want is a DoCmd.FindNext immediately after rst.MoveNext

An alternate way to do this same kind of operation without having to involve navingating records on forms would be to open a second record set and use rst2.AddNew to create a new record and rst2.update to finish off the record

it the block would look something like this

dim rstemployees as RecordSet

''Outside the loop
Set rstEmployees = _
   dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

'' other stuff happens
Do While Not rst.EOF
    rstEmployees.AddNew
    rstEmployees!EmpName = "Fred"
    rstEmployees.Update
Loop
'' more stuff

rstEmployees.Close
set rstEmployees = Nothing

Upvotes: 0

Summer-Time
Summer-Time

Reputation: 1874

Its easy with the recordset object Me!sfrm_recherche_cours.Form.Recordset.AddNew

Upvotes: 0

Related Questions