Recordset Problems

[karmahum]

Hello all. I am trying to determine if a query results in any records. Depending on the situtation (no records or mutiple records) I need to do different things. The query is based on a number of parameters found on an open form. Typically I open a recordset based the query to determine if there are any records:

Dim rssnap As Recordset

Set rssnap = CurrentDb.OpenRecordset("qryBenefit_Rates_2003", dbOpenDynaset)

However, this code results in a error indicating: "Too Few Paramaters. Expecting 7."

Any ideas what is wrong? Any ideas on a better method odf doing this.

Thank.

 

[Travis]

Put a Break Point on the Line:

Set rssnap = CurrentDb.OpenRecordset("qryBenefit_Rates_2003", dbOpenDynaset)


When it reaches this line of code. Goto the Database window and open the query "qryBenefit_Rates_2003". If you get the same error as you are discribing, then look at the query to see what parameters you are not setting that it believes it should have.

Without actually seeing the Query and the Open Form with the parameters its hard to give any additional advice, except to make sure that the Parameter form is open and that the Query opens under normal conditions.

 

[Alexandre]

The problem is likely to come from your query, not from the recordset. Probably you are in one of the following situations:
- Either this is a parameter query, and you don't provide parameters when opening it;
-Or the query refers to a varaible it cannot access at the time when you open the recordset (e: query using data in a form, ie criteria like: [Criteria]=Forms!FormName!controlName);
-Or there is a syntax problem leading access to expect a variable.

HTH

 

[Alexandre]

Bumped into Travis

 

[The_Doc_Man]

Part of the problem COULD be that the OpenRecordSet method isn't getting all the parameters it thinks it needs.

The number of parameters in an OpenRecordSet varies according to the type of recordset being opened. I would strongly suggest looking up the help files for OpenRecordSet to see the combinations of parameters needed for each situation, from opening a table-type recordset to opening up an ODBC recordset on a remote server somewhere in your network cloud.

 

[karmahum]

Thanks for the replies.

Yes, I had already done this and the query works correctly. I have had this problem before...Sometime it works and other times this problem occurs.

I know that the form that supplies the parameters to the query is open. It is odd that opening the query normally, not as a recordset, work just fine.

Ok. So, any other suggestions how to accomplish this?

Thanks again.

Sean

 

[Alexandre]

Did you make sure that the query can not be launched without the user having filled all required data in the form?

 

[karmahum]

Yes, the query parameters. The way the query is written, variations of 5 different variables are required. So the query will run as long as the form is open. The query run without problem each time, reagardless of the values on the form.

 

[Alexandre]

Let's see your SQL.

 

[karmahum]

Here is the SQL. It looks messer than it is. Basically, I have a table that contains 4 fields. Three of these fields are then used to limit the query results based on values in text boxes on a form.

SELECT tblBenefit_Rates_2003.*
FROM tblBenefit_Rates_2003
WHERE (((tblBenefit_Rates_2003.EMR)=[Forms]![frmPlan_Information]![txtEMR]) AND ((tblBenefit_Rates_2003.[Ben ID])=[Forms]![frmPlan_Information]![txtBenID]) AND ((tblBenefit_Rates_2003.[Plan ID])=[Forms]![frmPlan_Information]![txtPlanIDEE])) OR (((tblBenefit_Rates_2003.EMR)=[Forms]![frmPlan_Information]![txtEMR]) AND ((tblBenefit_Rates_2003.[Ben ID])=[Forms]![frmPlan_Information]![txtBenID]) AND ((tblBenefit_Rates_2003.[Plan ID])=[Forms]![frmPlan_Information]![txtPlanIDEE1])) OR (((tblBenefit_Rates_2003.EMR)=[Forms]![frmPlan_Information]![txtEMR]) AND ((tblBenefit_Rates_2003.[Ben ID])=[Forms]![frmPlan_Information]![txtBenID]) AND ((tblBenefit_Rates_2003.[Plan ID])=[Forms]![frmPlan_Information]![txtPlanIDEESP])) OR (((tblBenefit_Rates_2003.EMR)=[Forms]![frmPlan_Information]![txtEMR]) AND ((tblBenefit_Rates_2003.[Ben ID])=[Forms]![frmPlan_Information]![txtBenID]) AND ((tblBenefit_Rates_2003.[Plan ID])=[Forms]![frmPlan_Information]![txtPlanIDEECH])) OR (((tblBenefit_Rates_2003.EMR)=[Forms]![frmPlan_Information]![txtEMR]) AND ((tblBenefit_Rates_2003.[Ben ID])=[Forms]![frmPlan_Information]![txtBenID]) AND ((tblBenefit_Rates_2003.[Plan ID])=[Forms]![frmPlan_Information]![txtPlanIDFAM]));

Urgh.

 

[Alexandre]

Well... I asked for it didn't I?
Doesn't seems to be anything very particular about your Select query. I have two ideas left at the moment:
- You should trap errors related to null values by wrapping them into a Nz function (ex: tblBenefit_Rates_2003.EMR)=Nz([Forms]![frmPlan_Information]![txtEMR], Whatever irrealistic value)
So that the test will fail without error in case of a null value.
-Also, on your form, make sure that the last entered value is committed (updated) before launching the query (by setting the focus elswhere for ex.)

 

[karmahum]

Thanks for much for the reply. And thanks for the thought. I went and added the NZ error-trapping idea (I forgot to do that)...No luck.

Also, the form that feeds the queries is based on a table and rarely changed, so the data is refreshed and commited...No luck.

This problem has vexed me for awhile. I may just have to find another way.

Thanks again.

 

[Alexandre]

Well, now you managed to raise my curiousity.
If your db is not too big, zip and send it to me (or just the query and form in question) and I'll have a look.

 

[Jerry Stoner]

why not Dcount?

If DCount("*", "[MyQuery]") > 0 Then
Do Stuff
Else
Dother Stuff
End If

 

[Alexandre]

Now, that is funny.
I just came into the same problem with a query referrencing a control on an opened form. I solved it by using a string to hold the SQL and create the recordset.
strSQL = "..."
dbEngine(0)(0).OpenRecordSet(strSQL, dbOpenSnapshot)
(Beware with the syntax of the SQL string)

However, I'm sure I opened tons of recordsets based on stored queries of this kind in the past, without pb. I am not sure about what hapenned here, but I'll try to find out what made the difference.

 

[mission2java_78]

With something as large as that wouldn't a function be a more understandable/readable/decent solution?
Jon

 

[John.Woody]

I think Jon's on the right line. You're trying to run the query programically (is that made up?) by opening a new dbs workspace and running the query where it can't see the form. If you put your sql code in a function and run it from there you should have more luck i.e.

Dim dbs as database
Dim rsnap as recordset
Dim strsql as string

Set dbs = currentdb

strsql = "SELECT tblBenefit_Rates_2003.*
FROM tblBenefit_Rates_2003
WHERE (((tblBenefit_Rates_2003.EMR)=" & [Forms]!frmPlan_Information]![txtEMR] & ") ....etc

Set rst = dbs.OpenRecordset( strsql, dbOpenDynaset)


HTH
John

 

[karmahum]

Ok. Thanks for all the suggestions and idea. I decided to try the easier ones first...and actually got lucky. I was dreading having to move the logic into code. So I tried Jerry Stoner DCount Method and it appears to be working!!!

I will continue to test it, but for now it has resolved my problem.

Thanks again!