How to code a query

[Rich7638]

I have a form built on query1. query2 contains records that are related to query1 via the query1 primary key. I want to put a button on the form that will retreive a record from query2 that matches the primary key value of query1 and has a "like" condition in another one of it's fields. Once I get this record, I'll clean it up and use it to open a file for the user to view. All of the button stuff and opening file stuff I can do, what I can't figure out how to do is code a query in VBA so that I can get the record and process it. RunSQL only works for action queries, OpenQuery actually opens the query and displays the data, neither of which are usefull to me. I have written a fair amount of code earlier in my career and I'm just now getting back into it using VB. I suspect that there is a simple answer but I sure can't find it.

Thanks
Rich

 

[longhair]

Rich7638,
try the following path...
put a parameter in your query for example [pQueryDetail]
then you can code like this:

Dim dbs As DAO.Database
Dim qdfDtl As DAO.QueryDef

txtPO = Me.Form.[po-num]

Set dbs = CurrentDb

Set qdfDtl = dbs.QueryDefs("GetDtl")
qdfDtl.Parameters("pQueryDtl") = txtPO
Set rstDtl = qdfDtl.OpenRecordset(dbOpenDynaset)
intRecCount = rstDtl.RecordCount
If intRecCount > 0 Then
'do something

rstDtl is delared public at the module level
hth
regards,
longhair

 

[Rich7638]

longhair, thanks for your response

I inserted your code and I'm not getting an error and the record count is greater than 0. So, how do I retreive the data now. If I use [PVDocs].[FILFilName] I get an error saying that it is not found in this collection. The query works because when I run it manually, and enter the parameter, I get the correct data.

I just realized that I haven't declared rstDtl at the module level yet, what is it declared as?

Rich

 

[Golom]

Try

PVDocs[COLOR=red][b]![/b][/color][FILFilName]

Assuming that PVDocs is the name of your recordset object.

The other possible problem is that [FILFilName] is not the name of a field in the recordset.

 

[longhair]

on my way out for the day - but will try quick:
Public rstDtl As DAO.Recordset
rstDtl actually holds the query results.
you can set them to a form, debug.print etc.
will try to answer more in the am.
regards,

longhair

 

[Rich7638]

longhair

I have it sorted out. txtFile = rstDtl!FIlFileName worked fine. Thank you for your help.

rich

 

[longhair]

Rich7638,
your welcome.
regards,
longhair