Actually, I had not pasted the complete code. I have a main form and subform in Access. In the main form I have some fields from the master table and in the subform I have the fields from detail table. Now when I select the Diet (which is in the main form)from the combo box in the master form and click on the OK button, the subform should fill with all the records corresponding to that diet in the subform.
But when I run the application, only the last record is being displayed in the subform. That is if there are fifteen records correspnding to Diet A then all the fifteen records are not displayed. Instead, only the last record is displayed in 15 rows.
When I run the program step by step by using F8 key, then I get the error
Run-time error 2499: You cant use the GotoRecord action or method on an object in Design view.
in the stmts DoCmd.GoToRecord , , acFirst, DoCmd.GoToRecord , , acNewRec

If I comment the DoCmd stmts I dont get the error but the last record is displayed in all rows of subform.
I am pasting the complete code I have used in the OK click button.
Dim dbs As dao.Database, rst As dao.Recordset, strSQL As String
    Dim fld As Field
    Dim Crittext As String
        Dim Crittext1 As String

    Set dbs = CurrentDb

strSQL = "SELECT DISTINCTROW DietMain.CodeNo,DietMain.Protein,DietMain.Hospital No,DietMain.Suger,DietMain.Advice,DietMain.Diet,PN o,DietSubform.item,DietSubform.SN FROM DietSubform INNER JOIN DietMain ON DietSubform.CodeNo = DietMain.CodeNo"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
Dim ccc ', Crittext
ccc = "Low Cholesterol Diet" '[Forms]![Dietmain]![DietTitle] ' = "me"
Crittext = "Diet=" & Chr(34) & ccc & Chr(34)
rst.FindFirst Crittext
Me![ge] = rst![Advice]
Me![HospitalNo] = rst![HospitalNo]
Me![CodeNo] = rst![CodeNo]
          Do
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acFirst
[Forms]![DietMain]![DietSubform].[Form]![Item] = rst![Item]
[Forms]![DietMain]![DietSubform].[Form]![SN] = rst![SN]
rst.MoveNext
DoCmd.GoToRecord , , acNext
Loop Until rst.EOF
rst.Close 'close recordset.

End Sub
Please tell the code correction and the new code to be given as I am new to VBA.

The code you posted makes no sense. It looks like you're trying to manually "paste" the data from your recordset into the subform by reading through a recordset. What is the record source of the subform? If you set the Link Master and Link Child properties of the subform control properly, Access should display the related records automatically as soon as you set or navigate to the related record in the outer form. No code required at all!


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

set the Link Master and Link Child properties of the subform it works. That is what I had done first. But I want it to fill the fields only when the button is clicked. What do I do?

Why not let Access display the related records as soon as the user picks the Diet (or whatever is the related field) on the outer form? I suppose you could leave the Link Child and Link Master properties blank and set them when the user clicks the button.

But I sense something else is going on here. What is the business problem you're trying to solve? Describe the problem from the perspective of your user, not your code or design.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

I had a very similar problem with DoCmd.GoToRecord and Subforms. I found the solution after several hours of playing with the code. To get DoCmd.GoToRecord to work on a Subform, set the focus on the Subform object as a whole (you don't want to set focus on a control in the Subform) with this line of code:
Forms!FormName!SubformName.SetFocus
Then use the DoCmd.GoToRecord command directly after.
Worked for me!