We help IT Professionals succeed at work.

How to populate the subform with a filtered query

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
I want to update the subform with a filtered query in Ms Access,but for some reasons the query is failing, see below:

Parent form: frmGrn
Subform : sfrmGrnDetails Subform

Private Sub CmdGrnForms_Click()
'  Const SQL_SELECT As String = "SELECT * FROM QryGrnSubformUpdates;"
  
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Dim i As Long
  Set db = CurrentDb
  Set qdf = db.QueryDefs("QryGrnSubformUpdates")
For Each prm In qdf.Parameters
prm = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)

Set qdf = Nothing
 rs.MoveFirst
    Do While Not rs.EOF
    rs.AddNew
            rs![Forms].[frmGrn].[sfrmGrnDetails Subform].[PurchasesID] = ("PurchasesID")
            rs![Forms].[frmGrn].[sfrmGrnDetails Subform].[ProductID] = ("ProductID")
            rs![Forms].[frmGrn].[sfrmGrnDetails Subform].[Qty] = ("Qty")
            rs![Forms].[frmGrn].[sfrmGrnDetails Subform].[Cost] = ("Cost")
  
  rs.Close
  Set rs = Nothing
  Set db = Nothing
  Loop
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
I guess you're looking for something like

Private Sub CmdGrnForms_Click()

  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Dim i As Long

  Set db = CurrentDb
  Set qdf = db.QueryDefs("QryGrnSubformUpdates")
  For Each prm In qdf.Parameters
    prm = Eval(prm.Name)
  Next prm

  Set prm = Nothing
  Set rs = qdf.OpenRecordset(dbOpenSnapshot, dbSeeChanges)
  Set qdf = Nothing
  If Not rs.Bof And Not rs.Eof Then
    Do While Not rs.EOF
      [sfrmGrnDetails Subform].SetFocus
      DoCmd.GoToRecord , , acNewRec
      [sfrmGrnDetails Subform].Form![PurchasesID] = rs![PurchasesID]
      [sfrmGrnDetails Subform].Form![ProductID] = rs![ProductID]
      [sfrmGrnDetails Subform].Form![Qty] = rs![Qty]
      [sfrmGrnDetails Subform].Form![Cost] = rs![Cost]
    Loop
  End If

  rs.Close
  Set rs = Nothing
  Set db = Nothing

End Sub

Open in new window

But I have to admit, what do you need Eval() for?

I'd prefer normally something like this:

Private Sub CmdGrnForms_Click()

  Const SQL_INSERT As String = _
    "INSERT INTO baseTableOfsfrmGrnDetails ( PurchasesID, ProductID, Qty, Cost ) "
    "SELECT PurchasesID, ProductID, Qty, Cost " & _
    ""FROM QryGrnSubformUpdates;"

  CurrentDb.Execute SQL_INSERT, dbFailOnErrors
  [sfrmGrnDetails Subform].Form.Requery

End Sub

Open in new window

But why don't you use simply a stored append query and just execute that?
ste5anSenior Developer

Commented:
Can you explain, why you're using Eval(), please?