help with this query? Please!

[Guest]

I have a form which shows records in datasheet view, I would like to select
any record I want from the form, double click on it and have another form
open showing only the record I selected. I created the two forms, but I don’t
know how the query should be structured. Could someone please help me with
this? Thank you kindly.

 

[SusanV]

Instead of datasheet view, use a continuous form, and put the open form code
in the onDoubleClick event of the control..

 

[Guest]

You need a query that your second form is based on and your query needs a
field that matches one of the fields on your main form. In the criteria
build a pointer to the field in your main form.

Build a command button that opens your second form whose control source is
the second query with the criteria specifying the current record that you
have highlighted

 

[Guest]

You can either base the second form on a query which references the key of
the first form, so its RecordSource would be something like:

SELECT *
FROM MyTable
WHERE MyID = Forms!Form1!MyID;

or you can leave the second form's RecordSource unrestricted and filter the
form when you open it by means of the WhereCondition argument of the OpenForm
method.

For the first method add a function to the first form's module such as:

Function OpenForm2()

DoCmd.OpenForm "Form2"

End Function

For the second amend the function to:

Function OpenForm2()

DoCmd.OpenForm "Form2", WhereCondition:="MyID = " & MyID

End Function

This assumes that the 'MyID' key is a number data type. If the key is a
text data type field then you'd wrap the value in quotes:

DoCmd.OpenForm "Form2", WhereCondition:="MyID = """ & MyID & """"


Doing this in datasheet view you'd need to set the On Dbl Click event
property of the each control bound to a field to:

=OpenForm2()

If you used continuous form view, however, you could overlay all controls
with a transparent command button and set its On Dbl Click event property to:

=OpenForm2()

Ken Sheridan
Stafford, England