Req: Filtering forms and subforms.

[Dave]

Hi All,

I hope you can help me here, I'm a little confuzzled.

All my forms are based on data sheets.

I have a main form (workstations) with a sub-form (workstation assets).

I have a button on the main form which loads another form with a view of all the workstation assets (everything in the database). This allows me to filter the assets. I have a button on this form -
'Jump to asset' which closes the form and moves the station view to the station that contains 'that' asset - and positions it on the row that contains the asset.

All well and good - most of the time.

But if the asset belongs to the station that was already 'in view' on the subform - the sub-form 'current' event doesn't re-position my .FindFirst to the correct row.

I hope I'm making myself clear here.

I know it has to be something to do with the filter jumping to the current station - and assets, but I'm unsure how to kick it to re-look at the record set already loaded.

Apologies if this is unclear, but I've spent ages trying to word this so it's understandable. I hope it's clear to someone.

Many thanks,
Dave

 

[strive4peace]

Hi Dave,

trying to understand better what you are doing ...

main form -- assets
subform -- workstation assets

button on main form opens another form showing detail and closes the
main form

"But if the asset belongs to the station that was already 'in view' on
the subform - the sub-form 'current' event doesn't re-position my
..FindFirst to the correct row."

what form does this statement apply to? the form you have just opened?
Can you describe the form that is opened?

Warm Regards,
Crystal

*
have an awesome day
*

 

[Dave]

Hi Crystal,

My apologies for being vague - I realised that I wouldn't explain it properly.

Basically on my main station form I have a subform for the assets for each station. I want to be able to filter all the assets so I have a popup form which lists all the assets and lets me filter
them.

The main form is still open underneath my 'filtering' form by the way, this 'filtering' form has a button - 'Jump to asset' which, closes the filter form and returns to the main form. Hopefully
re-locating me to the asset I have chosen during my filter.

I have code which re-positions to the correct station and then jumps to the row in the asset subform which contains the asset selected from the 'filtering' form.

Hope it's clear so far.

For the main this all works well and I'm pleased with it, but there is one condition which has me stumped...

My problem is this. Say I'm looking through the stations and I'm on station 'Stock Room', this has 200 assets and I'm looking for a keyboard by serial no. - it's quicker to bring up the filter form
and filter on part of the serial no. It may or may not be in the 'Stock Room' - I'm not sure. However, if I then jump to asset and that asset 'is' in the Stock Room - ie. the same station I was
looking at on the main form then no events trigger because the station hasn't changed.

IE. My changing of the station is based on code in the Form_Activate event of the main form, and jumping to the asset in the subform is based on code in the Form_Current event of the subform. If there
isn't a change to the station then no events trigger in the subform because well, nothing has changed. If the station doesn't change then no events at all seem to trigger on the subform.

So consequently I'm left pointing at the same station and asset record I was before I loaded my filter form up.

I do hope this helps you.

Thank you very much for replying and your patience.

Kind regards,
Dave

 

[strive4peace]

Hi Dave,

If I am understanding this correctly...

it seems that your popup filter form needs to position the record on the
main subform when it closes -- actually, I would put the code on the
*Unload* event since the current record will still be active

'~~~~~~~~~~~~~~
' use WITH so we do not have to repeat the form reference
With forms!mainformname!subform_controlname.form

' save record of popup form if changes have been made
' -- IF applicable
if me.dirty then
me.dirty = false
'show changes on main subform
.requery
end if

' if you are not on a current record, don't do anything
if me.newRecord then exit sub

' find the first value that matches in the main subform
.RecordsetClone.FindFirst "[SomeID] = " & me.RecordID

' if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

WHERE
SomeID is the fieldname of the Workstation Asset ID
RecordID is the field or controlname on the popup form that corresponds
to the Workstation Asset ID
mainformname is the name of the mainform
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Dave]

Hi Crystal,

That'll do the trick nicely.

I'd never have thought of doing it in the 'unload' event, but it makes perfect sense. So many ways of doing things.

Nice work - many thanks.

Warm regards,
Dave

Hi Dave,

If I am understanding this correctly...

it seems that your popup filter form needs to position the record on the
main subform when it closes -- actually, I would put the code on the
*Unload* event since the current record will still be active

'~~~~~~~~~~~~~~
' use WITH so we do not have to repeat the form reference
With forms!mainformname!subform_controlname.form

