moving through a recordset

[gwilym40]

I have a table called functions where there is a grouping number field. Each record can have the same grouping number i.e. duplicates allowed

I have another table which holds the group - Arts for example may be number 10

the user chooses a group say "Arts" (from a combo box) and the str says:
select * from functions where [grouping] = 10
the table functions may have 10 records which have a grouping number of 10
These 10 records will have a distinct reference code.
I'd like to store these 10 records, in say a temporary table.

There is another table called receipts which I'd like to loop through the 10 records and find any records in the table receipts where the distinct reference code matches.
I'd then like to have a message box saying how many, if any records were found and if some were found have them in a continious form

Any help much appreciated
I hope I've made myself clear

Gwilym

 

[Ouch]

i think you need to query the query!

you have a query that returns the 10 records

create another query based on the first query and the reciepts table.

this should give you the results you need..

 

[gwilym40]

I'm not using a query though!?!

at the moment I've got

Dim db As Database
Dim rst, rst1 As Recordset
Dim str, str1 As String

Set db = CurrentDb
str = "SELECT * from functions where [grouping] = " & Me.Combo0.Column(0)
Set rst = db.OpenRecordset(str)
If Not rst.EOF Then
With rst
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
rst.MoveNext
Loop
Msgbox rst.RecordCount
End With

now this works, because it says, in my example there are 11 occasions where the grouping is 10 i.e. Arts

I now want to say, for each of those 11 records run this code and loop again

str1 = "SELECT * from receipts where [entrydate] = " & "#" & Me!entry_date & "# AND [MNo]=" & "'" & rst("RefNo&quot & "'"


Regards

 

[braindead2]

ouch is correct. You need to use a subquery
quick attempt below. You will need to edit the syntax but hopefully you get the idea.

str1 = "SELECT * from receipts where [entrydate] = " & "#" & Me!entry_date & "# AND [MNo]in
(SELECT refnn from functions where [grouping] = " & Me.Combo0.Column(0))

good luck