Need to select from listbox and open form to that record

[desjardins]

Hi,

I have a form where people can enter new employees. If they enter in a last name that matches a current employee, I have a pop-up form asking them to check for duplicates. There is a listbox on this form that contains possible duplicates, for example
Jane Smith
Joe Smith
Bob Smith

What I want is for them to be able to choose "Joe Smith" from the listbox and have the main form (frm_checklist) populate with Joe Smith's information so they can edit it.

Here is my code for the command button:

Private Sub cmd_choose_Click()
On Error GoTo Err_cmd_choose_Click
Dim strSelection As String, varItem As Variant
Dim strSQL As String
For Each varItem In Me.list_choose.ItemsSelected
strSelection = strSelection & Me.list_choose.ItemData(varItem) & ","
Next
strSelection = Left(strSelection, Len(strSelection) - 1)
strSQL = "tbl_employees.PersonID = (" & strSelection & ")"
' open the checklist form for the selected person
DoCmd.OpenForm "frm_checklist", acNormal, , strSQL, acFormEdit, acWindowNormal, ""
DoCmd.Close acForm, "frm_duplicates"

Exit_cmd_choose_Click:
    Exit Sub

Err_cmd_choose_Click:
    MsgBox Err.Description
    Resume Exit_cmd_choose_Click
End Sub

Here's what is happening:
1. When the possible duplicate last name is entered, the duplicates form pops up with the correct list of names.
2. When I choose a name from the listbox and click on the select button (cmd_choose) the pop-up form is supposed to close and return me to that person's record on the main form. But I get a message saying "Open form was canceled" and the pop-up form does not close.

Thanks in advance, y'all have been very helpful to me so far.

 

[John Big Booty]

Have a look at the Where Condition of the OpenForm command, in you situation it will be something like;

"PersonID = " & Me.list_choose

 

[desjardins]

Have a look at the Where Condition of the OpenForm command, in you situation it will be something like;

"PersonID = " & Me.list_choose

This line already does that:

strSQL = "tbl_employees.PersonID = (" & strSelection & ")"

I don't think it's a matter of being able to find the record - in fact I have a very similar form already that lets you search by last name - it's something to do with actually opening the form.

 

[John Big Booty]

Ah Ok, is your form frm_checklist already open? If so try the FindRecrod command instead.

 

[desjardins]

Hm, we're getting closer as it now closes frm_duplicates and resets the focus on frm_checklist, but the frm_checklist doesn't actually go to the record, it stays where it was.

My code (The line I added is in red.):

Private Sub cmd_choose_Click()
On Error GoTo Err_cmd_choose_Click
Dim strSelection As String, varItem As Variant
Dim strSQL As String
For Each varItem In Me.list_choose.ItemsSelected
strSelection = strSelection & Me.list_choose.ItemData(varItem) & ","
Next
strSelection = Left(strSelection, Len(strSelection) - 1)
strSQL = "tbl_employees.PersonID = (" & strSelection & ")"
[COLOR="YellowGreen"]' open the checklist form for the selected person[/COLOR]
DoCmd.OpenForm "frm_checklist"
[COLOR="Red"]DoCmd.FindRecord strSQL[/COLOR]
DoCmd.Close acForm, "frm_duplicates"

Exit_cmd_choose_Click:
    Exit Sub

Err_cmd_choose_Click:
    MsgBox Err.Description
    Resume Exit_cmd_choose_Click
End Sub

 

[John Big Booty]

So is form frm_checklist already open when this code is run?

 

[desjardins]

Yes, it is. The popup form is triggered by the before update event in the Last Name text box. So if I type "Smith", the form (frm_duplicates) pops up with a listbox of people named Smith and asks me if I meant one of those. If I click a name and click the cmd_choose button, it is supposed to return me to the (already open) frm_checklist with the name I chose.

 

[boblarson]

