Main for with two subforms

[GLT]

Hi,

I have a main form with a sub form and the two are linked by Child and
Master fields. The main form has a list of workstations, and the sub form
has a history of errors that have occurred on that workstation. So, when you
select a particular workstation, the related errors show up in the related
sub form - all works good.

Now, I want to add a new sub form which contains a vertical list of
workstations. The idea being that when you select a workstation from the new
(unlinked sub form), the main form and its linked sub form display that
workstation.

Can anyone advise how to achieve this?

Cheers,
GLT.

 

[Jeanette Cunningham]

Usually, in access, we do this by putting a dropdown in the header of the
main form.
The dropdown (unbound), contains the list of workstations.
User selects a workstation from the dropdown and code in the after update
event for the dropdown moves the main form to that workstation.
The subform is automatically filtered to that workstation through the link
master and link child fields.

Sample code for the after update of the combo can be found here
http://www.allenbrowne.com//ser-03.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[GLT]

Hi Jeanette,

Thanks for your reply, but I am using a subform to list out workstations (ie
continuous forms) as there is other data displayed with each workstation and
this data is colured (red + green). The idea being that you scroll down this
list and can action items that appear in red, so a combo box is not ideal for
this. To action an item, you select the workstation and then the mainform
jumps to that workstation.

Going by this code from Allen Brownes website I tried this:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = " & Me.RecID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Forms![Get all Details].setfocus
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

 

[Jeanette Cunningham]

Doing it by getting the main form to jump to the record shown in the subform
is tricky.
Allen has some code here to filter a form by a value in a subform.

http://www.allenbrowne.com//ser-28.html

If I wanted to edit an item chosen in a subform, I would open another form
to allow edits of the selected record and hide the main form and subform
until the edit was saved in the opened edit form.
Then in the unload event of that opened edit form I would requery the hidden
main form and subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Thanks for your reply, but I am using a subform to list out workstations
(ie
continuous forms) as there is other data displayed with each workstation
and
this data is colured (red + green). The idea being that you scroll down
this
list and can action items that appear in red, so a combo box is not ideal
for
this. To action an item, you select the workstation and then the mainform
jumps to that workstation.

Going by this code from Allen Brownes website I tried this:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = " & Me.RecID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Forms![Get all Details].setfocus
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

-------------------------------------------

But the above does not work ... any ideas?

Usually, in access, we do this by putting a dropdown in the header of the
main form.
The dropdown (unbound), contains the list of workstations.
User selects a workstation from the dropdown and code in the after update
event for the dropdown moves the main form to that workstation.
The subform is automatically filtered to that workstation through the
link
master and link child fields.

Sample code for the after update of the combo can be found here
http://www.allenbrowne.com//ser-03.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




.

 

[GLT]

Hi Jeanette,

I like your original idea to use the recordset.clone command which is what
I'm still trying to get working. I get the following error when I try to
run the VB code below:

Data type mismatch in criteria expression. (Error 3464)

On this line:

rs.FindFirst "[RecID] = " & Me.RecID

Why would I be getting this when both Subform and Mainform have access to
exactly the same RecID field?

Doing it by getting the main form to jump to the record shown in the subform
is tricky.
Allen has some code here to filter a form by a value in a subform.

http://www.allenbrowne.com//ser-28.html

If I wanted to edit an item chosen in a subform, I would open another form
to allow edits of the selected record and hide the main form and subform
until the edit was saved in the opened edit form.
Then in the unload event of that opened edit form I would requery the hidden
main form and subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Thanks for your reply, but I am using a subform to list out workstations
(ie
continuous forms) as there is other data displayed with each workstation
and
this data is colured (red + green). The idea being that you scroll down
this
list and can action items that appear in red, so a combo box is not ideal
for
this. To action an item, you select the workstation and then the mainform
jumps to that workstation.

Going by this code from Allen Brownes website I tried this:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = " & Me.RecID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Forms![Get all Details].setfocus
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

