Using List Box

[benchpolo]

Scenario: I have a database with Member ID and DateCreated columns, which are both unique keys (meaning member id can be duplicated with a different DateCreated). I have an edit form where I load a list box form listing the Member ID and Date Created. I need to be able to double click a certain member id and DateCreated and the edit form loads with that selection.

For example,
Sample1: Member ID: 201301 Date Created: 01/04/2013 08:30 AM
Sample2:Member ID: 201301 Date Created: 01/05/2013 09:30 PM

If double click sample one in the list box, it should load the sample one record in my edit form. I can only do this on single value, but i am having difficulty if i need to use more than one field in the list box? make sense?

Thanks in advance!

[Seth Schrock]

The key is to have the primary key included in the row source of the listbox and have the bound column of the list box be the column that has the primary key. The primary key doesn't have to be visible, but it needs to be included. You can then use the list box's OnDblClick event and do something like this:

DoCmd.Openform "[I]Form_Name[/I]"
DoCmd.SearchForRecord , , acFirst, "[I]your_PK[/I] = " & Me.[I]ListBox_Name[/I]

I'm not sure what you mean by "but i am having difficulty if i need to use more than one field in the list box". Are you getting an error message? Is it going to the wrong record? By using more than one field, do you mean if you select more than one field or if you have multiple columns?

[ADezii]

1. You need to access the Values in both Columns of the List Box, then build your Criteria accordingly. Assuming your Table Name is YaDaYaDa, and the List Box is named List1:
   

   Private Sub List1_AfterUpdate()
   Dim lngMemberID As Long
   Dim dteDateCreated As Date
   Dim strCriteria As String
   
   If IsNull(Me![List1]) Then Exit Sub
   
   With Me![List1]
     lngMemberID = .Column(0)
     dteDateCreated = .Column(1)
   End With
   
   strCriteria = "SELECT * FROM <YaDaYaDa> WHERE [MemberID] = " & lngMemberID & " AND " & _
                 "[Date Created] = #" & dteDateCreated & "#;"
   
   Debug.Print strCriteria
   End Sub

2. Selecting the 2nd entry in List1 would produce:
   

   SELECT * FROM <YaDaYaDa> WHERE [MemberID] = 201301 AND [Date Created] = #1/5/2013#;

[Seth Schrock]

ADezii, why would you put this in the after update event? I don't see in the OP where information has changed in the list box so how would the listbox AfterUpdate event trigger?

[ADezii]

Strictly for test purposes, Seth.

[NeoPa]

@BenchPolo.
I would hope by now, that you understood the requirement to post what you have in your question when asking for help with code. We do not (are not supposed to) provide code solutions on request. We need to see what you already have and then we can help you from there. In truth, most of our members are so eager they won't require your code to do much more than compile, but we do need to see something before we provide you with code solutions. It's a site rule.

I suspect your best answer regardless, is to design the subform with two controls that reflect the two fields you need to link on, then specify in the SubForm control itself (LinkMasterField s and LinkChildFields), that you want to link on those two fields and all should simply work smoothly. Code shouldn't be required as far as I can see.