Recordset too few parameters

[Dinger_80]

The other day my database started having some problems. I have two copies. One on the server that only gets changed when I have a fair amount of updates to be added to that copy. I also have a copy on my desk top that I tinker with. They both have the same back end. The problem is that the one on the server threw an error 3061 too few parameters. Expected 1. Now this is code that I have been using several months if not longer. I haven't had one problem with it until last Friday. I started to try and figure out where the error was occurring and traced it to a specific line on my copy on my desk top. So I have two files that suddenly started generating problems at the same time. That is a little weird to me. That being said, the problem is that I am trying to open a recordset (instead of multiple Dlookups) to get the information I need from a query. When I run the query while the form is open, it does return the information I am looking for. Thusly, when I do debug.print and do a dlookup, I also get a desired return in the immediate window. What I don't get is why then do I get too few parameters when clearly, there are parameters. Here is the code below. Any advice would be appreciated.

Dim FSO As Object
Dim ToPath As String
Dim FolderName As String
Dim FromPath As String
Dim TRNYear As String
Dim Destination As String
Dim Hyper As String
Dim ToPathCheck As String
Dim MsgStr As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

FolderName = Me.TRNumberCombo.Column(1)
TRNYear = Left(FolderName, 4)
           
    Set db = CurrentDb
    Debug.Print DLookup("InitialServerDestination", "TestLocationQuery")
    Set rs = db.OpenRecordset("TestLocationQuery")
        rs.MoveFirst
        FromPath = rs!InitialServerDestination & FolderName
        Destination = rs!CompletedServerDestination
        ToPath = rs!CompletedServerDestination & "\" & TRNYear & "\" & FolderName
    rs.Close
Following code .....
End Sub

 

[spikepl]

What I don't get is why then do I get too few parameters when clearly, there are parameters.

I don't get why people dismiss the messages from Access! It is telling you what it sees, and you cannot just say "No that is not so", because Access does not care in the least.

Access is telling you it is not getting what it is expecting, so that is the case. If your stuff worked for a long time but now stopped executing, that implies a change in data. Check the data - see if there are some unexepected nulls, or how the new records used to feed the query deviate from the old data.

If that doesn't solve your problem then show the SQL of the query.

 

[Dinger_80]

spikepl,

I don't disagree that I shouldn't dismiss what Access is saying. However, when I select the specific record on the form and run the same query against the record it works. Even the debug print returns results. So I am confused at how I can get information from the query, but that the open recordset doesn't see anything. All the same here is the sql for the query TestLocationQuery

SELECT TestRequestTable.TestRequestNumber, FacilityLocations.InitialServerDestination, FacilityLocations.InitialServerHyperlink, FacilityLocations.CompletedServerDestination, FacilityLocations.CompletedServerHyperlink, FacilityLocations.LabScheduleDestination, FacilityLocations.LabScheduleHyperlink, TestRequestTable.ProjectNumber
FROM FacilityLocations INNER JOIN TestRequestTable ON FacilityLocations.ID = TestRequestTable.TestLocation
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));

 

[robslob]

I believe you will need to use a parameterised query using QueryDef for this to work in a recordset. Add the following.

Dim qd As DAO.QueryDef

Set qd = db.QueryDefs("TestLocationQuery")
qd("[Forms]![LabScheduleForm]![TRNumberCombo]") = [Forms]![LabScheduleForm]![TRNumberCombo]
Set rs = qd.OpenRecordset

Remove this line
Set rs = db.OpenRecordset("TestLocationQuery")

I think that will do it, if not the mods will whip me.

 

[pbaldy]

Access can't resolve the form reference:

https://support.microsoft.com/en-us/kb/209203

 

[Dinger_80]

robslob,
That fixed things perfectly. Thank you for the help with that.

pbaldy,
That article will take me a little bit to digest. Thank you for directing it to me.

Here is the code with the corrections.

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

FolderName = Me.TRNumberCombo.Column(1)
TRNYear = Left(FolderName, 4)
           
    Set db = CurrentDb
    Set qd = db.QueryDefs("TestLocationQuery")
        qd("[Forms]![LabScheduleForm]![TRNumberCombo]") = [Forms]![LabScheduleForm]![TRNumberCombo]
    Set rs = qd.OpenRecordset
        rs.MoveFirst
        FromPath = rs!InitialServerDestination & FolderName
        Destination = rs!CompletedServerDestination
        ToPath = rs!CompletedServerDestination & "\" & TRNYear & "\" & FolderName
    rs.Close