Listbox sometimes finds record and sometime pulls different record

G

Guest

I have a listbox that shows the Time a note was written, the EntryType
whether the source of the note is an Email saint/recieved, letter
setn/recieved, phone call S/R etc and then the First 8 characters from the
Note. Users can then select a note based on when they wrote to Whom it was
concerning, and the Content of the note.

However recently it spazzed out and now on one record (yes it works on
everything but one) it selects the first item in the listbox when I select
the last one.

The lstbox is populated by a query on the notesTable.

******* RecordSource for the Listbox.
Public Sub UpdateList()
On Error GoTo UpdateListError
' If IsNull(Forms!frmPropertiesNew!ID) Then ' removed 16 Sep
2003, did not get to clear lstDates
' Exit Sub
' End If

strSQL = "SELECT PropertyID, NoteDate, MemoPart, EntryType, RER,
Contacts " & _
"FROM tblPropertiesUpdates " & _
"WHERE (PropertyID = " & [Forms]![frmPropertiesNew]![ID] & ") "
& _
"ORDER BY NoteDate desc" ' SMRF 6392 - 13
October 2000 - Ruth Radmall

Me.lstDates.RowSource = strSQL
Me.lstDates.Requery

Me.Memo.SetFocus

Exit Sub
UpdateListError:
MsgBox Err.Number & " " & Err.Description

End Sub

*********When I click on a listbox item.
Private Sub lstDates_AfterUpdate()
On Error Resume Next

Dim strSQL As String

Me.RecordsetClone.FindFirst "PropertyID= " & Me.lstDates.Column(0) _
& " AND NoteDate= #" & Me.lstDates.Column(1)
& "#" _
& " AND MemoPart = '" &
Me.lstDates.Column(2) & "'"

Me.Bookmark = Me.RecordsetClone.Bookmark
Me.lstDates.SetFocus

End Sub
 
D

Dirk Goldgar

Dabaum said:
I have a listbox that shows the Time a note was written, the EntryType
whether the source of the note is an Email saint/recieved, letter
setn/recieved, phone call S/R etc and then the First 8 characters
from the Note. Users can then select a note based on when they wrote
to Whom it was concerning, and the Content of the note.

However recently it spazzed out and now on one record (yes it works on
everything but one) it selects the first item in the listbox when I
select the last one.

The lstbox is populated by a query on the notesTable.

******* RecordSource for the Listbox.
Public Sub UpdateList()
On Error GoTo UpdateListError
' If IsNull(Forms!frmPropertiesNew!ID) Then ' removed 16
Sep 2003, did not get to clear lstDates
' Exit Sub
' End If

strSQL = "SELECT PropertyID, NoteDate, MemoPart, EntryType, RER,
Contacts " & _
"FROM tblPropertiesUpdates " & _
"WHERE (PropertyID = " & [Forms]![frmPropertiesNew]![ID]
& ") " & _
"ORDER BY NoteDate desc" ' SMRF
6392 - 13 October 2000 - Ruth Radmall

Me.lstDates.RowSource = strSQL
Me.lstDates.Requery

Me.Memo.SetFocus

Exit Sub
UpdateListError:
MsgBox Err.Number & " " & Err.Description

End Sub

*********When I click on a listbox item.
Private Sub lstDates_AfterUpdate()
On Error Resume Next

Dim strSQL As String

Me.RecordsetClone.FindFirst "PropertyID= " &
Me.lstDates.Column(0) _ & " AND
NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = '" &
Me.lstDates.Column(2) & "'"

Me.Bookmark = Me.RecordsetClone.Bookmark
Me.lstDates.SetFocus

End Sub
Click to expand...

It sounds like it's not finding the record in question, and you have no
check to see if that has happened. Also, you have error-handling
disabled, so you can't tell if an error has occurred. I suspect the
problem with this particular record may be because you are using
single-quotes (') to delimit the value for MemoPart, and (I guess) the
value from lstDates.Column(2) contains that character.

You should be able to fix all these problems by revising your code as
follows:

'----- start of revised code -----
Private Sub lstDates_AfterUpdate()

On Error GoTo Err_Handler

Dim Q As String
Dim QQ As String

Q = Chr(34)
QQ = Q & Q

With Me.RecordsetClone

.FindFirst _
"PropertyID= " & Me.lstDates.Column(0) _
& " AND NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = " & _
Q & _
Replace(Me.lstDates.Column(2), Q, QQ) & _
Q

If .NoMatch Then
MsgBox _
"The record you selected was not found.", _
vbInformation, _
"Not Found"
Else
Me.Bookmark = .Bookmark
End If

End With

Me.lstDates.SetFocus

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of revised code -----
 
G

Guest

Thanks. I guess it pays to check for errors. Appreciate your time.

Dirk Goldgar said:
Dabaum said:
I have a listbox that shows the Time a note was written, the EntryType
whether the source of the note is an Email saint/recieved, letter
setn/recieved, phone call S/R etc and then the First 8 characters
from the Note. Users can then select a note based on when they wrote
to Whom it was concerning, and the Content of the note.

However recently it spazzed out and now on one record (yes it works on
everything but one) it selects the first item in the listbox when I
select the last one.

The lstbox is populated by a query on the notesTable.

******* RecordSource for the Listbox.
Public Sub UpdateList()
On Error GoTo UpdateListError
' If IsNull(Forms!frmPropertiesNew!ID) Then ' removed 16
Sep 2003, did not get to clear lstDates
' Exit Sub
' End If

strSQL = "SELECT PropertyID, NoteDate, MemoPart, EntryType, RER,
Contacts " & _
"FROM tblPropertiesUpdates " & _
"WHERE (PropertyID = " & [Forms]![frmPropertiesNew]![ID]
& ") " & _
"ORDER BY NoteDate desc" ' SMRF
6392 - 13 October 2000 - Ruth Radmall

Me.lstDates.RowSource = strSQL
Me.lstDates.Requery

Me.Memo.SetFocus

Exit Sub
UpdateListError:
MsgBox Err.Number & " " & Err.Description

End Sub

*********When I click on a listbox item.
Private Sub lstDates_AfterUpdate()
On Error Resume Next

Dim strSQL As String

Me.RecordsetClone.FindFirst "PropertyID= " &
Me.lstDates.Column(0) _ & " AND
NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = '" &
Me.lstDates.Column(2) & "'"

Me.Bookmark = Me.RecordsetClone.Bookmark
Me.lstDates.SetFocus

End Sub
Click to expand...

It sounds like it's not finding the record in question, and you have no
check to see if that has happened. Also, you have error-handling
disabled, so you can't tell if an error has occurred. I suspect the
problem with this particular record may be because you are using
single-quotes (') to delimit the value for MemoPart, and (I guess) the
value from lstDates.Column(2) contains that character.

You should be able to fix all these problems by revising your code as
follows:

'----- start of revised code -----
Private Sub lstDates_AfterUpdate()

On Error GoTo Err_Handler

Dim Q As String
Dim QQ As String

Q = Chr(34)
QQ = Q & Q

With Me.RecordsetClone

.FindFirst _
"PropertyID= " & Me.lstDates.Column(0) _
& " AND NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = " & _
Q & _
Replace(Me.lstDates.Column(2), Q, QQ) & _
Q

If .NoMatch Then
MsgBox _
"The record you selected was not found.", _
vbInformation, _
"Not Found"
Else
Me.Bookmark = .Bookmark
End If

End With

Me.lstDates.SetFocus

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of revised code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top