List Box to SubForm 1

[SJW]

What is the code for double clicking in a list box and putting that information in a SubForm as a new record.<br>Thanks

 

[RoyVidar]

What I use, is often variations on the following code, given that the listboks value is the ID of the recordset you'll use in the sub-form;<br><br>Private Sub lstOpenItems_DblClick()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim stDocName As String<br><br>&nbsp;&nbsp;&nbsp;&nbsp;stLinkCriteria = &quot;[SubID]=&quot; & &quot;'&quot; & Me![lstOpenItems] & &quot;'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenForm stDocName, , , stLinkCriteria<br><br>End Sub<br><br>If you've filled the listboks with data that aren't a record, you might wanna check out the column property of a list boks<br><br>varTest = lstList.Column(Column[,row])<br><br>varTest = lstOpenItems(2,3) will give you the value in the third column of the fourth row of the list<br>(Access uses zero-based numbers for the column and row numbers - that's standarization;-)

 

[SJW]

Thanks RoyVidar let me explain a little more <br><br>I would like the information in the listbox (ItemID)put in a subform that was made from a new table.<br><br>Will the same code work?<br><br>Thanks for the help<br><br>Steve

 

[RoyVidar]

Sorry, I misunderstood. The code previously given, is for opening a new form where the forms ID matches the value in the first column of the listboks.<br><br>Adding a new record in a subform based on a selection in a listbox in a main form (note the comment below), could be performed like this;<br><br>[subForm].[Form]![subField] = Me!lstOpenItems ' In the OnDblClick event of the listboks.<br><br>Be aware though, that if the sub contains a record, with a value in this field, you will change that value unless you also move to a new record in the sub.

 

[SJW]

How do you move to a new record in the sub after double click<br>Update?<br>NewRecord?<br><br>I need to add more then one record.<br><br>Thanks,<br>Steve<br>

 

[RoyVidar]

Well, I’m afraid that’s not one of my favourites. Whenever I try this, I seem to trigger “all possible events” and thereby loosing control on the main/sub behaviour. Perhaps some of the experts on this site might give you a better answer. <br><br>I’ll try giving you a non-code solution, not involving double clicking at all. If this solves your problem, you might use clicking events to change the visible property of the sub or changing other control properties, if that’s interesting. <br><br>Link the sub to your listbox. (Properties of the sub, data tab) Link Child Fields; “ItemID” (field/control name in the sub) Link Master Fields; “lstOpenItems” (listboks).<br><br>This will provide linking between the listboks and the sub. Your foreign key field will automatically receive the value from the selected item in your listboks. This is based on ordinary one-to-many-relationship. <br><br>To make the sub only available for data entry, set the Data Entry property for the sub (properties, data tab) to yes.<br>