Populating a multirow form from SQL.

  • Thread starter Thread starter Ian Norris
  • Start date Start date
I

Ian Norris

I have a multirow form which I want to populate via SQL.
I retrieve 13 records from SQL into a recordset, but when
I try to enter them into a form the first row in the form
is constantly overwritten leaving me just the 13th record
displayed. I populate the form using the following code.

Do While Not rs.EOF
With rs
Forms!mainform!subform!field1 = !field1
Forms!mainform!subform!field2 = !field2
rs.MoveNext
End With
Loop
rs.close

Each insert completely fills the row on my subform and I
thought
that it would automatically move to the next row for my
next insert.

Does anyone know how I can force a move to the next row
after each insert.

Many Thanks
 
I have a multirow form which I want to populate via SQL.
I retrieve 13 records from SQL into a recordset, but when
I try to enter them into a form the first row in the form
is constantly overwritten leaving me just the 13th record
displayed. I populate the form using the following code.

Do While Not rs.EOF
With rs
Forms!mainform!subform!field1 = !field1
Forms!mainform!subform!field2 = !field2
rs.MoveNext
End With
Loop
rs.close

Each insert completely fills the row on my subform and I
thought
that it would automatically move to the next row for my
next insert.

Does anyone know how I can force a move to the next row
after each insert.

Many Thanks
Click to expand...

Sounds like you are using the wrong aproach. SQL is much better at inserting selected data than Access code is.
DoCmd.RunSQL "INSERT [table name] (Field1, Field2) [your SELECT SQL here]"
Try to look up the INSERT statement (action queries, append queries) in the help files if you need more clarity, they explain
it well.
DoCmd.RunSQL "INSERT Table2 (Field1, Field2) SELECT Field1, Field2 FROM Table1"
The columns in the SELECT statement must match the columns in the table or if distinct columns are designated in the INSERT
statement (such as "(Field1, Field2)") it must match that definition.

If you really want to use code to do this, there are 2 simple steps you can add to your code. After setting all of the
fields, force Access to update (save) the record, then move to the new record.
To make Access update the record (depending on your MS Access version) either
Forms!mainform!subform.Refresh
Or
Forms!mainform!subform.Dirty = False
The DoCmd.GoToRecord command doesn't let you specify a SubForm (or does it?) so use the Recordset object (if available in
your MS Access version)
Forms!mainform!subform.Recordset.AddNew

Jeremiah Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top