Listbox selection

[DubboPete]

Hi all,

haven't had much to do with listboxes before, cos I haven't had the need.
BUT.... now I need to click on a listbox row, and then update a field called
[note] from the same table.

The listbox rowsource property is this

SELECT [ID], [ContactDate], [ContactTime] FROM ContactHistory WHERE [ID] =
Combo0.Value;

On clicking one of the many rows in the listbox, I just want to display the
contents of that row value's [note] field in a memo box called [memo1]

sorry for being stupid, but I couldn't find it on the Group web search...

DubboPete
the List's 17th Century Goat Herder

 

[Guest]

So, the [note] field is in the same CaseHistory table and is therefore tied
to the listbox's row value by the [ID] field? If so, in the listbox
AfterUpdate event write code to lookup the [note] field based on the [ID] and
then display it in the memo box.

Sub Listbox1_AfterUpdate()
If Not IsNull(Me.Listbox1)Then
Dim strNote as String
strNote= DLookup("Note", "CaseHistory", "ID='" & Me.Listbox1)
Me.Memo1 = strNote
End If
End Sub

IF the listbox is supposed to bring up the record that was selected in the
listbox (your form's control source is set to CaseHistory) AND you have the
memo1's control source set to [note] then you can use something more like
this:

Private Sub Listbox1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Listbox1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Hope this helps!

Diana Criscione

 

[DubboPete]

Hi Diana

The former bit of code worked a treat - many thanks for your help

Pete