Opening a form from clicking on a List Box

[Guest]

Hi All, I had searched for some help on this issue and found it but I am
having an error and am requesting some assistance.

I have a form (frmMainMenu) that displays information in an Unbound List
Box. The List box displays the most recent entries in another table
(tblSENESarLog). What I want to do is to be able to click on one of the
entries and have that entry come up, in the Form (frmSENESarLog).

This is what I am using on my After Update event of my List Box:

Private Sub List235_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[id] = " & Me![List235]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Refresh
Me.frmSENESarLog.SetFocus
End Sub

I am getting an error around the Me.frmSENESarLog.SetFocus area. It states:

Compile error:

Method or data member not found.

Any assistance greatly appreciated!

Steph

 

[Guest]

It’s the frmSENESarLog form whose bookmark you need to synchronize with that
of its clone, not that of the current form:

Private Sub List235_AfterUpdate()

Const FORMNOTOPEN = 2450

Dim frm As Form
Dim rst As Object

On Error Resume Next
Set frm = Forms("frmSENESarLog")
Select Case Err.Number
Case 0
' no error
Case FORMNOTOPEN
DoCmd.OpenForm "frmSENESarLog"
Set frm = Forms("frmSENESarLog")
Case Else
' unknown error
MsgBox Err.Description
Exit Sub
End Select
Set rst = frm.Recordset.Clone

With rst
If Not .NoMatch Then
.FindFirst "id = " & Me.List235
frm.Bookmark = .Bookmark
frm.SetFocus
End If
End With

End Sub

Ken Sheridan
Stafford, England

 

[Guest]

Ken, thanks for the response! Worked like a gem EXCEPT, when I click a
certain entry it goes to the same one everytime. If my goal is to go that
specific record, am I missing something or doing something wrong?

Thanks again.

It’s the frmSENESarLog form whose bookmark you need to synchronize with that
of its clone, not that of the current form:

Private Sub List235_AfterUpdate()

Const FORMNOTOPEN = 2450

Dim frm As Form
Dim rst As Object

On Error Resume Next
Set frm = Forms("frmSENESarLog")
Select Case Err.Number
Case 0
' no error
Case FORMNOTOPEN
DoCmd.OpenForm "frmSENESarLog"
Set frm = Forms("frmSENESarLog")
Case Else
' unknown error
MsgBox Err.Description
Exit Sub
End Select
Set rst = frm.Recordset.Clone

With rst
If Not .NoMatch Then
.FindFirst "id = " & Me.List235
frm.Bookmark = .Bookmark
frm.SetFocus
End If
End With

End Sub

Ken Sheridan
Stafford, England

Hi All, I had searched for some help on this issue and found it but I am
having an error and am requesting some assistance.

I have a form (frmMainMenu) that displays information in an Unbound List
Box. The List box displays the most recent entries in another table
(tblSENESarLog). What I want to do is to be able to click on one of the
entries and have that entry come up, in the Form (frmSENESarLog).

This is what I am using on my After Update event of my List Box:

Private Sub List235_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[id] = " & Me![List235]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Refresh
Me.frmSENESarLog.SetFocus
End Sub

I am getting an error around the Me.frmSENESarLog.SetFocus area. It states:

Compile error:

Method or data member not found.

Any assistance greatly appreciated!

Steph

 

[Guest]

The problem is more likely to stem from the data than the code, which is
pretty standard for this sort of navigational routine. The key factor is
that selecting an item in the list box must result in a unique value, i.e.
the values of the list box's bound column, ID in your case, must all be
unique.

If the frmSENESarLog form is then to move to a specific record the ID values
in the form's underlying recordset must also be unique. Otherwise it will go
to the first match for the selected value. Sometimes this is what's wanted,
e.g. you might select a customer from a list box and go to the first of a set
of orders by that customer in a form whose records are sorted by customer. I
get the impression from what you've said that it’s a specific record you need
to go to, so there must be a one-to-one match between the ID selected in the
list box, and the relevant record in the form's recordset.

BTW one thing I omitted was to turn the in-line error handling off again, so
add a line:

On Error Goto 0

after the End Select line. Better still would be to include a generic error
handler at the end of the routine (as is done in code created by the button
wizard etc) and Goto this after the End Select statement.

Ken Sheridan
Stafford, England

Ken, thanks for the response! Worked like a gem EXCEPT, when I click a
certain entry it goes to the same one everytime. If my goal is to go that
specific record, am I missing something or doing something wrong?

Thanks again.

It’s the frmSENESarLog form whose bookmark you need to synchronize with that
of its clone, not that of the current form:

Private Sub List235_AfterUpdate()

Const FORMNOTOPEN = 2450

Dim frm As Form
Dim rst As Object

On Error Resume Next
Set frm = Forms("frmSENESarLog")
Select Case Err.Number
Case 0
' no error
Case FORMNOTOPEN
DoCmd.OpenForm "frmSENESarLog"
Set frm = Forms("frmSENESarLog")
Case Else
' unknown error
MsgBox Err.Description
Exit Sub
End Select
Set rst = frm.Recordset.Clone

With rst
If Not .NoMatch Then
.FindFirst "id = " & Me.List235
frm.Bookmark = .Bookmark
frm.SetFocus
End If
End With

End Sub

Ken Sheridan
Stafford, England

Hi All, I had searched for some help on this issue and found it but I am
having an error and am requesting some assistance.

I have a form (frmMainMenu) that displays information in an Unbound List
Box. The List box displays the most recent entries in another table
(tblSENESarLog). What I want to do is to be able to click on one of the
entries and have that entry come up, in the Form (frmSENESarLog).

This is what I am using on my After Update event of my List Box:

Private Sub List235_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[id] = " & Me![List235]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Refresh
Me.frmSENESarLog.SetFocus
End Sub

I am getting an error around the Me.frmSENESarLog.SetFocus area. It states:

Compile error:

Method or data member not found.

Any assistance greatly appreciated!

Steph