Using data from a stored procedure in Sql Server to fill an Access continuous form

Question

I'm trying to fill a continuous form in access 2013 using data from a stored procedure in ms sql server 2014. The database connection works, I can use other access forms to add data to the ms sql server database. So that's not the problem. Here's my stored procedure:

CREATE PROCEDURE spArtikelen AS
BEGIN
SELECT a.artikelnr, omschrijving, v.voorraad, p.prijs, a.leverancier
FROM artikel AS a, artikelprijs AS p, artikelvoorraad AS v
WHERE a.artikelnr = p.artikelnr 
AND a.artikelnr = v.artikelnr 
AND CAST(GETDATE() AS DATE) BETWEEN p.begindatum AND p.einddatum
END

And here's my vba code (I put the vba code on the LOAD event of the form):

Private Sub Form_Load()
    Dim rs As ADODB.Recordset

    Set rs = DbConn.Execute("EXEC spArtikelen")
        Do Until rs.EOF
            Me.txtArtikelnr.Value = rs!artikelnr
            Me.txtOmschrijving.Value = rs!omschrijving
            Me.txtVoorraad.Value = rs!voorraad
            Me.txtPrijs.Value = rs!prijs
            Me.txtLeverancier.Value = rs!leverancier
            Debug.Print rs!artikelnr, rs!omschrijving, rs!voorraad, rs!prijs, rs!leverancier
            rs.MoveNext
        Loop
End Sub!

the continuous form..

How do I fix this? I've been trying to get it work all day.. without success.. Every time I run the form, I only get one record, and I can't click through the records.!

Here's the data from the stored procedure I'm trying to put into the form. Possible solution?

Answer 1

Open your recordset with adUseClient for its CursorLocation property. Then set the form's Recordset property to your ADO recordset.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "EXEC spArtikelen", DbConn
Set Me.Recordset = rs

Then with artikelnr for txtArtikelnr.ControlSource, omschrijving for txtOmschrijving.ControlSource, and so forth the form should load with the recordset rows as you wish.

Note the data displayed within the form will be read-only. Hopefully that is not an unwelcome surprise.

Also, I suggested this approach because you already had a suitable ADO recordset. However, I think Gord's suggestion to use a pass-through query as your form's RecordSource is simpler.