-------------------------------------------

But the above does not work ... any ideas?

Usually, in access, we do this by putting a dropdown in the header of the
main form.
The dropdown (unbound), contains the list of workstations.
User selects a workstation from the dropdown and code in the after update
event for the dropdown moves the main form to that workstation.
The subform is automatically filtered to that workstation through the
link
master and link child fields.

Sample code for the after update of the combo can be found here
http://www.allenbrowne.com//ser-03.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have a main form with a sub form and the two are linked by Child and
Master fields. The main form has a list of workstations, and the sub
form
has a history of errors that have occurred on that workstation. So,
when
you
select a particular workstation, the related errors show up in the
related
sub form - all works good.

Now, I want to add a new sub form which contains a vertical list of
workstations. The idea being that when you select a workstation from
the
new
(unlinked sub form), the main form and its linked sub form display that
workstation.

Can anyone advise how to achieve this?

Cheers,
GLT.


.

.

 

[GLT]

Hi Jeanette,

My code now looks like this, I think I have fixed the previous error:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone

MsgBox rs![RecID]

rs.FindFirst "[RecID] = '" & Me.RecID & "'"

If rs.NoMatch Then
'Do Nothing
Else
'Display the found record in the form.
Me.Parent.[ServerList].Bookmark = rst.Bookmark 'reposition the
form
Forms!frmMain![Get all Details].Requery
End If
Set rs = Nothing

End Sub

However the 'me.parent.subformname.bookmark = rst.bookmark' is not working...

Hi Jeanette,

I like your original idea to use the recordset.clone command which is what
I'm still trying to get working. I get the following error when I try to
run the VB code below:

Data type mismatch in criteria expression. (Error 3464)

On this line:

rs.FindFirst "[RecID] = " & Me.RecID

Why would I be getting this when both Subform and Mainform have access to
exactly the same RecID field?

Doing it by getting the main form to jump to the record shown in the subform
is tricky.
Allen has some code here to filter a form by a value in a subform.

http://www.allenbrowne.com//ser-28.html

If I wanted to edit an item chosen in a subform, I would open another form
to allow edits of the selected record and hide the main form and subform
until the edit was saved in the opened edit form.
Then in the unload event of that opened edit form I would requery the hidden
main form and subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Thanks for your reply, but I am using a subform to list out workstations
(ie
continuous forms) as there is other data displayed with each workstation
and
this data is colured (red + green). The idea being that you scroll down
this
list and can action items that appear in red, so a combo box is not ideal
for
this. To action an item, you select the workstation and then the mainform
jumps to that workstation.

Going by this code from Allen Brownes website I tried this:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = " & Me.RecID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Forms![Get all Details].setfocus
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

-------------------------------------------

But the above does not work ... any ideas?
:

Usually, in access, we do this by putting a dropdown in the header of the
main form.
The dropdown (unbound), contains the list of workstations.
User selects a workstation from the dropdown and code in the after update
event for the dropdown moves the main form to that workstation.
The subform is automatically filtered to that workstation through the
link
master and link child fields.

Sample code for the after update of the combo can be found here
http://www.allenbrowne.com//ser-03.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have a main form with a sub form and the two are linked by Child and
Master fields. The main form has a list of workstations, and the sub
form
has a history of errors that have occurred on that workstation. So,
when
you
select a particular workstation, the related errors show up in the
related
sub form - all works good.

Now, I want to add a new sub form which contains a vertical list of
workstations. The idea being that when you select a workstation from
the
new
(unlinked sub form), the main form and its linked sub form display that
workstation.

Can anyone advise how to achieve this?

Cheers,
GLT.


.

.

 

[Jeanette Cunningham]

Yes,
You are using the recordset clone in the form where the code is running .
When you set the bookmark , access is expecting you to set the bookmark in
the same form.

'Display the found record in the form.
Me.Bookmark = rst.Bookmark 'reposition the form

