Open another form based on the same records as in the first form

[Guest]

I have a form (Machine) that displays records from a table. When clicking a
command button, it opens another form (MachineList), and I want it to use the
same filter as I may have in the first form (Machine).

I have tried the following (in the OnOpen event of the MachineList form):

Me.Filter = Forms("Machine").Filter
If Forms("Machine").FilterOn = True Then Me.FilterOn = True

Problem is that the form Machine includes Combo boxes, and if I use the
filter on those, The MachineList form prompts me for the value of a field
(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList based on the
records currently filtered in Machine - only specified by the primary
index-field: "MachineID" - because this field is the same for both forms.

So if I use a filter in the Machine form, which result in three records with
the MachineID values of 1080047, 1080048 and 1080049, I would like the
MachineList form to use the following filter: [MachineID] = 1080047 OR
1080048 or 1080049.

It sounds simple, but how do I do?

 

[Ken Snell [MVP]]

I'm not understanding your comment about having used the "filter" on your
combo boxes in the "machine" form? Are you applying a filter to the form
that also filters these combo boxes?

You could use the WHERE argument of the DoCmd.OpenForm method to carry the
first form's filter to the second form:

DoCmd.OpenForm "MachineList", , , Me.Filter

 

[Guest]

If I use a filter in the Machine form on a numeric field, it works fine when
I open the MachineList form. But if I use it on a combo box that looks up
another value, the same filter won't work in my MachineList form.

I don't want to open the MachineList form with the same filter, I just want
to open it with ONLY the records where MachineID are as in the Machine form.

Example: If I filter the field "Width" in the Machine form to 1000, it will
result in only 3 records, and their MachineID is: 1080047, 1080048 and
1080049.
Now, when I open the MachineList form, I want it to display only the records
where MachineID is 1080047, 1080048 or 1080049, I.E. the same 3 records!

Hope you understand now! Thanks for any help!

I'm not understanding your comment about having used the "filter" on your
combo boxes in the "machine" form? Are you applying a filter to the form
that also filters these combo boxes?

You could use the WHERE argument of the DoCmd.OpenForm method to carry the
first form's filter to the second form:

DoCmd.OpenForm "MachineList", , , Me.Filter

--

Ken Snell
<MS ACCESS MVP>

I have a form (Machine) that displays records from a table. When clicking a
command button, it opens another form (MachineList), and I want it to use the
same filter as I may have in the first form (Machine).

I have tried the following (in the OnOpen event of the MachineList form):

Me.Filter = Forms("Machine").Filter
If Forms("Machine").FilterOn = True Then Me.FilterOn = True

Problem is that the form Machine includes Combo boxes, and if I use the
filter on those, The MachineList form prompts me for the value of a field
(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList based on the
records currently filtered in Machine - only specified by the primary
index-field: "MachineID" - because this field is the same for both forms.

So if I use a filter in the Machine form, which result in three records with
the MachineID values of 1080047, 1080048 and 1080049, I would like the
MachineList form to use the following filter: [MachineID] = 1080047 OR
1080048 or 1080049.

It sounds simple, but how do I do?

 

[Ken Snell [MVP]]

You can build the "WHERE" filter via code before you open the second form.

Dim strFilter As String
strFilter = ""
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
strFilter = strFilter & "MachineID=" & _
.Fields("MachineID").Value & " Or "
.MoveNext
Loop
End With
strFilter = Left(strFilter, Len(strFilter) - 4)
DoCmd.OpenForm "MachineList", , , strFilter

--

Ken Snell
<MS ACCESS MVP>

If I use a filter in the Machine form on a numeric field, it works fine when
I open the MachineList form. But if I use it on a combo box that looks up
another value, the same filter won't work in my MachineList form.

I don't want to open the MachineList form with the same filter, I just want
to open it with ONLY the records where MachineID are as in the Machine form.

Example: If I filter the field "Width" in the Machine form to 1000, it will
result in only 3 records, and their MachineID is: 1080047, 1080048 and
1080049.
Now, when I open the MachineList form, I want it to display only the records
where MachineID is 1080047, 1080048 or 1080049, I.E. the same 3 records!

Hope you understand now! Thanks for any help!

I'm not understanding your comment about having used the "filter" on your
combo boxes in the "machine" form? Are you applying a filter to the form
that also filters these combo boxes?

You could use the WHERE argument of the DoCmd.OpenForm method to carry the
first form's filter to the second form:

DoCmd.OpenForm "MachineList", , , Me.Filter

--

Ken Snell
<MS ACCESS MVP>

I have a form (Machine) that displays records from a table. When

clicking
a

command button, it opens another form (MachineList), and I want it to

use
the

same filter as I may have in the first form (Machine).

I have tried the following (in the OnOpen event of the MachineList form):

Me.Filter = Forms("Machine").Filter
If Forms("Machine").FilterOn = True Then Me.FilterOn = True

Problem is that the form Machine includes Combo boxes, and if I use the
filter on those, The MachineList form prompts me for the value of a field
(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList based

on
the

records currently filtered in Machine - only specified by the primary
index-field: "MachineID" - because this field is the same for both forms.

So if I use a filter in the Machine form, which result in three

records
with

the MachineID values of 1080047, 1080048 and 1080049, I would like the
MachineList form to use the following filter: [MachineID] = 1080047 OR
1080048 or 1080049.

It sounds simple, but how do I do?

 

[Guest]

Thanx for the help! This code worked perfectly!

You can build the "WHERE" filter via code before you open the second form.

Dim strFilter As String
strFilter = ""
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
strFilter = strFilter & "MachineID=" & _
.Fields("MachineID").Value & " Or "
.MoveNext
Loop
End With
strFilter = Left(strFilter, Len(strFilter) - 4)
DoCmd.OpenForm "MachineList", , , strFilter

--

Ken Snell
<MS ACCESS MVP>

If I use a filter in the Machine form on a numeric field, it works fine when
I open the MachineList form. But if I use it on a combo box that looks up
another value, the same filter won't work in my MachineList form.

I don't want to open the MachineList form with the same filter, I just want
to open it with ONLY the records where MachineID are as in the Machine form.

Example: If I filter the field "Width" in the Machine form to 1000, it will
result in only 3 records, and their MachineID is: 1080047, 1080048 and
1080049.
Now, when I open the MachineList form, I want it to display only the records
where MachineID is 1080047, 1080048 or 1080049, I.E. the same 3 records!

Hope you understand now! Thanks for any help!

I'm not understanding your comment about having used the "filter" on your
combo boxes in the "machine" form? Are you applying a filter to the form
that also filters these combo boxes?

You could use the WHERE argument of the DoCmd.OpenForm method to carry the
first form's filter to the second form:

DoCmd.OpenForm "MachineList", , , Me.Filter

--

Ken Snell
<MS ACCESS MVP>


I have a form (Machine) that displays records from a table. When clicking
a
command button, it opens another form (MachineList), and I want it to use
the
same filter as I may have in the first form (Machine).

I have tried the following (in the OnOpen event of the MachineList form):

Me.Filter = Forms("Machine").Filter
If Forms("Machine").FilterOn = True Then Me.FilterOn = True

Problem is that the form Machine includes Combo boxes, and if I use the
filter on those, The MachineList form prompts me for the value of a field
(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList based on
the
records currently filtered in Machine - only specified by the primary
index-field: "MachineID" - because this field is the same for both forms.

So if I use a filter in the Machine form, which result in three records
with
the MachineID values of 1080047, 1080048 and 1080049, I would like the
MachineList form to use the following filter: [MachineID] = 1080047 OR
1080048 or 1080049.

It sounds simple, but how do I do?

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thanx for the help! This code worked perfectly!

You can build the "WHERE" filter via code before you open the second form.

Dim strFilter As String
strFilter = ""
With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
strFilter = strFilter & "MachineID=" & _
.Fields("MachineID").Value & " Or "
.MoveNext
Loop
End With
strFilter = Left(strFilter, Len(strFilter) - 4)
DoCmd.OpenForm "MachineList", , , strFilter

--

Ken Snell
<MS ACCESS MVP>

If I use a filter in the Machine form on a numeric field, it works

fine
when

I open the MachineList form. But if I use it on a combo box that looks up
another value, the same filter won't work in my MachineList form.

I don't want to open the MachineList form with the same filter, I just want
to open it with ONLY the records where MachineID are as in the Machine form.

Example: If I filter the field "Width" in the Machine form to 1000, it will
result in only 3 records, and their MachineID is: 1080047, 1080048 and
1080049.
Now, when I open the MachineList form, I want it to display only the records
where MachineID is 1080047, 1080048 or 1080049, I.E. the same 3 records!

Hope you understand now! Thanks for any help!

:

I'm not understanding your comment about having used the "filter" on your
combo boxes in the "machine" form? Are you applying a filter to the form
that also filters these combo boxes?

You could use the WHERE argument of the DoCmd.OpenForm method to

carry
the

first form's filter to the second form:

DoCmd.OpenForm "MachineList", , , Me.Filter

--

Ken Snell
<MS ACCESS MVP>


I have a form (Machine) that displays records from a table. When clicking
a
command button, it opens another form (MachineList), and I want it

to
use

the
same filter as I may have in the first form (Machine).

I have tried the following (in the OnOpen event of the MachineList form):

Me.Filter = Forms("Machine").Filter
If Forms("Machine").FilterOn = True Then Me.FilterOn = True

Problem is that the form Machine includes Combo boxes, and if I

use
the

filter on those, The MachineList form prompts me for the value of

a
field

(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList

based
on

the
records currently filtered in Machine - only specified by the primary
index-field: "MachineID" - because this field is the same for both forms.

So if I use a filter in the Machine form, which result in three records
with
the MachineID values of 1080047, 1080048 and 1080049, I would like the
MachineList form to use the following filter: [MachineID] = 1080047 OR
1080048 or 1080049.

It sounds simple, but how do I do?