' save record of popup form if changes have been made
' -- IF applicable
if me.dirty then
me.dirty = false
'show changes on main subform
.requery
end if

' if you are not on a current record, don't do anything
if me.newRecord then exit sub

' find the first value that matches in the main subform
.RecordsetClone.FindFirst "[SomeID] = " & me.RecordID

' if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

WHERE
SomeID is the fieldname of the Workstation Asset ID
RecordID is the field or controlname on the popup form that corresponds
to the Workstation Asset ID
mainformname is the name of the mainform
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

My apologies for being vague - I realised that I wouldn't explain it properly.

Basically on my main station form I have a subform for the assets for each station. I want to be able to filter all the assets so I have a popup form which lists all the assets and lets me filter
them.

The main form is still open underneath my 'filtering' form by the way, this 'filtering' form has a button - 'Jump to asset' which, closes the filter form and returns to the main form. Hopefully
re-locating me to the asset I have chosen during my filter.

I have code which re-positions to the correct station and then jumps to the row in the asset subform which contains the asset selected from the 'filtering' form.

Hope it's clear so far.

For the main this all works well and I'm pleased with it, but there is one condition which has me stumped...

My problem is this. Say I'm looking through the stations and I'm on station 'Stock Room', this has 200 assets and I'm looking for a keyboard by serial no. - it's quicker to bring up the filter form
and filter on part of the serial no. It may or may not be in the 'Stock Room' - I'm not sure. However, if I then jump to asset and that asset 'is' in the Stock Room - ie. the same station I was
looking at on the main form then no events trigger because the station hasn't changed.

IE. My changing of the station is based on code in the Form_Activate event of the main form, and jumping to the asset in the subform is based on code in the Form_Current event of the subform. If there
isn't a change to the station then no events trigger in the subform because well, nothing has changed. If the station doesn't change then no events at all seem to trigger on the subform.

So consequently I'm left pointing at the same station and asset record I was before I loaded my filter form up.

I do hope this helps you.

Thank you very much for replying and your patience.

Kind regards,
Dave

 

[Dave]

Hi Crystal,

What are you !!?

You're a star !

I've just finished implementing the changes and it all works great. That's the first time I've changed an underlying forms' recordsource whilst being 'above it' (so to speak) ie. while unloading
another form. It works great.

The actual error only manifested itself occasionally, but it irrated me. I have a warm feeling now watching it work properly.

Tommorow I'm gonna sit down and read through your tutorial.

Many thanks,
Dave

Hi Dave,

If I am understanding this correctly...

it seems that your popup filter form needs to position the record on the
main subform when it closes -- actually, I would put the code on the
*Unload* event since the current record will still be active

'~~~~~~~~~~~~~~
' use WITH so we do not have to repeat the form reference
With forms!mainformname!subform_controlname.form

' save record of popup form if changes have been made
' -- IF applicable
if me.dirty then
me.dirty = false
'show changes on main subform
.requery
end if

' if you are not on a current record, don't do anything
if me.newRecord then exit sub

' find the first value that matches in the main subform
.RecordsetClone.FindFirst "[SomeID] = " & me.RecordID

' if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

WHERE
SomeID is the fieldname of the Workstation Asset ID
RecordID is the field or controlname on the popup form that corresponds
to the Workstation Asset ID
mainformname is the name of the mainform
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

My apologies for being vague - I realised that I wouldn't explain it properly.

Basically on my main station form I have a subform for the assets for each station. I want to be able to filter all the assets so I have a popup form which lists all the assets and lets me filter
them.

The main form is still open underneath my 'filtering' form by the way, this 'filtering' form has a button - 'Jump to asset' which, closes the filter form and returns to the main form. Hopefully
re-locating me to the asset I have chosen during my filter.

I have code which re-positions to the correct station and then jumps to the row in the asset subform which contains the asset selected from the 'filtering' form.

Hope it's clear so far.

For the main this all works well and I'm pleased with it, but there is one condition which has me stumped...

My problem is this. Say I'm looking through the stations and I'm on station 'Stock Room', this has 200 assets and I'm looking for a keyboard by serial no. - it's quicker to bring up the filter form
and filter on part of the serial no. It may or may not be in the 'Stock Room' - I'm not sure. However, if I then jump to asset and that asset 'is' in the Stock Room - ie. the same station I was
looking at on the main form then no events trigger because the station hasn't changed.

