Loading data into a subform

[Ian Norris]

Hi,

I am trying to load a number of records into a subform "Schedule Codes" from form "Certif" using the followowing code.
[CODE]Do While Not RS.EOF
With RS
Forms![certif]![schedule codes]![GroupNo] = !GroupNo
Forms![certif]![schedule codes]!

 = !Code
  Forms![certif]![schedule codes]![Date] = Now()
  Forms![certif]![schedule codes]![GroupNo].SetFocus
  DoCmd.GoToRecord , , acNewRec
  RS.MoveNext                                      
 End With
Loop
RS.Close
Forms![certif].SetFocus

On the first pass data is loaded into the first record of form Schedule Codes but Docmd.GoToRecor d , , acNewRec produces a "Can't go to Specified Record" error and subsequent loads overwrite the first record. I have tried to set focus to the subform but get the error "Can't move to the control Schedule Codes". The .SetFocus only seems to work if I enter a field name.

Any help on loadiing data into a subform would be appreciated.

[MikeTheBike]

Hi,

I am trying to load a number of records into a subform "Schedule Codes" from form "Certif" using the followowing code.

Do While Not RS.EOF
With RS
Forms![certif]![schedule codes]![GroupNo] = !GroupNo
Forms![certif]![schedule codes]![Code] = !Code
Forms![certif]![schedule codes]![Date] = Now()
Forms![certif]![schedule codes]![GroupNo].SetFocus
DoCmd.GoToRecor d , , acNewRec
RS.MoveNext
End With
Loop
RS.Close
Forms![certif].SetFocus

On the first pass data is loaded into the first record of form Schedule Codes but Docmd.GoToRecor d , , acNewRec produces a "Can't go to Specified Record" error and subsequent loads overwrite the first record. I have tried to set focus to the subform but get the error "Can't move to the control Schedule Codes". The .SetFocus only seems to work if I enter a field name.

Any help on loadiing data into a subform would be appreciated.

Hi

I have more questions than answers, but, on the basis that the code is running in the main (Certif) form, then I think to move to a new record in the sub-form should be (something) like this – may be!

DoCmd.GoToRecor d acDataForm, “schedule codes”, acNewRec

As it stands you are trying to move to a new record in the main form.

Looking at Access Help under GoToRecord may help.


HTH


MTB

[Ian Norris]

Yes I have tried putting the form name into the GoToRecord command but that returns the error The Object "Schedule Codes" isn't open. But it must be open as the first record is loaded. I've tried opening the subform again but, as expected, that opens a new version of the form.

[nico5038]

Your fields on the subform should be referenced like:

Forms![certif]![schedule codes].form![GroupNo] = !GroupNo

Check also: http://www.mvps.org/access/forms/frm0031.htm

Nic;o)

[Stewart Ross]

Hmm... I just can't see why are you trying to 'load records' into your subform in this laborious way. There's loads of ways to do it, but in all honesty trying to load data by overwriting fields on the form is not going to be the way forward for you.

If you want to use code why not just add the records to the subform's recordset using the normal methods available? Use recordsetclone to copy the subform's recordset, open the cloned recordset, add the new records using the Addnew method, set the field values, then use Update to store the record. When complete, close the recordset, and copy it back to the form concerned. Look up help on Addnew for code samples.

Alternatively, and more simply, if you are appending records to your recordset you can run an update query in SQL then requery the subform to refresh its recordsource.

If you are not appending records at all and you need to change your recordsource dynamically in response to user selections say you can change the form's recordsource in code to another recordset in one step instead of loading records one at a time. For a subform the syntax is

forms!formname!subformname.form.recordsource = recordsetname

or for an SQL statement

forms!formname!subformname.form.recordsource = "SELECT stuff FROM sometable ORDER BY somefield"

-Stewart

[MikeTheBike]

Hi again

Never like to leave a problem unsolved, but in view of Stewert's last post, which could not agree with more, I am reluctant to post the solution(s) I have for manupulating the sub-form records.

If it is felt this is the only way possible (which it obviously isn't) then I will post what I have, but I suggest one (or more) of Stewert suggestions are tried first.


MTB

[Ian Norris]

Thank you all for all your help and suggestions. I have implemented Stewart's SQL example solution and that works fine.
I was not aware that option was available. I'll store it away for future reference.

Ian