ListBox Value One form to another without filter applied

[bobdydd]

Hi All

Access 2007

I have a form with a ListBox called "ListContactTasks"
DoubleClicking the ListBox opens another form called "frmContact" at a
particular record corresponding to the one in the listbox Column(0)

The ListBox is on a form that has no recordset of it's own.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContact"
stLinkCriteria = "[ContactID]=" & Me!
[ListContactTasks].Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that it takes me to the correct record alright, but
applies a filter that endusers compalin about.

So my question: Is there a technique to achieve the above WITHOUT the
filter remaining on?

Regards and Thanks

 

[Ken Sheridan]

Instead of filtering the form move to the first matching record when the
frmContact form loads, so for your list box's DblClick event procedure simply
open the frmContact form with:

DoCmd.OpenForm "frmContact"

In the frmContact form's Load event procedure check to see if the unbound
form with the list box is open (I've assumed its called frmContactDlg below)
by attempting to return a reference to it, and if so find the first match in
the form's recordset's clone, and if a match exists synchronize the form's
bookmark with the clone's:

Dim rst As Object
Dim frm As Form

On Error Resume Next
Set frm = Forms("frmContactDlg")

If Err = 0 Then
Set rst = Me.Recordset.Clone
With rst
.FindFirst "ContactID = " & frm.ListContactTasks.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Ken Sheridan
Stafford, England

 

[storrboy]

Hi All

Access 2007

I have a form with a ListBox called "ListContactTasks"
DoubleClicking the ListBox opens another form called "frmContact" at a
particular record corresponding to the one in the listbox Column(0)

The ListBox is on a form that has no recordset of it's own.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContact"
stLinkCriteria = "[ContactID]=" & Me!
[ListContactTasks].Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that it takes me to the correct record alright, but
applies a filter that endusers compalin about.

So my question: Is there a technique to achieve the above WITHOUT the
filter remaining on?

Regards and Thanks

If you can make a few changes to the forms, my prefered method would
be to supply the criteria in the OpenArgs and move the forms book mark
when it opens.

<In Click Event change last line to >
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

<In other forms open event, add or insert >
Dim rst As DAO.Recordset

'Change to continue event if other code runs
If IsNull(Me.OpenArgs) Then Exit Sub
Set rst = Me.RecordsetClone
rst.FindFirst Me.OpenArgs
If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark

Set rst = Nothing

The form should move to the selected record if found, open to the
begining if not, no filter applied.

 

[bobdydd]

Thanks!

I will give that try

 

[i_takeuti]

Instead of filtering the form move to the first matching record when the
frmContact form loads, so for your list box's DblClick event procedure
simply
open the frmContact form with:

DoCmd.OpenForm "frmContact"

In the frmContact form's Load event procedure check to see if the unbound
form with the list box is open (I've assumed its called frmContactDlg
below)
by attempting to return a reference to it, and if so find the first match
in
the form's recordset's clone, and if a match exists synchronize the form's
bookmark with the clone's:

Dim rst As Object
Dim frm As Form

On Error Resume Next
Set frm = Forms("frmContactDlg")

If Err = 0 Then
Set rst = Me.Recordset.Clone
With rst
.FindFirst "ContactID = " & frm.ListContactTasks.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Ken Sheridan
Stafford, England

Hi All

Access 2007

I have a form with a ListBox called "ListContactTasks"
DoubleClicking the ListBox opens another form called "frmContact" at a
particular record corresponding to the one in the listbox Column(0)

The ListBox is on a form that has no recordset of it's own.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmContact"
stLinkCriteria = "[ContactID]=" & Me!
[ListContactTasks].Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

My problem is that it takes me to the correct record alright, but
applies a filter that endusers compalin about.

So my question: Is there a technique to achieve the above WITHOUT the
filter remaining on?

Regards and Thanks