ADO Help 2

[bdavis96]

The following is what I am trying to accomplish:

I am using ADO to connect to a Query, but I only need a subset of the Query for the Recordset. My understanding of how to do that is:

rst.Open "Select * From Query WHERE Query.Field = Data;, conn ...

I have another recordset that will be providing the data:

rst.Open "Select * From Query WHERE Query.Field = rstOther!Field;, conn ...

I keep getting the error of too few arguements, so I am lost in how to do this.

Any help would be much appreciated.

 

[mmilan]

Look at either filters or using your additional criteria in the where clause of the first run of the query...

mmilan

 

[HarleyQuinn]

Hi,

You could add the cursortype and locktype to the end...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[bdavis96]

What kind of additional criteria in the WHERE clause? Filters have crossed my mine, but I want to see if I can use this way first.

 

[HarleyQuinn]

Are you trying to pull data from the first recordset named rst and then trying to include them in the query to open the second recordset (rst) using the criteria from the first still open recordset????

Harleyquinn

---------------------------------
For tsunami relief donations

 

[bdavis96]

This is my actual code:

Public PC As ADODB.Connection
Public PC_FMAPRS As ADODB.Recordset
Public PC_FMIMPR As ADODB.Recordset


Set PC = New ADODB.Connection
Set PC_FMAPRS = New ADODB.Recordset
Set PC_FMIMPR = New ADODB.Recordset

PC.Open "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=C:\Progra~1\DB.mdb;"
PC_FMAPRS.Open "Select * From FMAPRS", PC, adOpenDynamic, adLockOptimistic
PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = PC_FMAPRS![APORA#]", PC, adOpenDynamic, adLockOptimistic

FMAPRS & FMIMPR are queries in DB.mdb. I am using Queries instead of Tables for the sorting.

 

[HarleyQuinn]

Where does the error occur???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[bdavis96]

Missed one step, after PC_FMAPRS.Open, I do PC_FMAPRS.MoveFirst so I know the recordset pointer is on the first record. There are 5 records in PC_FMAPRS and 15 records in PC_FMIMPR (3 records per record in PC_FMAPRS), so when PC_FMIMPR.Open, the recordset should see 3 records.

 

[Sheco]

You can put more than one criteria in you WHERE clause like this:

SELECT field1, field2, field3
FROM myTable
WHERE
(field1 = this) AND
(field2 = that) AND
(field3 = theOther)


maybe I didn't understand the question

 

[bdavis96]

Error occurs on the PC_FMIMPR.Open statement:

Run-time Error -2147217904

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

 

[Sheco]

Oh the problem is that PC_FMAPRS is the name of your local recordset variable so it doesnt belong in your WHERE clause like that.

 

[bdavis96]

PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = PC_FMAPRS![APORA#]", PC, adOpenDynamic, adLockOptimistic

I just want to compare the one field to the one field in PC_FMAPRS. So the PC_FMIMPR recordset should contain 3 of 15 records, and have access to all the fields of those 3 records.

 

[Sheco]

A better syntax would be:

"Select * From FMIMPR WHERE FMIMPR.[IPORA#] = FMIMPR.[APORA#]"

But I have a strong feeling that this is NOT what you intend to happen... so although the syntax is better, problems remain.

Do you want to loop through the first recordset and use its field values to create the second recordset?

 

[bdavis96]

There are two open recordsets at the same time PC_FMAPRS and PC_FMIMPR, each based on its respective Query FMAPRS and FMIMPR. I have even tried Value = PC_FMAPRS![APORA#] then PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = Value;" ... but that doesn't seem to work either.

 

[Sheco]

So you want something more like:

PC_FMAPRS.Open "Select * From FMAPRS", PC, adOpenDynamic, adLockOptimistic

Do While Not PC_FMAPRS.EOF
  PC_FMIMPR.Open "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = " & PC_FMAPRS![APORA#], PC, adOpenDynamic, adLockOptimistic

  '<< Do something with the 2nd recordset here>>

  PC_FMAPRS.MoveNext
Loop

 

[bdavis96]

Recordset PC_FMAPRS based on FMAPRS will have it's pointer on the first record (really doesn't matter what record), so PC_FMAPRS![APORA#] = 00101103. I want PC_FMIMPR based on FMIMPR to open up with only those records where PC_FMIMPR![IPORA#] = 00101103. So I am not understanding why PC_FMIMPR.Open = "Select * From FMIMPR WHERE FMIMPR.[IPORA#] = PC_FMAPRS![APORA#] isn't working. I do have all current updates on VB6 and Reference ADO 2.8.

 

[Sheco]

The problem happens when you pass the SQL string to the .Open() method of the recordset object.

This object is just sending the string to the database to get the results.

The database knows nothing of the existance of your other variables.

That is why it doesn't recognise it when you refer to it as PC_FMAPRS![APORA#] or as Value

You have to build the SQL string so that it is correct BEFORE it goes to the recordset object... don't put the variable name inside the SQL string.

 

[bdavis96]

Almost Sheco, I am not looping through PC_FMAPRS

 

[HarleyQuinn]

Hi,

I have noticed from the previous couple of posts that no-one is using single quotes in the criteria for the where clause...

Harleyquinn

---------------------------------
For tsunami relief donations

 

[bdavis96]

By moving PC_FMAPRS![APORA#] outside the string, it gives me the error:

Data type mismatch in criteria expression on that same line.