Basic Form query (am sure answer is simple)

  • Thread starter micah jardine via AccessMonster.com
  • Start date
M

micah jardine via AccessMonster.com

Hi,

I have just started to use access.

I have created a form which display personal information.

I have also created a search function that allows you to find the names of
people, the search populates a list box.

What I want to do is double click on the item in the list box and have that
record displayed in the form.

How is this done?

Thanks in advance.
mj.
 
D

Dennis

On the Double Click Event of the Listbox use the following code
This assumes personID is text. Substitute your field from your People table.

Private Sub ListBox_DblClick(Cancel As Integer)
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = '" & Me![ListBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
M

micah jardine via AccessMonster.com

Hi,

This does not appear to work, I modified it sligtly (ID is a autonumber) to
the below, while it does not throw any errors, the record is not displayed.

Set rs = Me.Recordset.Clone
rs.FindFirst [ID] = Me![SearchList]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Any other ideas? can it be done with the DoCmd.GoToRecord method?
 
D

Dennis

Its probably just that ID is Autonumber and SearchList is Text or your
SearchList is not returning the correct value to match your ID. Put a msgbox
statement to check what Searchlist is once you have clicked it from the
listbox. You may need to use ListBox.Column(0) or (1) etc. if you have more
than 1 column in your ListBox
 
M

micah jardine via AccessMonster.com

Hi Dennis,

I have already done this, and the correct value is being obtained,
SearchList.Value is producing the correct ID.

I am trying to have the data displayed in a Tab form, and the listbox is on
the same tab. Will that make a differnce?

:)
 
M

micah jardine via AccessMonster.com

Hi Dennis, this is exactly what I am doing....

Any ideas?

Dim intRecordSelect As Integer
Dim rs As Object

' SearchList.Value takes the bound vaule from SearchList
intRecordSelect = SearchList.Value

' The following code does not throw any errors, but does not work.
Set rs = Me.Recordset.Clone
rs.FindFirst [ID] = intRecordSelect
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

MsgBox intRecordSelect
 
D

Dennis

I don't know what the answer is unless the Tab has something to do with it.
I use this code all the time for selecting records from a combo or listbox.
This code is actually given by the access wizard when you add a combo or
listbox to a form.
 
M

micah jardine via AccessMonster.com

Hi Dennis,

Thx for you help, did what you did ran the wizard and then copied what I
required. the final solution was this...

rs.FindFirst "[ID] = " & Str(Nz(Me![SearchList], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
Top