However your code seems to be trying to set the bookmark in a different form
'Display the found record in the form.
Me.Parent.[ServerList].Bookmark = rst.Bookmark 'reposition the
form

So, if you want to set the bookmark, run that routine below in the form
where you want to set the bookmarkk.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

My code now looks like this, I think I have fixed the previous error:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone

MsgBox rs![RecID]

rs.FindFirst "[RecID] = '" & Me.RecID & "'"

If rs.NoMatch Then
'Do Nothing
Else
'Display the found record in the form.
Me.Parent.[ServerList].Bookmark = rst.Bookmark 'reposition the
form
Forms!frmMain![Get all Details].Requery
End If
Set rs = Nothing

End Sub

However the 'me.parent.subformname.bookmark = rst.bookmark' is not
working...

Hi Jeanette,

I like your original idea to use the recordset.clone command which is
what
I'm still trying to get working. I get the following error when I try
to
run the VB code below:

Data type mismatch in criteria expression. (Error 3464)

On this line:

rs.FindFirst "[RecID] = " & Me.RecID

Why would I be getting this when both Subform and Mainform have access to
exactly the same RecID field?

Doing it by getting the main form to jump to the record shown in the
subform
is tricky.
Allen has some code here to filter a form by a value in a subform.

http://www.allenbrowne.com//ser-28.html

If I wanted to edit an item chosen in a subform, I would open another
form
to allow edits of the selected record and hide the main form and
subform
until the edit was saved in the opened edit form.
Then in the unload event of that opened edit form I would requery the
hidden
main form and subform.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette,

Thanks for your reply, but I am using a subform to list out
workstations
(ie
continuous forms) as there is other data displayed with each
workstation
and
this data is colured (red + green). The idea being that you scroll
down
this
list and can action items that appear in red, so a combo box is not
ideal
for
this. To action an item, you select the workstation and then the
mainform
jumps to that workstation.

Going by this code from Allen Brownes website I tried this:

Sub Form_Click()
Dim rs As DAO.Recordset
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[RecID] = " & Me.RecID
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Forms![Get all Details].setfocus
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing

End Sub

-------------------------------------------

But the above does not work ... any ideas?
:

Usually, in access, we do this by putting a dropdown in the header
of the
main form.
The dropdown (unbound), contains the list of workstations.
User selects a workstation from the dropdown and code in the after
update
event for the dropdown moves the main form to that workstation.
The subform is automatically filtered to that workstation through
the
link
master and link child fields.

Sample code for the after update of the combo can be found here
http://www.allenbrowne.com//ser-03.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

I have a main form with a sub form and the two are linked by Child
and
Master fields. The main form has a list of workstations, and the
sub
form
has a history of errors that have occurred on that workstation.
So,
when
you
select a particular workstation, the related errors show up in the
related
sub form - all works good.

Now, I want to add a new sub form which contains a vertical list
of
workstations. The idea being that when you select a workstation
from
the
new
(unlinked sub form), the main form and its linked sub form display
that
workstation.

Can anyone advise how to achieve this?

Cheers,
GLT.


.



.

 

[KenSheridan via AccessMonster.com]

You can do this quite easily with very little code in fact. Base the main
form on a query which references the primary key of the unlinked workstations
subform, e.g.

SELECT *
FROM WorkStations
WHERE WorkStationID =
Forms!frmWorkStations!sfcWorkStations.Form!WorkStationID OR Forms!
frmWorkStations!sfcWorkStations.Form!WorkStationID IS NULL;

where sfcWorkStations is the name of the subform control in the parent form
frmWorkStations and WorkStationID is the primary key.

In the Current event procedure of the workstations subform requery the parent
form with:

Me.Parent.Requery

When the parent form is opened it will include all workstation records and be
open at the first record in the sort order. Once a row is selected in the
subform the parent form will be requeried to the selected workstation record
only. The linked errors subform will be requeried automatically of course.
If you navigate to an empty new row in the workstations subform the parent
form will be requeried to all workstation records again by virtue of the
subform's WorkStationID being Null.

