Dynamic Query to Form

[nhtraven]

Hi all

I have a form that allows the users to build a query, allowing up to 6 fields. I have another form that will display the results. My problem is it all works except i cannot get rid of the query datasheet with results. I have tried this code in a multitude of places

docmd.close acquery ("Dynamic_Query")

i would have thought that this in atleast the Form_CLose would have worked no dice.

Am i missing something or is the code above incorrect? any help would be appreciated

Raven

 

[KenReay]

Hi

Could you explain a little more about how you are actually doing this, to elaborate, if I were doing this I would take on of two approaches

A) have a saved query with criteria based the values entered to a form, use this saved query as the record source of the results form

or

b) build my query in code as an SQL string, pass this string to the results form to be used as the recordsource

either way I would not be running the query such that its results were displayed in the query grid datasheet style, which is waht you seem to be describing, as I say, could you explain a little more

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[nhtraven]

Ken

Sorry i was not clear. I have a find form that allows the user to enter information into 6 different fields, then using this code i build a dynamic query:

Private Sub cmdfind_Click()

'declarations
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

'set variable as current database

Set db = CurrentDb()

'delete exsisting DQ; traps error

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

'set variant where to null

where = Null

where = where & " AND [WSnum] like '" + Me![txtwsnum] + "'"
where = where & " AND [ponum] like '" + Me![txtponum] + "'"
where = where & " AND [compname] like '" + Me![txtcompany] + "'"
where = where & " AND [serialno] = " + Me![txtslnum]
where = where & " AND [rmanum] = " + Me![txtrmanum]


'message box to show the SQL statement for admin

MsgBox "Select * tblrma" & (" where " + Mid(where, 2))

Set QD = db.CreateQueryDef("Dynamic_Query", "SELECT * FROM tblrma " & ("WHERE " + Mid(where, 6)) & ";")
DoCmd.OpenQuery "Dynamic_Query"

End Sub



This all works and delivers the results in query datasheet. Like this:

rmanum serialno oldrmano
1011 23456789 1100006
1017 0
1018 0


I want it to go to a form. I have changed the docmd to about everything i could think of.

I hope this makes it a little clearer.

Thanks

 

[PHV]

Don't open the Query but a Form with RecordSource="Dynamic_Query"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884