Q: open form filtered by several values?

[Jen]

In a forms recordsource (which is a query) there can be many values. For
instance 4, 19 and 32. In this form I cycle through the values normally in
form view, they appear in a textbox one at a time when going right and left
klicking the arrows at the bottom.

On this form I also have a CommandButton. This button opens a second form
filtered by the value in the textbox.

Works in a way, that is, the second form opens filtered to the record that
was in the textbox on the first form when I clicked the Commandbutton.

However I need to open the second form filtered to all the records on the
first form, not only the one showing at the time of clicking the
CommandButton. That is, the second form should open filtered showing 3
records (records 4, 19 and 32) so that I could cycle through the same
records in the same way on the second form as in the first form.

The value being filtered in the second form is the second forms only unique
value coming from that forms underlying tables primary key.

No matter what I try, the second form opens filtered to one record only.

Anyone? Jen.

 

[Albert D. Kallal]

How is your first form behind filtered?

You can certainly open the 2nd form using the 'filer' clause (and not the
where) clause.


Your code behind the button to launch the 2nd form could be:

me.refresh
docmd.OpenForm "second form",,me.filter


I also suggest you make that 2nd form model.

 

[Jen]

Hello Albert. I don't filter the first form in any way. Behind the button
there is:

Private Sub cmd_bladdra_sena_Click()
On Error GoTo Err_cmd_bladdra_sena_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fakturainmatning"

stLinkCriteria = "[fakturanr]=" & Me![fakturanr]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmd_bladdra_sena_Click:
Exit Sub

Err_cmd_bladdra_sena_Click:
MsgBox err.Description
Resume Exit_cmd_bladdra_sena_Click

End Sub

Jen.

 

[John Vinson]

However I need to open the second form filtered to all the records on the
first form, not only the one showing at the time of clicking the
CommandButton. That is, the second form should open filtered showing 3
records (records 4, 19 and 32) so that I could cycle through the same
records in the same way on the second form as in the first form.

The second form should not reference the first form's controls at all.
Remember, the form doesn't contain any information! It's JUST A
WINDOW, viewing the data in the Table.

You will need to construct the WhereCondition or Filter clause of the
openform event for the second form from the first form's query
criteria. Since you didn't post any information about the nature of
either form's recordsource it's hard to say just what you need to do;
would you care to post the SQL of the query and the code that you're
using?

 

[Jen]

Hello John. Thanks for giving this a thought, I really need help on this.

The query/ recordsource of the first form is:

SELECT fakturor.fakturanr, qry_kritisk.kritisk
FROM qry_kritisk INNER JOIN fakturor ON qry_kritisk.fakturanr =
fakturor.fakturanr
WHERE (((qry_kritisk.kritisk)<Date()));

...and the value(s) qry_kritisk.fakturanr is the values that the second form
should be filtered to show when clicking a commandbutton opening the second
form.

Jen.

 

[Jen]

Hello John. Thanks for giving this a thought, I really need help on this.

The query/ recordsource of the first form is:

SELECT fakturor.fakturanr, qry_kritisk.kritisk
FROM qry_kritisk INNER JOIN fakturor ON qry_kritisk.fakturanr =
fakturor.fakturanr
WHERE (((qry_kritisk.kritisk)<Date()));

...and the value(s) qry_kritisk.fakturanr is the values that the second form
should be filtered to show when clicking a commandbutton opening the second
form.

Jen.

 

[John Vinson]

SELECT fakturor.fakturanr, qry_kritisk.kritisk
FROM qry_kritisk INNER JOIN fakturor ON qry_kritisk.fakturanr =
fakturor.fakturanr
WHERE (((qry_kritisk.kritisk)<Date()));

..and the value(s) qry_kritisk.fakturanr is the values that the second form
should be filtered to show when clicking a commandbutton opening the second
form.

I'd suggest basing the second form on a Query joinng the above query -
let's call it QueryX, since I don't know its name - to the second
form's current recordsource query; or use a criterion in the
WhereCondition such as

strWhere = "[Faduranr] IN (SELECT Fakturanr FROM QueryX)"
DoCmd.OpenForm "<second form>", WhereCondition:=strWhere

 

[Jen]

I'd suggest basing the second form on a Query joinng the above query -

let's call it QueryX, since I don't know its name - to the second
form's current recordsource query; or use a criterion in the
WhereCondition such as

strWhere = "[Faduranr] IN (SELECT Fakturanr FROM QueryX)"
DoCmd.OpenForm "<second form>", WhereCondition:=strWhere

Got it. Thanks a million times John!