Open a record from Listbox in another form

[myrt]

Hello,
I hope this is the right place to ask for help.
I'm not sure how to write the code since docmd.GoToRecord is not a line I'm overly familiar with.

I have a form where, by clicking a button, I jump to another form with a searching bar through a listbox. Now, when I double click a record in the list, I'd like to have the previous form go to that record and the current form to close. I'd like to avoid reopening the previous form since - upon loading - it runs a code that alters a table.
Also, I tried (but I have no idea how to accomplish this) to get this effect: whenever I click elsewhere from the current searching form, I'd like it to close down. At the moment if I click out of the window of this popup form it blinks several times to accentuate the fact that I must close it by clicking on the 'X'.

Here are my attempts
on the main form:

Private Sub SearchClient_Click()
       DoCmd.OpenForm "Risultati", , , , , acDialog
End Sub

On the 'popup' search form

Private Sub SearchResults_DblClick(Cancel As Integer)
        DoCmd.GoToRecord '?? I'm not sure how to write it 
End Sub

Private Sub Form_LostFocus()
         DoCmd.Close acForm, frmMe.Name 'doesn't work :(
End Sub

'the following part was something I found online
Private Sub Form_Open(Cancel As Integer)
         Dim frmPrevious As Form
         Set frmPrevious = Screen.ActiveForm
End Sub
     
Private Sub Form_Close()
          frmPrevious.SetFocus
End Sub

 

[Ranman256]

There's no need to close the previous form, minimize if you want.
Click button,and get the ID to open the new form....
Docmd.openform "frmDetail",,"[keyID]=" & me.ID

 

[myrt]

Thanks for your speedy reply!

If I use a docmd.openform will it reset focus on the previous form or will the it reopen the form anew as I mistakenly thought it would do?

 

[myrt]

Guys, I tried. Nothing happens. No idea why.
I even turned off the .allowedits property in CLIENTS form. Still nothing
I tried to put column(0) and column (1) and non column at all...

DoCmd.OpenForm "CLIENTS", acNormal, "[IDClient] = " & Me.SearchResults.Column(1)

when I tried:
DoCmd.OpenForm "CLIENTS", acNormal, "[IDClient] = " & Me.IDClient
I got compiling error "not found..."

DoCmd.OpenForm "CLIENTS", acNormal, "[IDClient] = " & Me.SearchResults

CLIENTS is the table I want to open
IDClient is the associated first column in che listbox. Both form have the same table as rowsource
SearchResults is the listbox name

What am I doing wrong?


In the end I applied a filter.

 

[Rx_]

Just a reminder, there is a Sample Databases link on this forum.
It has been a while since I looked. There were some good demos there of linked / cascading and other list box posted there.
If anything, it helped expand my options of how to approach a problem.

 

[AccessBlaster]

This is how I do it. Double click the listbox in design view. On the events tab scroll down to the double click event, click on the three (...) dots. And paste something like this.

On Error GoTo Form_Err
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CLIENTS"

    stLinkCriteria = "[IDClient]=" & Me![SearchResults]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Form_Exit:
    Exit Sub

Form_Err:
    MsgBox err.Description
    Resume Form_Exit

 

[myrt]

thanks! That worked beautifully
So the place where to put the criteria sting was one comma too early. Since the suggestions that appear while typing don't tell me much I tried to follow other users' directions. Thanks again for your reply )

 

[AccessBlaster]

Glad it worked out.