Help - VBA - Search Functionality - Database MS access

Question

Database Structure:

UserName String

Location String

Price Number

Requirement:

I have listbox with some items into it say 100. The user will select only 10 randomly in the listbox (after changing the MULTISELECT to 2fmMultiselect property). I will have search button. Once I select and click Search, the total price of selected items has to be calculated and displayed.

My search code ( Thanks to Alex sir)

enter code here
Private Sub CommandButton4_Click()

Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sName As String

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\test2.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open

Set rs = New ADODB.Recordset

sName = Replace$(TextBox1.Text, "'", "''")
rs.Open "Select * from SampleTable where UserName = '" & sName & "'", Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If (rs.EOF) Then
    MsgBox "no match"
Else
    TextBox3.Text = rs("UserName") & " " & rs("Location")
     
    
    rs.Close
    
End If

Set rs = Nothing
Cn.Close
Set Cn = Nothing


End Sub

That code was just to search and display in a textbox.

Now, I need to total the price of what all UserName field selected by the user from listbox.

Why are you using ADO in Access? DAO makes more sense. But for that matter, it's not clear to me why you need a recordset in the first place. Don't you want to display the results in a form with a total? In that case, it's just a matter of getting the criteria passing it in the command you use to open the form. — David-W-Fenton
– David-W-Fenton, Commented Apr 18, 2011 at 2:11

Praesagus · Accepted Answer · 2011-05-20 17:24:28Z

1

Try this, but make sure the bound column in your list box is the ID field of your table.

Private Function SelectedListString(lstSelected As Access.ListBox) As String
'Loop though a list and get the IDs of all the selected items
'Assumes the bound column is the contains the ID field
    Dim sList As String
    Dim vSelected As Variant        
    With lstSelected
        For Each vSelected In .ItemsSelected
            sList = sList & .ItemData(vSelected) & ","
        Next
    End With
    If sList <> "" Then sList = Left(sList, Len(sList) - 1) 'trim trailing coma
    SelectedListString = sList
End Function

you can use the result in an IN statement in your SQL

Dim sSql As String
If myListbox.ItemsSelected.Count > 0 Then
    sSql = "SELECT * FROM table WHERE IDField IN (" & SelectedListString(myListbox) & ")"
End If

edited May 20, 2011 at 17:24

answered May 20, 2011 at 17:12

Praesagus

2,1045 gold badges30 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Help - VBA - Search Functionality - Database MS access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related