Ken Sheridan
Stafford, England

 

[Jeanette Cunningham]

Ken
that's an interesting way to do it.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[GLT]

Thanks Jeanette,

I modified my code and it works great. Thanks Ken for your response as well.

Cheers,
GT.

 

[KenSheridan via AccessMonster.com]

Jeanette:

It can also be extended a little further by including an unbound 'Show all'
check box in the parent form, and amending its RecordSource:

SELECT *
FROM WorkStations
WHERE WorkStationID =
Forms!frmWorkStations!sfcWorkStations.Form!WorkStationID OR
Forms!frmWorkStations!sfcWorkStations.Form!WorkStationID IS NULL
OR Forms!frmWorkStations!ckkShowAll = TRUE;

That way you use the check box to toggle between the workstation currently
selected in the subform and all workstations without the need to move to a
blank new record in the subform. The subform's AllowAdditions property can
therefore be False, which is better as the list box is really being used as a
list box on steroids here. The parent form is requeried (twice - more about
this below!) in the check box's AfterUpdate event procedure with:

Me.Requery
Me.Requery

If you want the parent form to be restricted to the selected record when the
user navigates to a record in the subform regardless of whether the check box
is True or False the subform's Current event procedure becomes:

Me.Parent.chkShowAll = False
Me.Parent.Requery
Me.Parent.Requery

I've no idea why its necessary to requery the parent form twice, but I've
found that if the check box is checked to show all records in the parent form,
then you navigate to a different record in the parent form, and then select a
record via the subform, or if you try to return to the previously selected
record by unchecking the check box, the parent record is not restricted to
the selected record but moves to its first record. Its as though its being
half-requeried! It makes no sense to me, but the double requerying seems to
cure it.

You could of course use a toggle button instead of the check box if preferred.


Ken Sheridan
Stafford, England

Ken
that's an interesting way to do it.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can do this quite easily with very little code in fact. Base the main
form on a query which references the primary key of the unlinked

[quoted text clipped - 50 lines]

 

[Jeanette Cunningham]

Thanks Ken,
that's handy to know.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette:

It can also be extended a little further by including an unbound 'Show
all'
check box in the parent form, and amending its RecordSource:

SELECT *
FROM WorkStations
WHERE WorkStationID =
Forms!frmWorkStations!sfcWorkStations.Form!WorkStationID OR
Forms!frmWorkStations!sfcWorkStations.Form!WorkStationID IS NULL
OR Forms!frmWorkStations!ckkShowAll = TRUE;

That way you use the check box to toggle between the workstation currently
selected in the subform and all workstations without the need to move to a
blank new record in the subform. The subform's AllowAdditions property
can
therefore be False, which is better as the list box is really being used
as a
list box on steroids here. The parent form is requeried (twice - more
about
this below!) in the check box's AfterUpdate event procedure with:

Me.Requery
Me.Requery

If you want the parent form to be restricted to the selected record when
the
user navigates to a record in the subform regardless of whether the check
box
is True or False the subform's Current event procedure becomes:

Me.Parent.chkShowAll = False
Me.Parent.Requery
Me.Parent.Requery

I've no idea why its necessary to requery the parent form twice, but I've
found that if the check box is checked to show all records in the parent
form,
then you navigate to a different record in the parent form, and then
select a
record via the subform, or if you try to return to the previously selected
record by unchecking the check box, the parent record is not restricted to
the selected record but moves to its first record. Its as though its
being
half-requeried! It makes no sense to me, but the double requerying seems
to
cure it.

You could of course use a toggle button instead of the check box if
preferred.


Ken Sheridan
Stafford, England

Ken
that's an interesting way to do it.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

You can do this quite easily with very little code in fact. Base the
main
form on a query which references the primary key of the unlinked

[quoted text clipped - 50 lines]

Cheers,
GLT.