Using listbox result to populate form on double click

[BrotherBook]

Hi-

I have a listbox containing a large number of potential prospects. When a user double clicks on one, I am trying to pass information contained in the list box to the new entry of a form that launches on the double click. What is weird is that the first field populates in the new form, but the subsequent fields do not. The only difference is the first field is shown in the listbox and the other fields are not shown. Below is the code I am using.

Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "Prospect/Client Entry"
DoCmd.GoToRecord acDataForm, "Prospect/Client Entry", acNewRec

[Forms]![Prospect/Client Entry]![Company_Name] = [Forms]![Menu]![SearchResults].Column(1)
[Forms]![Prospect/Client Entry]![Contact_Name] = [Forms]![Menu]![SearchResults].Column(4) & " " & [Forms]![Menu]![SearchResults].Column(5)
[Forms]![Prospect/Client Entry]![Contact_Title] = [Forms]![Menu]![SearchResults].Column(6)
[Forms]![Prospect/Client Entry]![Contact_Phone] = [Forms]![Menu]![SearchResults].Column(7)
[Forms]![Prospect/Client Entry]![NAICS] = [Forms]![Menu]![SearchResults].Column(8)

End Sub

The Company_Name field populates with the correct information, but the remaining fields are not populating. Any ideas?

Mike

 

[pr2-eugin]

Mike, I am not sure, looks a bit okay.. Maybe try this..

Private Sub SearchResults_DblClick(Cancel As Integer)
    Docmd.OpenForm "Prospect/Client Entry", DataMode:=acFormAdd
    [Forms]![Prospect/Client Entry]![Company_Name] = Me.[SearchResults].Column(1)
    [Forms]![Prospect/Client Entry]![Contact_Name] = Me.[SearchResults].Column(4) & " " & Me.[SearchResults].Column(5)
    [Forms]![Prospect/Client Entry]![Contact_Title] = Me.[SearchResults].Column(6)
    [Forms]![Prospect/Client Entry]![Contact_Phone] = Me.[SearchResults].Column(7)
    [Forms]![Prospect/Client Entry]![NAICS] = Me.[SearchResults].Column(8)
End Sub

 

[BrotherBook]

pr2-eugin - Thanks for the reply.

I tried the change and it is still not working. I also looked through my OnCurrent, OnLoad, and OnActivate code for the "Prospect/Client Entry" form and I don't see anything that should affect the values I am trying to pass through. The only code in there is to maximize the screen, show/hide buttons if it's a NewRecord, and limit a rowsource based on if it is a NewRecord.

 

[pr2-eugin]

Okay lets try and debug the problem.. Just see if you can get the values in the immediate window (CTRL+G)..

Private Sub SearchResults_DblClick(Cancel As Integer)
    Debug.Print Me.[SearchResults].Column(1) & " - " & Me.[SearchResults].Column(4) & " - " & Me.[SearchResults].Column(5) 
    Debug.Print Me.[SearchResults].Column(6) & " - " & Me.[SearchResults].Column(7) & " - " & Me.[SearchResults].Column(8)
[COLOR=Green]    'Docmd.OpenForm "Prospect/Client Entry", DataMode:=acFormAdd
    '[Forms]![Prospect/Client Entry]![Company_Name] = Me.[SearchResults].Column(1)
    '[Forms]![Prospect/Client Entry]![Contact_Name] = Me.[SearchResults].Column(4) & " " & Me.[SearchResults].Column(5)
    '[Forms]![Prospect/Client Entry]![Contact_Title] = Me.[SearchResults].Column(6)
    '[Forms]![Prospect/Client Entry]![Contact_Phone] = Me.[SearchResults].Column(7)
    '[Forms]![Prospect/Client Entry]![NAICS] = Me.[SearchResults].Column(8)[/COLOR]
End Sub

 

[BrotherBook]

When I double click a record in search results nothing happens with that code. I also removed the brackets around SearchResults and still nothing.

However, I changed the Debug.Print to MsgBox() and the message box pops up. I am getting no values for the hidden fields.

AH HA!

pr2-eugin - I went back and checked my RowSource for SearchResults and realized that for some reason Access wasn't referencing my source query any more. It had the original SQL from the query I based the listbox on, not a reference to my query. So my listbox didn't actually contain those columns.

n00b mistake on my part pr2-eugin... Thanks for helping me with this!

 

[pr2-eugin]

Glad you have it sorted.. Good luck !!