CODEWITHSUNDEEP
ms-accessvbams-access-2007
Hawsidog
Hawsidog

Reputation: 165

How to add DAO recordset as rows in a datasheet subform

I have the following code to move product information from a sales quote to a lease qualification form. The products go to a subform which is in datasheet view, however, each row in the recordset just updates the first row of the subform.

sql = "SELECT ProdID, ProdQty, ProdPrice FROM ProductsQuoted WHERE quoteID = " & quoteeID
  Set db = CurrentDb
  Set rs = CurrentDb.OpenRecordset(sql)

  If Not (rs.EOF And rs.BOF) Then
     rs.MoveFirst
     Do Until rs.EOF
        [Forms]![Qualification]![QualificationDetails]![Item] = rs.Fields("ProdID").Value
        [Forms]![Qualification]![QualificationDetails]![Qty] = rs.Fields("ProdQty").Value
        [Forms]![Qualification]![QualificationDetails]![Price] = rs.Fields("ProdPrice").Value
        rs.MoveNext
    Loop
  End If

How can I have each record in the dataset update a new row on the subform?

Upvotes: 0

Views: 619

Answers (1)

Hawsidog
Hawsidog

Reputation: 165

Okay, I was able to use DoCmd.GoToRecord,, acNewRec and meet my need. 

sql = "SELECT ProdID, ProdQty, ProdPrice FROM ProductsQuoted WHERE quoteID = " & quoteeID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)

       If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF
            Debug.Print rs.Fields("ProdID") & ", " & rs.Fields("ProdQty") & ", " & rs.Fields("ProdPrice")
            QualificationDetails.SetFocus
            QualificationDetails!Item.SetFocus
            DoCmd.GoToRecord , , acNewRec
            [Forms]![Qualification]![QualificationDetails]![Item] = rs.Fields("ProdID").Value
            [Forms]![Qualification]![QualificationDetails]![Qty] = rs.Fields("ProdQty").Value
            [Forms]![Qualification]![QualificationDetails]![Price] = rs.Fields("ProdPrice").Value
            rs.MoveNext
        Loop
    End If

Upvotes: 1

Related Questions