Populate a subform field based on a search form list box

[kysterathome]

Hi All,

I am looking for a solution for a lookup funtion and hope s.o. has an idea.

What I got:
A main form with meetings (frmMeetings)
A subform with Attendees (subfrmAttendees) - a continuous Form that has buttons next to each name entry, one of which is Cmd Button NameLookup that launches frmSearchContact

Objective:
Populate the sbfrmAttendees with names selected from a tblContacts

Tool:
frmSearchContact allows the user to look up Attendees according to certain criteria (e.g. CompanyName, LastName) from tblContacts and returns a list of names into a listbox (lstCustInfo) This I got. Upon double-clicking on any given name in this results list, as it is now, it opens the main subfrmAttendees but doesn't popluate the last field in the particular meeting I want it to. This is the last code, I believe that will be relevant:

'Pass the SQL to the RowSource of the listbox
Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmAttendees based on the ID from lstCustInfo listbox

DoCmd.OpenForm "ContactsForm", , , "[ID] = " & Me.lstCustInfo

Upon double clicking the name in the list box, I would like to return to the frmMeetings (as opposed to re-openening it, I guess) at the place where I left it (at that particular meeting when I first opened the frmSearchContact) and have the empty field in subfrmAttendees be populated with the result I clicked on.

But ... how do I do that? I am a beginner in coding, hence a maybe simple question... (silently hoping)

I would be grateful for any indication you may have

Kysterathome

 

[Reinier10]

Hi Kysterathome,

From what I understand you need the subform's controls (sbfrmAttendees) to be set to the values of the listbox on your search form?
I'm not sure what you mean, since in the code the COntactsform is opend. Above you wrote you got the part of filling the listbox, so I think you meen to return to the frmAttendees? I also think you mean to leave the frmAttendees opened, when the searchbutton (Cmd Button NameLookup) is clicked?
So I'll try to help you on setting the subform controls to the values of the listbox 'lstCustInfo'

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmAttendees based on the ID from lstCustInfo listbox

'I assume the frmAttendees is still opened. Later on we can 'return on that, findng out how to check this through code

Dim frmMeeting as Form 'variable thold the form frmMeetings
Dim sfrmAttendees as Form 'variable to hold the subform 

set frmAttend=forms("Attendees")  'set the variable tot the mainform

set frmAttendees= frmMeeting.Controls("sbfrmAttendees").Form 
'set the variable to the subform. You actually rever to the subform-[b]control[/b], 
'and address it's property Form the point to the form object 

'We dont have to set the mainform to the right record , I assume it was selected first and is still there
'We set the controls to the values of the listbox
'I actually don't have then names of the controls on your subform so I made some up
frmAttendees.Controls("FirstName")=me.lstCustInfo.Column(0)
frmAttendees.Controls("LastName")=me.lstCustInfo.Column(1)
'and so on. 
'Easer would be looping the controls on the subforms detail  section. ( again we can do this later)

end sub

Is this wat you are trying to do, and does it make some sense to you? Willing to (try to) help some more, just ask.

Grz
Reinier

 

[TheAceMan1]

How are ya kysterathome . . . . .

Replace what you have in the listbox [blue]Double-Click event[/blue] with the following ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Forms!frmMeetings!subfrmAttendees.form![purple][b]LastTextBoxName[/b][/purple] = Me![purple][b]ListBoxName[/b][/purple].Column([b]1[/b])[/blue]

You may have to play with the column number [blue].Column([purple]1[/purple])[/blue]. Just remember column numbers start at zero (from left to right) and include any Widths set to zero in the [blue]Column Widths[/blue] property of the listbox.

See Ya! . . . . . .

 

[kysterathome]

TheAceMan1,

Your suggestion worked perfectly. Thank you so much!!

Reinier10, thank you too for your inputs and willingness to help!

Now, my application rocks even more - I am SO happy... another sleepless night full of excitement

Kysterathome