IE. My changing of the station is based on code in the Form_Activate event of the main form, and jumping to the asset in the subform is based on code in the Form_Current event of the subform. If there
isn't a change to the station then no events trigger in the subform because well, nothing has changed. If the station doesn't change then no events at all seem to trigger on the subform.

So consequently I'm left pointing at the same station and asset record I was before I loaded my filter form up.

I do hope this helps you.

Thank you very much for replying and your patience.

Kind regards,
Dave

 

[strive4peace]

you're welcome, Dave happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

That'll do the trick nicely.

I'd never have thought of doing it in the 'unload' event, but it makes perfect sense. So many ways of doing things.

Nice work - many thanks.

Warm regards,
Dave

Hi Dave,

If I am understanding this correctly...

it seems that your popup filter form needs to position the record on the
main subform when it closes -- actually, I would put the code on the
*Unload* event since the current record will still be active

'~~~~~~~~~~~~~~
' use WITH so we do not have to repeat the form reference
With forms!mainformname!subform_controlname.form

' save record of popup form if changes have been made
' -- IF applicable
if me.dirty then
me.dirty = false
'show changes on main subform
.requery
end if

' if you are not on a current record, don't do anything
if me.newRecord then exit sub

' find the first value that matches in the main subform
.RecordsetClone.FindFirst "[SomeID] = " & me.RecordID

' if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

WHERE
SomeID is the fieldname of the Workstation Asset ID
RecordID is the field or controlname on the popup form that corresponds
to the Workstation Asset ID
mainformname is the name of the mainform
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

My apologies for being vague - I realised that I wouldn't explain it properly.

Basically on my main station form I have a subform for the assets for each station. I want to be able to filter all the assets so I have a popup form which lists all the assets and lets me filter
them.

The main form is still open underneath my 'filtering' form by the way, this 'filtering' form has a button - 'Jump to asset' which, closes the filter form and returns to the main form. Hopefully
re-locating me to the asset I have chosen during my filter.

I have code which re-positions to the correct station and then jumps to the row in the asset subform which contains the asset selected from the 'filtering' form.

Hope it's clear so far.

For the main this all works well and I'm pleased with it, but there is one condition which has me stumped...

My problem is this. Say I'm looking through the stations and I'm on station 'Stock Room', this has 200 assets and I'm looking for a keyboard by serial no. - it's quicker to bring up the filter form
and filter on part of the serial no. It may or may not be in the 'Stock Room' - I'm not sure. However, if I then jump to asset and that asset 'is' in the Stock Room - ie. the same station I was
looking at on the main form then no events trigger because the station hasn't changed.

IE. My changing of the station is based on code in the Form_Activate event of the main form, and jumping to the asset in the subform is based on code in the Form_Current event of the subform. If there
isn't a change to the station then no events trigger in the subform because well, nothing has changed. If the station doesn't change then no events at all seem to trigger on the subform.

So consequently I'm left pointing at the same station and asset record I was before I loaded my filter form up.

I do hope this helps you.

Thank you very much for replying and your patience.

Kind regards,
Dave

Hi Dave,

trying to understand better what you are doing ...

main form -- assets
subform -- workstation assets

button on main form opens another form showing detail and closes the
main form

"But if the asset belongs to the station that was already 'in view' on
the subform - the sub-form 'current' event doesn't re-position my
.FindFirst to the correct row."

what form does this statement apply to? the form you have just opened?
Can you describe the form that is opened?

Warm Regards,
Crystal

*
have an awesome day
*


Dave wrote:
Hi All,

I hope you can help me here, I'm a little confuzzled.

All my forms are based on data sheets.

I have a main form (workstations) with a sub-form (workstation assets).

I have a button on the main form which loads another form with a view of all the workstation assets (everything in the database). This allows me to filter the assets. I have a button on this form -
'Jump to asset' which closes the form and moves the station view to the station that contains 'that' asset - and positions it on the row that contains the asset.

All well and good - most of the time.

But if the asset belongs to the station that was already 'in view' on the subform - the sub-form 'current' event doesn't re-position my .FindFirst to the correct row.

I hope I'm making myself clear here.

I know it has to be something to do with the filter jumping to the current station - and assets, but I'm unsure how to kick it to re-look at the record set already loaded.

Apologies if this is unclear, but I've spent ages trying to word this so it's understandable. I hope it's clear to someone.

