Help - VBA - Search Functionality - Database MS access

[elitetraj]

Hi All,

Pls help me!!!

• 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.

 

[DCrake]

You cannot use TextBox1.Text unless the control has the focus