Double click on a sql result in a listbox Access

Question

I've been looking for some answers on stackoverflow, but I havent found any. So I decided to ask my own question.

I'm coding a search engine in VBA in order to find contracts in my DB.

This is what I've done so far

Private Sub cmd_recherche_Click()

Dim strTable As String, strField As String, strCriteria As String, strSql As String
Dim Criter As Variant

If Me.listbox_search = "Num_contract" Then

Debug.Print Me.listbox_search

strTable = "AFN_LOT0"     
strField = "NUMERO"         


strCriteria = strTable & "." & strField & " Like """ & Me.txt_critere & """"


strSql = "SELECT DISTINCTROW " & strTable & "." & strField & "," & strTable & ".FORMULE," & strTable & ".DATE_EFFET," & strTable & ".DATE_ECHEANCE," & strTable & ".ETAT," & strTable & ".CODE_RESILIATION," & strTable & ".DATE_RESIL," & strTable & ".DATE_OPERATION"
strSql = strSql & " FROM " & strTable
strSql = strSql & " WHERE " & strCriteria & ";"


Me.lst_resultat.RowSource = strSql  
Me.lst_resultat.Requery

End if 
End sub

Now I'd like to be able to double click on a result in the listbox (lst_resultat) and open another form (F_InfosContract) which would contain all the information of the contract(dispatched in separate tables all linked together by the field ID_SOR) selected in the listbox.

I've tried this but it only opens the form with nothing in the listbox..

Private Sub lst_resultat_DblClick(Cancel As Integer)
Dim stLinkCriteria As String
Dim Selection As String

Selection = lst_resultat.Value
stLinkCriteria = Selection

DoCmd.OpenForm "F_InfosContract", , , stLinkCriteria
End Sub

If someone could help me out, that would be nice

Sorry for my english

Mdgy

Andy G · Accepted Answer · 2013-08-12 15:50:54Z

0

DoCmd.OpenForm "F_InfosContract", , , stLinkCriteria

You are only supplying the selected value as stLinkCriteria, this needs to be a correctly formed Where Condition. Take this example:

DoCmd.OpenForm "frmStaff", , , "StaffID=" & Me.List1.Value

StaffID is the name of the field in the other table, ID_SOR in your case.

If the id is a text field then the value needs to be surrounded in apostrophes:

DoCmd.OpenForm "frmStaff", , , "StaffID='" & Me.List1.Value & "'"

answered Aug 12, 2013 at 15:50

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mdgy Over a year ago

Ok, I've replaced stLinkCriteria by "ID_SOR=" & Me.lst_resultat.Value, it still opens the 2nd form with the empty listbox. Is there something that I have to change in the properties of the 2nd form?

Collectives™ on Stack Overflow

Double click on a sql result in a listbox Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related