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?
Click to expand...
Click to expand...
 
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
Click to expand...
clicking
a
command button, it opens another form (MachineList), and I want it to
Click to expand...
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
Click to expand...
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
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
use
the
filter on those, The MachineList form prompts me for the value of
Click to expand...
a
field
(for instance Lookup_Make.Name).

So what I really would like to do is open the form MachineList
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

DLookup Help 4
Autonumber field in Excel that behaves the same as Access 0
Filter main form to records with no related records in subform,maintain updateable recordset 4
filtering a form based on a filter on an open form 1
Remove sort and filters programmatically on form 2
Filtering and Removing Filter based on a combo box 3
dropdown to filter records 1
Filtering records based on field on a form 3

Top