Search List box dbl click problem

[Pusher]

Hi all,

I found this sweet little search db example that I want to learn to integrate into my db. It’s a simple search and double click select back to the original form. That is the problem, i integrated the search but double click with selecting the record i can't solve. Here is the link to that db.


http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=11661&d=1132308964


Can some one explain to me this code and how can I integrate it into my db.

  Private Sub List2_DblClick(Cancel As Integer)
  Dim rs As Object
   
      DoCmd.OpenForm "frmDVDTitles"
   
      Set rs = Forms!frmDVDTitles.Recordset.Clone
      rs.FindFirst "[DVDID] = " & Str(Nz(Me!
[List2], 0))
      If Not rs.EOF Then Forms!frmDVDTitles.Bookmark = rs.Bookmark
   
      DoCmd.Close acForm, Me.Name
   
  End Sub

Thanks

 

[Addyman]

I've added some comments to the code to describe what each step is doing. To integrate into your database, you will need to ensure that each record you have has a unique ID (which will your equivalent of DVDID from the example) and you will then need to substitute your database names into the code for the following items:

frmDVDTitles (the 'results' form)

List2 (the search list box)

[DVDID] (the unique ID field)

Make sure that the bound column in your search list box is set to the column which holds your unique ID.

Private Sub List2_DblClick(Cancel As Integer)
  Dim rs As Object
   
      [COLOR="Red"]' Open the 'results' form:[/COLOR]
      DoCmd.OpenForm "frmDVDTitles"
      
      [COLOR="red"]'Create a copy (clone) of the recordset / recordsource of the form to search through to find 
a match to the record you double-clicked[/COLOR]
      Set rs = Forms!frmDVDTitles.Recordset.Clone
      
      [COLOR="red"]'Find the first record in this cloned recordset where the ID field (DVDID)
 matches the ID of the item double-clicked in the listbox (if for some reason nothing is selected,
return the value '0')[/COLOR]
      rs.FindFirst "[DVDID] = " & Str(Nz(Me![List2], 0))

      [COLOR="red"]'If a match is found (i.e. you haven't reached the end of the recordset (rs.EOF) without 
finding a match, then set the bookmark of the form to be the same as the bookmark of the recordset (a bookmark is as it
 suggests, it tells you where you are within a set of records)[/COLOR]
      If Not rs.EOF Then Forms!frmDVDTitles.Bookmark = rs.Bookmark

      [COLOR="red"]'Close the search form[/COLOR]  
      DoCmd.Close acForm, Me.Name
   
  End Sub

 

[Pusher]

My query does not pull the whole record, must i include a unique id in my query and will he automatically search true the unique ids?
And is this correct interpunction?

rs.FindFirst "[ID_RASKRSNICE] =  & STR(Nz(Me!
[List9], 0))"

And my form is already open, how do i bring it to font when i double click on the record?

 

[Addyman]

You must have the unique ID (ID_RASKRSNICE) as the first column of you search list box and also it must be included in the recordsource and on the form for your results form (can be a hidden text box on your results form if you don't want to show it).

If you form is already open, I would suggest that when you double-click you add a line of code to hide the search form so that only the other form is showing. Then add a button on your results form to re-open the search form.

 

[Pusher]

I made it work BUT...it’s not what I want - I’m using this basic form for just entering the data so I don’t actually need to find the first record, I just need to fill the combo box with the found record. The search is for the name of the street (with some other information), when I dbl click on it in the search form I want it to just be entered in that combo box in my main form. How do I code that?

 

[Pusher]

Can some one help me with this, this will be a crucial part of my db and I will use this in many places. I have a search form – when I find a specific object I want just that object selected in my other form that has one to much relationship to a table that the search form is going thru. What kind of coding I must do to accomplish this?