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

A

Anders Berlin

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?
 
K

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
 
A

Anders Berlin

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!

Ken Snell said:
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>


Anders Berlin said:
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?
 
K

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>


Anders Berlin said:
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!

Ken Snell said:
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?
 
A

Anders Berlin

Thanx for the help! This code worked perfectly!

Ken Snell said:
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>


Anders Berlin said:
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!

Ken Snell said:
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?
 
K

Ken Snell [MVP]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Anders Berlin said:
Thanx for the help! This code worked perfectly!

Ken Snell said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top