Why it deletes my query?

[Jen]

Hi,

I have a simple database and I have a form which selecting criteria from
drop down list and display the result in subform.
First time the form is opened, it display result fine. But when I close the
form and reopen it, it says "Query must have at least one destination field."

The query is
SELECT Product.*
FROM Product
WHERE Product.Owner = Forms!Product!OwnerList;

OwnerList is List of Owner in dropdown menu and it is set "Select Name" as
default.
Once name is selected and the field updated, macro requery the above query.
I know I can use OpenQuery macro, but I would like to display in a form the
drop-down menu and result.

Is there any better way to do it?
Does requery macro delete the query?

Thank you in advance.
Jen

 

[jan kowalski]

Hi,

I have a simple database and I have a form which selecting criteria from
drop down list and display the result in subform.
First time the form is opened, it display result fine. But when I close
the
form and reopen it, it says "Query must have at least one destination
field."

The query is
SELECT Product.*
FROM Product
WHERE Product.Owner = Forms!Product!OwnerList;

OwnerList is List of Owner in dropdown menu and it is set "Select Name" as
default.
Once name is selected and the field updated, macro requery the above
query.
I know I can use OpenQuery macro, but I would like to display in a form
the
drop-down menu and result.

Is there any better way to do it?
Does requery macro delete the query?

Thank you in advance.
Jen

 

[Jeanette Cunningham]

Hi Jen,
Are you setting the record source of the subform on the after update event
for the combo?

Me.OwnerList_AfterUpdate()
Dim strSQL As String

If Len(Me.OwnerList ) > 0 Then
strSQL = " SELECT Product.* " _
& " FROM Product " _
& "WHERE Product.Owner = " & Me.OwnerList & ""
Debug.Print strSQL
Me.SubformControlName.Form.RecordSource = strSQL
End If
End Sub

The above is untested air code.
Replace SubformControlName with the actual name of the subform control
(which may be different from the subform inside it).
It assumes that the parent form is called frmProduct
There is a combo called OwnerList on the main form.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia