Why doesn't this work?

[AccessAmateur]

Why do I get 'runtime error 3061 too few parametrs' error with the 'Set rstName = .OpenRecordset()' staement in this:
I've followed the example in the help files as close as I could.
Next Question: The MsgBox gives a type mismatch error because rstName is not a String. How go I make a RecordSet into a String?

Private Sub InspNum_DblClick(Cancel As Integer)

Dim dbs As Database
Dim rstName As Recordset
Dim qdf As QueryDef
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")
With qdf
.SQL = "SELECT Name FROM List WHERE List.Num = " & Me.Num & " ;"

Set rstName = .OpenRecordset()

End With

'MsgBox "Name is " & rstName

 

[Rob.Mills]

It's because you're trying to open a recordset based on a query with a parameter. You get this error in this situation.

Here's how to fix it:

Insert a line in between setting the SQL and opening the recordset

qdf.[Me.Num]=Me.Num

Seems redundant I know but that's what's gotta be done. If you have more than one parameter you need to do this for each one.

 

[AccessAmateur]

Tried it:

With qdf
.SQL = "SELECT Name FROM List WHERE List.Num = " & Me.Num & " ;"

qdf.[Me.Num]=Me.Num

Set rstName = .OpenRecordset()

End With

also tried
With qdf
.SQL = "SELECT Name FROM List WHERE List.Num = " & Me.Num & " ;"
.[Me.Num]=Me.Num
Set rstName = .OpenRecordset()
End With
which should be the same thing.
I get "Compile error: Method or data member not found" in both cases.

 

[Rob.Mills]

Try this. Create a string variable to store the SQL statement.

Then set qdf.SQL=variable

It might eliminate the need for setting the parameter value.

 

[AccessAmateur]

Tried it.

Still getting the original 'Too few parameters' error.

You are right about it being something to do with the parameter. If I leave out the 'where' statement it doesn't give any error. (but I seem to get an empty recordset.)

 

[Rob.Mills]

Private Sub InspNum_DblClick(Cancel As Integer)

Dim dbs As Database
Dim rstName As Recordset
Dim qdf As QueryDef
Dim strSQL As String

strSQL="SELECT Name FROM List WHERE List.Num = " & Me.Num & ";"

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")
With qdf
.SQL = strSQL

Set rstName = .OpenRecordset

End With


I would think that this would not require a parameter as the string figures out the parameter first and then the SQL is set.

Do you did it this way?

 

[AccessAmateur]

That's exactly how I did it. and I'm still getting the error message.

 

[Rob.Mills]

Try making a parameter query and then calling that query in code.

Private Sub InspNum_DblClick(Cancel As Integer)

Dim dbs As Database
Dim rstName As Recordset
Dim qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("[Query Name]")
qdf.[Parameter]=Me.Num

Set rstName = .OpenRecordset

End With


Note: In the query itself you can't use Me.Num. Gotta use the full reference.

 

[AccessAmateur]

I was trying to avoid creating a permanent query. Aside from having the code creating multitudes of queries, it also gives problems with each time the script is run, it will again try to recreate a query & will upchuck on finding one already there.

I have also tried the method shown in the help files under 'Filter method' (recsetName.Filter & press F1) which leads back to pretty much the same code as what I had. What I really don't understand is that I'm following the example in the help files as close as possible but I'm stiil getting errors.

Next I guess I will try to bring in the recset without the Where clause then apply a filter to the recset.

 

[Rob.Mills]

One more thought... Trying using DAO. First make sure you have the DAO library referenced.

Then setup your variables with DAO in front like this:

Dim dbs As DAO.Database
Dim rstName As DAO.Recordset
Dim qdf As DAO.QueryDef

 

[AccessAmateur]

Got this to work. Thanks for all the help.

Set dbs = CurrentDb
Set recset = dbs.OpenRecordset( _
"SELECT Num, Name " & _
"FROM List ORDER BY Num", _
dbOpenSnapshot)
strSearch = "Num = ' " & Me.Num & " ' "
With recset
' Populate recordset.
.MoveLast
' Find first record satisfying search string. Exit
' loop if no such record exists.
.FindFirst strSearch
If .NoMatch Then
MsgBox "Error: Cannot find Number " & _
Me.Num & "in the List."
End If
strName = .Fields("Name")
End With

I knew ther must be a way, but why is it so well hidden!

End With