As a starter, suggest you have a look at the Access Help at these topics:
ItemsSelected and ItemData
They might give you the key to accessing items in a list box.
I have used code like this to open a specific record in a next form, based on the selection in a list box:
Dim frm As Form, ctl As Control
Dim varElement As Variant, inti As Integer
Dim stDocName As String
Dim stLinkCriteria As String
Dim SQLTekst As Variant
SQLTekst = ""
Set frm = Forms!MySelectionForm 'This is my form with the list box
Set ctl = frm!LstKlant 'This is my listbox
inti = 1
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Make your choice!", vbCritical, "Warning!"
If ctl.ItemsSelected.Count > 1 Then
MsgBox "You've selected " & ctl.ItemsSelected.Count & " items. " , vbInformation
'List box is set to multiple choice, so walk through all selected items:
For Each varElement In ctl.ItemsSelected
If inti > 1 Then
SQLTekst = SQLTekst & " OR "
SQLTekst = SQLTekst & "[MyKeyValueID]=" & "'" & ctl.ItemData(varElement) & "'"
inti = inti + 1
Re: Getting error on "DoCmd.OpenForm stDocName, , , stLinkCriteria"
Check the syntax of SQLTekst by removing the Rem.
Does the message box show like decent SQL?
Since you built SQLTekst with single and double quotes, things go easily wrong here. May also depend on the type of variables you pass in to the expression, whether they are text or numeric.
Just playing around with it will help you.
If you remove the , , , stLinkCriteria, your form opens normally I expect?
If so, then you are sure it is something in stLinkCriteria, which is the same as SQLTekst.
I think I found the problem. I was trying to pull a record by the autonumber field called Ga_Record_ID. And it was not working. So, I created another filed (text) called Ga_Record_ID_2 which basically copies ga_record_id field. And everything works fine.
I want to especially thank to Marion who helped me resolve this issue. Thanks Marion. Really appreciated your generous help.