Querying a query

[George J]

Not sure if this is possible, but worth a try.

I have a report that i am printing. The report has a recordsource of qryVP which filters so that only the selcted records are printed.

This works okay, but i am wanting to extract the contents from field adDesc (in the query) if paddID from the report = pandID in the query. There may be more than one record in the query with the same pandID which i would put into a textbox - each adDesc on a new line.

I hope this makes sense.
Can anyone assist with ideas?

Edit: Here's what i have so far - creating a new query and getting the required data. But how do i get the result to show as required in a textbox?

Set db = CurrentDb
    
    strSQL = "SELECT adDesc FROM qryVP Where pandID=" & Me.paddID
    Set qry = db.CreateQueryDef("qryVP2", strSQL)
    Set rst = qry.OpenRecordset()    
    Me.txtGJ = rst
    db.QueryDefs.Delete ("qryVP2")

thanks
George

 

[Sketchin]

Spelling error?
Where pandID=" & Me.paddID

 

[pbaldy]

That is a valiant effort, but wrong.

Try something like this:

  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT adDesc FROM qryVP Where pandID=" & Me.paddID
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF
    Me.txtGJ = Me.txtGJ & rs!adDesc & vbCrLf
    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

 

[George J]

You mean you can tell i've not done much with Access before?

You might be able to save me from another beginners mistake then. Short of counting the number of records, then dividing the length of the text in txtGJ by this - how can i stop the text from repeating?

example: There are 3 records which the code correctly reads, but instead of getting:
adDesc1
adDesc2
adDesc3

The result is :
adDesc1
adDesc2
adDesc3

adDesc1
adDesc2
adDesc3

adDesc1
adDesc2
adDesc3

I take it this is because there are 3 records with the appropriate pandID. Is there a 'proper' way of dealing with this, or is my old berserker style of count and slash okay?

And thank you for resolving my query before i went totally mad... probably.

 

[pbaldy]

If the query only has 3 matching records, the code should only read those 3. Can you post the db here?

 

[George J]

Unfortunately no
It's a work DB with comfy data already in it.

I'll try to mock up something similar.

 

[pbaldy]

You might also try this technique to make sure the SQL is correct:

http://www.baldyweb.com/ImmediateWindow.htm

 

[George J]

I'll test the SQL now - thanks (It worked fine, then debug.print me.txtGJ was okay.)

Just had a horrible nasty thought. The data in the description part is from a continuous form - would that create any issues?

 

[pbaldy]

Don't think so. The SQL is getting data from the table using whatever record has focus on the form.

 

[George J]

Strange and unusual - almost describes me that does.

I kept the txtGJ in the detail section and moved the address, date & comments to the footer; squished the txtGJ to basically no height and made it invisible. Added another textbox to the footer and maded txtGJ2 = txtGJ in the detail code. Look like that has done it - i'll need to check through it to make sure nothing is spooling into the wrong 'record' on the printout, but it looks like that's done it - or i'm too tired to tell any more.

Many thanks for your assistance and patience. Really appreciated - i'll pop back if there's any problems

 

[pbaldy]

No problem.