Creating Recordset in VBA wit SQL statement

Question

I am trying to create a recordset in Access VBA that will show me all records in a table related to the current record of a form. My current code looks like this:

Private Sub Form_Load()

    Dim rst As Recordset

    Set rst = CurrentDb.OpenRecordset("Select [ID], [Ln] From [Order Detail] Where ((([Order Detail].[ID]) = [Forms]![Order Data Entry Header]![ID]))")

    rst.MoveLast
    Forms![Order Data Entry Header].LineNum = rst![Ln]

End Sub

I am doing this so that when adding new records they can be numbered sequentially after the highest number. When I run the form it get "Run-time Error: '3061' Too few parameters. Expected 1." on the Set rst line.

Any help would be appreciated.

Answer 1

The issue is the fact that the string you see there is exactly what is getting passed to the driver.

You need to "build up" the string, like so:

Set rst = CurrentDb.OpenRecordset("Select [ID], [Ln] From [Order Detail] Where ((([Order Detail].[ID]) = " & [Forms]![Order Data Entry Header]![ID] & "))")

Watch to make sure that [Forms]![Order Data Entry Header]![ID] is safe content, since you are building up an SQL statement.