Many thanks,
Dave

 

[strive4peace]

thank you, Dave <blush> -- happy you have a warm feeling <smile>

hope you enjoy Access Basics!

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

What are you !!?

You're a star !

I've just finished implementing the changes and it all works great. That's the first time I've changed an underlying forms' recordsource whilst being 'above it' (so to speak) ie. while unloading
another form. It works great.

The actual error only manifested itself occasionally, but it irrated me. I have a warm feeling now watching it work properly.

Tommorow I'm gonna sit down and read through your tutorial.

Many thanks,
Dave

Hi Dave,

If I am understanding this correctly...

it seems that your popup filter form needs to position the record on the
main subform when it closes -- actually, I would put the code on the
*Unload* event since the current record will still be active

'~~~~~~~~~~~~~~
' use WITH so we do not have to repeat the form reference
With forms!mainformname!subform_controlname.form

' save record of popup form if changes have been made
' -- IF applicable
if me.dirty then
me.dirty = false
'show changes on main subform
.requery
end if

' if you are not on a current record, don't do anything
if me.newRecord then exit sub

' find the first value that matches in the main subform
.RecordsetClone.FindFirst "[SomeID] = " & me.RecordID

' if a matching record was found, then move to it
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
end with
'~~~~~~~~~~~~~~

WHERE
SomeID is the fieldname of the Workstation Asset ID
RecordID is the field or controlname on the popup form that corresponds
to the Workstation Asset ID
mainformname is the name of the mainform
subform_controlname is the Name property of the subform control

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Hi Crystal,

My apologies for being vague - I realised that I wouldn't explain it properly.

Basically on my main station form I have a subform for the assets for each station. I want to be able to filter all the assets so I have a popup form which lists all the assets and lets me filter
them.

The main form is still open underneath my 'filtering' form by the way, this 'filtering' form has a button - 'Jump to asset' which, closes the filter form and returns to the main form. Hopefully
re-locating me to the asset I have chosen during my filter.

I have code which re-positions to the correct station and then jumps to the row in the asset subform which contains the asset selected from the 'filtering' form.

Hope it's clear so far.

For the main this all works well and I'm pleased with it, but there is one condition which has me stumped...

My problem is this. Say I'm looking through the stations and I'm on station 'Stock Room', this has 200 assets and I'm looking for a keyboard by serial no. - it's quicker to bring up the filter form
and filter on part of the serial no. It may or may not be in the 'Stock Room' - I'm not sure. However, if I then jump to asset and that asset 'is' in the Stock Room - ie. the same station I was
looking at on the main form then no events trigger because the station hasn't changed.

IE. My changing of the station is based on code in the Form_Activate event of the main form, and jumping to the asset in the subform is based on code in the Form_Current event of the subform. If there
isn't a change to the station then no events trigger in the subform because well, nothing has changed. If the station doesn't change then no events at all seem to trigger on the subform.

So consequently I'm left pointing at the same station and asset record I was before I loaded my filter form up.

I do hope this helps you.

Thank you very much for replying and your patience.

Kind regards,
Dave

Hi Dave,

trying to understand better what you are doing ...

main form -- assets
subform -- workstation assets

button on main form opens another form showing detail and closes the
main form

"But if the asset belongs to the station that was already 'in view' on
the subform - the sub-form 'current' event doesn't re-position my
.FindFirst to the correct row."

what form does this statement apply to? the form you have just opened?
Can you describe the form that is opened?

Warm Regards,
Crystal

*
have an awesome day
*


Dave wrote:
Hi All,

I hope you can help me here, I'm a little confuzzled.

All my forms are based on data sheets.

I have a main form (workstations) with a sub-form (workstation assets).

I have a button on the main form which loads another form with a view of all the workstation assets (everything in the database). This allows me to filter the assets. I have a button on this form -
'Jump to asset' which closes the form and moves the station view to the station that contains 'that' asset - and positions it on the row that contains the asset.

All well and good - most of the time.

But if the asset belongs to the station that was already 'in view' on the subform - the sub-form 'current' event doesn't re-position my .FindFirst to the correct row.

I hope I'm making myself clear here.

I know it has to be something to do with the filter jumping to the current station - and assets, but I'm unsure how to kick it to re-look at the record set already loaded.

Apologies if this is unclear, but I've spent ages trying to word this so it's understandable. I hope it's clear to someone.

Many thanks,
Dave