Yes, it is. The popup form is triggered by the before update event in the Last Name text box. So if I type "Smith", the form (frm_duplicates) pops up with a listbox of people named Smith and asks me if I meant one of those. If I click a name and click the cmd_choose button, it is supposed to return me to the (already open) frm_checklist with the name I chose.

Based on that information I believe you want this:

Private Sub cmd_choose_Click()
    On Error GoTo Err_cmd_choose_Click
    Dim strSelection As String, varItem As Variant
    Dim strSQL As String
[B][COLOR=red]    Dim rst As DAO.Recordset[/COLOR][/B]
[B][COLOR=red]    Dim frm As Form[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
[COLOR=green]    ' since trying to find ONE person, set the list box's MULTI-SELECT property to NONE so one value is it.[/COLOR]
[COLOR=green]    ' then use Me.list_choose to get the value of the ID chosen[/COLOR]
    [COLOR=red][B]strSQL = "PersonID = " & Me.list_choose[/B][/COLOR]
[B][COLOR=#ff0000][/COLOR][/B] 
[COLOR=green]    ' open the checklist form for the selected person[/COLOR]
    DoCmd.OpenForm "frm_checklist"
 
  [COLOR=green]  ' set the form to a variable so we can simplify code[/COLOR]
    [B][COLOR=red]Set frm = Forms!frm_checklist[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
    ' set the recordset object to the form's recordsetclone
[COLOR=red][B]    Set rst = frm.RecordsetClone[/B][/COLOR]
 
[COLOR=red][COLOR=green]  ' do a find first to find the record[/COLOR][/COLOR]
[COLOR=red][B]    rst.FindFirst strSQL[/B][/COLOR]
[COLOR=red][B]    If Not rst.NoMatch Then[/B][/COLOR]
[COLOR=red][B]        frm.Bookmark = rst.Bookmark[/B][/COLOR]
[COLOR=red][B]    Else[/B][/COLOR]
[COLOR=red][B]        MsgBox "No Match Available"[/B][/COLOR]
[COLOR=red][B]    End If[/B][/COLOR]
[B][COLOR=#ff0000][/COLOR][/B] 
    DoCmd.Close acForm, "frm_duplicates"
Exit_cmd_choose_Click:
    Exit Sub

 

[desjardins]

Thanks Bob, that worked. I made a few little changes:

Private Sub cmd_choose_Click()
    On Error GoTo Err_cmd_choose_Click
    Dim strSelection As String, varItem As Variant
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim frm As Form
 
     [COLOR="red"]'do not save the attempted duplicate record
    Forms!frm_checklist.Undo[/COLOR]

    ' since trying to find ONE person, set the list box's MULTI-SELECT property to NONE so one value is it.
    ' then use Me.list_choose to get the value of the ID chosen
    strSQL = "PersonID = " & Me.list_choose
 
    ' open the checklist form for the selected person
    DoCmd.OpenForm "frm_checklist"
 
    ' set the form to a variable so we can simplify code
    Set frm = Forms!frm_checklist
 
    ' set the recordset object to the form's recordsetclone
    Set rst = frm.RecordsetClone
 
  ' do a find first to find the record
    rst.FindFirst strSQL
    If Not rst.NoMatch Then
        frm.Bookmark = rst.Bookmark
    Else
        MsgBox "No Match Available"
    End If
 
    DoCmd.Close acForm, "frm_duplicates"
Exit_cmd_choose_Click:
    Exit Sub
[COLOR="Red"]End Sub[/COLOR]

 

[desjardins]

Well, now it's sort of working. There's a problem in this section:

    rst.FindFirst strSQL
    If Not rst.NoMatch Then
        frm.Bookmark = rst.Bookmark
    Else
        MsgBox "No Match Available"
    End If

The first time I choose someone from the listbox, the msg box pops up with "no match available" even though the person is obviously in the database. In fact, anyone who appears in the listbox is de facto in the database, so there should never be "no match". What's weird is that if I re-test it, and choose the same person, it works fine. It's only the first time I choose that person. Any ideas?