Second Recordset?

[Tim]

Hi folks,

I tried to select the field value from my first recordset
but I got an error message. Could anyone help to fix it?

dim dbs as database
dim rst1 as recordset
dim rst1 as recordset

set dbs =currentdb
set rst1=dbs.openrecordset ("Select distinct [Test ID],
[Test Value] from table3;")

set rst2=dbs.openrecordset ("select " & rst1.fields
("[Test ID]").value & " as f1 From " & rst1 & " Group
by " & rst1.fields("[Test ID]").value & " Having Count("
& rst1.fields("[Test ID]").value & ") >1;")

debug.print rst2.fields("f1").value

If rst1 contains no record, what happen to rst2 and how
to handle it?

Thanks in advance.

Tim.

 

[Rob]

Hi Tim,

Might be a silly response but should your variables be named rst1 and rst2?

Cheers
Rob

 

[Rob]

Hi (again) Tim,

Not an access wiz but you could try:

If rst1.Recordcount > 0 Then
Set rst2 = ....
End If

or

If Not rst1.EOF Then
Set rst2 = ....
End If

Cheers
Rob

 

[Tim]

Hi Rob,

I got two problem for my code.

The first one is the second recordset is not working.
The second one was answer by you.

Do you have idea of my first question?

Thanks a lots.

Tim.

-----Original Message-----
Hi (again) Tim,

Not an access wiz but you could try:

If rst1.Recordcount > 0 Then
Set rst2 = ....
End If

or

If Not rst1.EOF Then
Set rst2 = ....
End If

Cheers
Rob

Hi folks,

I tried to select the field value from my first recordset
but I got an error message. Could anyone help to fix it?

dim dbs as database
dim rst1 as recordset
dim rst1 as recordset

set dbs =currentdb
set rst1=dbs.openrecordset ("Select distinct [Test ID],
[Test Value] from table3;")

set rst2=dbs.openrecordset ("select " & rst1.fields
("[Test ID]").value & " as f1 From " & rst1 & " Group
by " & rst1.fields("[Test ID]").value & " Having Count ("
& rst1.fields("[Test ID]").value & ") >1;")

debug.print rst2.fields("f1").value

If rst1 contains no record, what happen to rst2 and how
to handle it?

Thanks in advance.

Tim.

.

 

[Rob]

Hi Tim,

Can you supply some sample data and your expected result in rst1 and rst2?

Cheers
Rob

Hi Rob,

I got two problem for my code.

The first one is the second recordset is not working.
The second one was answer by you.

Do you have idea of my first question?

Thanks a lots.

Tim.

-----Original Message-----
Hi (again) Tim,

Not an access wiz but you could try:

If rst1.Recordcount > 0 Then
Set rst2 = ....
End If

or

If Not rst1.EOF Then
Set rst2 = ....
End If

Cheers
Rob

Hi folks,

I tried to select the field value from my first recordset
but I got an error message. Could anyone help to fix it?

dim dbs as database
dim rst1 as recordset
dim rst1 as recordset

set dbs =currentdb
set rst1=dbs.openrecordset ("Select distinct [Test ID],
[Test Value] from table3;")

set rst2=dbs.openrecordset ("select " & rst1.fields
("[Test ID]").value & " as f1 From " & rst1 & " Group
by " & rst1.fields("[Test ID]").value & " Having Count ("
& rst1.fields("[Test ID]").value & ") >1;")

debug.print rst2.fields("f1").value

If rst1 contains no record, what happen to rst2 and how
to handle it?

Thanks in advance.

Tim.

.

 

[tina]

AFAIK, while a recordset in VBA can contain multiple records, you can only
interact with one record at a time. so
HAVING Count(TestID) > 1
will always ensure that Rst2 is empty, because you're never looking at more
than one record at a time from Rst1.
suggest you post an explanation of what you're actually trying to
accomplish. maybe somebody can suggest an alternate solution.

hth

 

[Tim]

Rob,

I want to run two queries. The original query will be
like following:

SELECT DISTINCT Table3.[Test ID],
Table3.[Test Value]
FROM Table3

Save that query as VT

Now, use that

SELECT [Test ID]
FROM VT
Group By [Test ID]
Having Count [Test Id] > 1

Thanks.

Tim.

-----Original Message-----
Hi Tim,

Can you supply some sample data and your expected result in rst1 and rst2?

Cheers
Rob

Hi Rob,

I got two problem for my code.

The first one is the second recordset is not working.
The second one was answer by you.

Do you have idea of my first question?

Thanks a lots.

Tim.

-----Original Message-----
Hi (again) Tim,

Not an access wiz but you could try:

If rst1.Recordcount > 0 Then
Set rst2 = ....
End If

or

If Not rst1.EOF Then
Set rst2 = ....
End If

Cheers
Rob

Hi folks,

I tried to select the field value from my first recordset
but I got an error message. Could anyone help to

fix
it?

dim dbs as database
dim rst1 as recordset
dim rst1 as recordset

set dbs =currentdb
set rst1=dbs.openrecordset ("Select distinct [Test ID],
[Test Value] from table3;")

set rst2=dbs.openrecordset ("select " & rst1.fields
("[Test ID]").value & " as f1 From " & rst1 & " Group
by " & rst1.fields("[Test ID]").value & " Having

Count
("

& rst1.fields("[Test ID]").value & ") >1;")

debug.print rst2.fields("f1").value

If rst1 contains no record, what happen to rst2 and how
to handle it?

Thanks in advance.

Tim.


.

.

 

[Rob]

Tim,

Can you provide some of the actual data that you are attempting to
manipulate - it will help with defingin a solution. Hve you tried creating
your query in design view. Use the first query as a subquery for the
second. If this is the case you can copy the SQL from the design view into
the module,

Cheers
Rob

 

[Wayne Morgan]

Tim,

It appears you have a typo in your Dim statements, rst1 is listed twice.
Also, I don't think you can run a query on a recordset in memory. For rst2
you will need to run the query on the source of rst1's recordset.

What error message did you receive?

 

[Tim]

Wayne,

You are right. I got a typo in my previous statement.

The following list all the info:

Table3:

Test ID Test Value
99 aa
99 aa
98 ab
98 ba
97 cc
96 aa
96 aa
96 cc
95 cc
95 cc
95 cc
1 c
1 a


Output:

f1
1
96
98

Module:

Public Sub test()
Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT DISTINCT [Test ID],
[Test Value] FROM Table3;")

Set rst2 = dbs.OpenRecordset("SELECT " & rst1.Fields
("[Test ID]").Value & " as f1 FROM " & rst1 & _
" GROUP BY " &
rst1.Fields("[Test ID]").Value & _
" HAVING Count(" &
rst1.Fields("[Test ID]").Value & ") > 1;")

Debug.Print rst2.Fields("f1").Value


End Sub


When I complied the sub, I got “Tpye mismatch”.

Any idea to fix it?

Thanks.

Tim.

-----Original Message-----
Tim,

It appears you have a typo in your Dim statements, rst1 is listed twice.
Also, I don't think you can run a query on a recordset in memory. For rst2
you will need to run the query on the source of rst1's recordset.

What error message did you receive?

--
Wayne Morgan
Microsoft Access MVP

Hi folks,

I tried to select the field value from my first recordset
but I got an error message. Could anyone help to fix it?

dim dbs as database
dim rst1 as recordset
dim rst1 as recordset

set dbs =currentdb
set rst1=dbs.openrecordset ("Select distinct [Test ID],
[Test Value] from table3;")

set rst2=dbs.openrecordset ("select " & rst1.fields
("[Test ID]").value & " as f1 From " & rst1 & " Group
by " & rst1.fields("[Test ID]").value & " Having Count("
& rst1.fields("[Test ID]").value & ") >1;")

debug.print rst2.fields("f1").value

If rst1 contains no record, what happen to rst2 and how
to handle it?

Thanks in advance.

Tim.

.

 

[Wayne Morgan]

That was the 2nd part of my previous message. I don't believe you can run a
query on a variable. To open rst2, run the query on the table or query that
rst1 is based on, not on rst1. You can use the values of the fields from
rst1, just remember that it will be the value for the current record, this
won't "scroll" down the recordset to each value in turn.

To open this recordset on rst1 you could make rst1 a querydef object. Save
the querydef then use the querydef in rst2. If the SQL of the querydef will
change, that's not a problem. Just leave a saved querydef in the database so
and change its SQL property as desired. Since the querydef is always there,
you'll know its name for use in rst2.

 

[Marshall Barton]

The following list all the info:

Table3:

Test ID Test Value
99 aa
99 aa
98 ab
98 ba
97 cc
96 aa
96 aa
96 cc
95 cc
95 cc
95 cc
1 c
1 a


Output:

f1
1
96
98

Module:

Public Sub test()
Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT DISTINCT [Test ID],
[Test Value] FROM Table3;")

Set rst2 = dbs.OpenRecordset("SELECT " & rst1.Fields
("[Test ID]").Value & " as f1 FROM " & rst1 & _
" GROUP BY " &
rst1.Fields("[Test ID]").Value & _
" HAVING Count(" &
rst1.Fields("[Test ID]").Value & ") > 1;")

Debug.Print rst2.Fields("f1").Value

I'm not sure I understand what you want here, but I
**think** you only need one query:

SELECT [Test ID]
FROM table3
GROUP BY [Test ID], [Test Value]
HAVING Count(*) > 1

 

[Tim]

Marshall,

My problem can only be resolved by two queries. The
second query select the record from the first query. I
tried the query you stated but the output was different
from my expected.

Thanks anyway.

Tim.

-----Original Message-----

The following list all the info:

Table3:

Test ID Test Value
99 aa
99 aa
98 ab
98 ba
97 cc
96 aa
96 aa
96 cc
95 cc
95 cc
95 cc
1 c
1 a


Output:

f1
1
96
98

Module:

Public Sub test()
Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT DISTINCT [Test ID],
[Test Value] FROM Table3;")

Set rst2 = dbs.OpenRecordset("SELECT " & rst1.Fields
("[Test ID]").Value & " as f1 FROM " & rst1 & _
" GROUP BY " &
rst1.Fields("[Test ID]").Value & _
" HAVING Count(" &
rst1.Fields("[Test ID]").Value & ") > 1;")

Debug.Print rst2.Fields("f1").Value

I'm not sure I understand what you want here, but I
**think** you only need one query:

SELECT [Test ID]
FROM table3
GROUP BY [Test ID], [Test Value]
HAVING Count(*) > 1

 

[Rob]

Tim,

Have you created the two queries in design view? You can then use the
QueryDef object to create a recordset?

Cheers
Rob

Marshall,

My problem can only be resolved by two queries. The
second query select the record from the first query. I
tried the query you stated but the output was different
from my expected.

Thanks anyway.

Tim.

-----Original Message-----

The following list all the info:

Table3:

Test ID Test Value
99 aa
99 aa
98 ab
98 ba
97 cc
96 aa
96 aa
96 cc
95 cc
95 cc
95 cc
1 c
1 a


Output:

f1
1
96
98

Module:

Public Sub test()
Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT DISTINCT [Test ID],
[Test Value] FROM Table3;")

Set rst2 = dbs.OpenRecordset("SELECT " & rst1.Fields
("[Test ID]").Value & " as f1 FROM " & rst1 & _
" GROUP BY " &
rst1.Fields("[Test ID]").Value & _
" HAVING Count(" &
rst1.Fields("[Test ID]").Value & ") > 1;")

Debug.Print rst2.Fields("f1").Value

I'm not sure I understand what you want here, but I
**think** you only need one query:

SELECT [Test ID]
FROM table3
GROUP BY [Test ID], [Test Value]
HAVING Count(*) > 1