Open form to records matching the records in another query?

[gojets1721]

Basically the title...any suggestions for code to open a form to only the records that match an ID field of records in another query? Which isn't the form's record source?

 

[ebs17]

Do what you say.
RecordSource:

SELECT
   *
FROM
   TableX
WHERE
   ID IN
      (
         SELECT
            ID
         FROM
            AnotherQuery
      )
'-------------------------------------------------------
SELECT 
   TableX.* 
FROM 
   TableX 
      INNER JOIN AnotherQuery 
      ON TableX.ID = AnotherQuery.ID

 

[gojets1721]

Do what you say.
RecordSource:

SELECT
   *
FROM
   TableX
WHERE
   ID IN
      (
         SELECT
            ID
         FROM
            AnotherQuery
      )
'-------------------------------------------------------
SELECT
   TableX.*
FROM
   TableX
      INNER JOIN AnotherQuery
      ON TableX.ID = AnotherQuery.ID

Gotcha. So I want to open the form from a command (sorry; I should have mentioned that). How do I build this into a DoCmd.OpenForm code?

 

[ebs17]

Which isn't the form's record source?

Are you just talking about a form's RecordSource property, or are you able to use that properly as well?

You can also derive a filter for an OpenForm from the first suggestion.

 

[Pat Hartman]

Gotcha. So I want to open the form from a command (sorry; I should have mentioned that). How do I build this into a DoCmd.OpenForm code?

Are you aware of intellisense? - it helps you to build code as you type
Are you aware of the Access help entries? - they describe the parts of the OpenForm method.

Both will help you to include a where clause as part of the OpenForm method.

Your question is still very imprecise. It is really hard to give specific advice to a non-specific question. If the button is on the form that is bound to a record that contains the ID of the record you want to open in the second form, that is one condition.

In general:

DoCmd.OpenForm "yourformname", , , "where condition", , acDialog

Specifically to open the form to an ID you have access to on the current form:

DoCmd.OpenForm "yourformname", , , "SomeIDField =" & Me.SomeIDField, , acDialog

 

[gojets1721]

Are you just talking about a form's RecordSource property, or are you able to use that properly as well?

You can also derive a filter for an OpenForm from the first suggestion.

So I have a form of complaints (frmComplaints). The form's record source is tblComplaints.

I have a large inner join query whose records have a field of ComplaintID (qryX).

I want to use a command to open frmComplaints and also filter it to any records whose ComplaintID matches the ComplaintID found in any records in qryX.

I hope this clarifies a bit.

 

[ebs17]

"ID IN (SELECT ID FROM qryX)"

This is a usable WHERE condition, as you can see above.

It seems that you would starve just by putting bread in your hand. You have to be fed.

 

[arnelgp]

I want to use a command to open frmComplaints and also filter it to any records whose ComplaintID matches the ComplaintID found in any records in qryX.

you can use qryX as your Filter when you open the frmComplaints form:

DoCmd.OpenForm FormName:="frmComplaints", View:=acFormDS, FilterName:="qryX"

 

[gojets1721]

This is a usable WHERE condition, as you can see above.

It seems that you would starve just by putting bread in your hand. You have to be fed.

I'm very much a beginner with Access/VBA. I'm sorry

 

[ebs17]

Open form

DoCmd.OpenForm method (Access)

Office VBA reference topic

learn.microsoft.com

If you want to learn, reading helps, even before being asked to do so by third parties. The method does not have such an unusual name now, which would make it difficult to find.

 

[Pat Hartman]

I want to use a command to open frmComplaints and also filter it to any records whose ComplaintID matches the ComplaintID found in any records in qryX.

I gave you the sample code back in #5. Please try it.

 

[arnelgp]

this is a demo that demonstrate what i am saying in post #8.
you can Filter your form using qryX.
Open forrm1 and see qryX.

 

[gojets1721]

This is a usable WHERE condition, as you can see above.

It seems that you would starve just by putting bread in your hand. You have to be fed.

This worked. I appreciate all the help