Recordset Program

[Dinger_80]

I am currently working on enhancing my database at work. I am trying to open up a recordset. The problem is that I get an error that says too few parameters expected. I am using a query as the recordset and it does return the one record that I want it to return. The recordset though doesn't seem to be able to see the record. If anyone can help it would be appreciated. The code up to the recordset is fine and works. The error occurs when I open up the recordset.

Dim db As DAO.Database
Dim rs As Recordset

Me.TestRequestCombo.SetFocus
If Me.TestRequestCombo.Text = "" Then
    MsgBox "You have not made a valid selection. Please select a valid Test Request Number."
    Exit Sub
Else
    Set db = CurrentDb
    Set rs = db.OpenRecordset("FullTestRequestRetrievalQuery")
        rs.MoveFirst
         some more code
        rs.Close

 

[MarkK]

This is the error you get when your query contains fieldname(s) that don't exist in the table. Check your spellings.

Any text that the query parser can't identify as a keyword or a table or a field, it assumes to be a parameter. Then when values aren't supplied for said parameter, you get a a "too few parameters" error when the query is run.

 

[Dinger_80]

MarkK,
All my field names are fields in the tables that make up the query. Now not all of the fields have a value in them when the query is run. That is to be expected for some records while others will be fully populated. As I said the query does return a single record as I want it to do, just the recordset doesn't see the record. Does it matter if all the fields have a value in them?

 

[MarkK]

Your query contains an unrecognized identifier, maybe a function call to a function you deleted, or a reference to a form that is closed, but not only that, you are totally free to disagree with my assessment.
All the best,

 

[Dinger_80]

No not disagreeing with you, just trying to be informative. I pull the record based on the primary key of my main table. The rest of the query is just fields from related tables so as to aquire the needed data. I tried to make it a simple select query.

 

[Dinger_80]

So I put the SQL statement as a SQL string in my vba area. I then ran a debug.print.
I copied the print into a new query in the SQL Section. This is how it looks

SELECT TestRequestTable.TestRequestNumber, CrouseHindsPersonalTable.FullName, TestRequestTable.IndividualComponent, ComponentTable.ID, ComponentTable.ComponentType, ComponentTable.ComponentManufacturer, ComponentTable.CompetitorComponent, CatalogNumberTable.ProductName, CatalogNumberTable.ProductLine, CatalogNumberTable.Manufacturer, CatalogNumberTable.CompetitorProduct, TestRequestTable.WitnessTesting, WitnessTestingTable.WitnessAgency, WitnessTestingTable.FileNumber, TestRequestTable.TestLocation, TestRequestTable.TestCatagory1, IntervalsTable.IntervalsModule, CrouseHindsPersonalTable.LoginID, CrouseHindsPersonalTable.Position, CrouseHindsPersonalTable.AccessLevel, TestRequestTable.FolderHyperLink, TestingProcedureTable.LabProcedureNumber FROM ((((CrouseHindsPersonalTable INNER JOIN ((TestRequestTable INNER JOIN WitnessTestingTable ON TestRequestTable.ProjectNumber = WitnessTestingTable.ID) INNER JOIN IntervalsTable ON TestRequestTable.IntervalsNumber = IntervalsTable.ID) ON CrouseHindsPersonalTable.ID = T
estRequestTable.Requestor) INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID) INNER JOIN FacilityLocations ON TestRequestTable.TestLocation = FacilityLocations.ID) INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![TestRequestNavigationForm]![TestRequestCombo]));

At this point it wont execute because of

((((CrouseHindsPersonalTable INNER JOIN ((TestRequestTable INNER JOIN WitnessTestingTable ON TestRequestTable.ProjectNumber = WitnessTestingTable.ID) INNER JOIN IntervalsTable ON TestRequestTable.IntervalsNumber = IntervalsTable.ID) ON CrouseHindsPersonalTable.ID = T
estRequestTable.Requestor) INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID) INNER JOIN FacilityLocations ON TestRequestTable.TestLocation = FacilityLocations.ID) INNER JOIN TestingProcedureTable ON

The Field TestRequestTable.Requestor is broken into two lines like T
estRequestTable.Requestor. If I join these two back together the query does execute. However when I try to use the OpenRecordset(strsql) it still finds too few parameters. I don't know how to make it read the TestRequestTable.Requestor as a whole. Any advice?

 

[jdraw]

Please post the sql for your query and a picture of your relationships windows showing all tables expanded.

OOoops: You posted while I was writing.


When the query did execute, did it return your expected results?
If this is a saved query, you could open the querydef SQL, edit it to ensure all names are contiguous, then resave.

Your SQL seems OK

SELECT 
TestRequestTable.TestRequestNumber
, CrouseHindsPersonalTable.FullName
, TestRequestTable.IndividualComponent
, ComponentTable.ID
, ComponentTable.ComponentType
, ComponentTable.ComponentManufacturer
, ComponentTable.CompetitorComponent
, CatalogNumberTable.ProductName
, CatalogNumberTable.ProductLine
, CatalogNumberTable.Manufacturer
, CatalogNumberTable.CompetitorProduct
, TestRequestTable.WitnessTesting
, WitnessTestingTable.WitnessAgency
, WitnessTestingTable.FileNumber
, TestRequestTable.TestLocation
, TestRequestTable.TestCatagory1
, IntervalsTable.IntervalsModule
, CrouseHindsPersonalTable.LoginID
, CrouseHindsPersonalTable.Position
, CrouseHindsPersonalTable.AccessLevel
, TestRequestTable.FolderHyperLink
, TestingProcedureTable.LabProcedureNumber 
FROM
 (
  ( 
   (
     (CrouseHindsPersonalTable INNER JOIN 
       (
        (TestRequestTable INNER JOIN WitnessTestingTable ON TestRequestTable.ProjectNumber = WitnessTestingTable.ID
        ) INNER JOIN 
         IntervalsTable ON TestRequestTable.IntervalsNumber = IntervalsTable.ID
       ) ON CrouseHindsPersonalTable.ID = TestRequestTable.Requestor
     ) INNER JOIN 
       ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID
    ) INNER JOIN FacilityLocations ON TestRequestTable.TestLocation = FacilityLocations.ID
   ) INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID
  ) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID 
 WHERE 
  (
   (
    (TestRequestTable.TestRequestNumber)=[Forms]![TestRequestNavigationForm]![TestRequestCombo]
   )
  );

Is the Form TestRequestNavigationForm open?

Can you show us the fields in each of your table definitions?

 

[Dinger_80]

When I went to run the query the first time no it didn't return the desired results. I looked things over and noticed that the one field had its name split apart. When I fixed that it worked perfectly. It is a saved query. The form TestRequestNavigation stays open till I close it at the end. I am not so sure I know what you are talking about with regards to the querydef SQL. That isn't something I am familiar with. I am including a screen shot of tables and the field names.

 

[spikepl]

Parametrized queries in VBA are dealt with here:

http://access.mvps.org/access/queries/qry0013.htm

 

[Dinger_80]

Thank you very much spikepl. That was the information that I needed and the recordset worked great.