I manipulate a record that I've put into a listbox (vba)

  • Thread starter Thread starter zskillz
  • Start date Start date
Z

zskillz

using access 2003

I've small table that has multiple rows and columns.

I've created a form which has several comboboxes with their control source
based on a column in the table. the user selects options from the comboboxes
and then clicks the 'product search' button. When this button is pressed, a
SQL query is built based on the values in the boxes. Next, the sql query is
run and the results are placed into a listbox (this may be where I'm doing
something wrong) by setting the listbox rowsource to the sql query ordered
by a few of the more important columns _but not all of them_.

Finally - and this is the part I just cannot figure out - I want the user to
be able to double click on an item in the listbox and have all of the columns
associated with that record (which are not all displayed in the listbox) pop
up in a message box. Could someone please tell me how to do this. Does it
have something to do with bookmarks? I've also tried using a recordset, but
I'm obviously doing something wrong there too...

thanks in advance
-z

I've posted the code associated with the "product search" button below:

Private Sub Product_search_Click()
'On Error GoTo Err_Product_search_Click
Dim db As Database
Dim qdf As QueryDef
Dim strSQL, strSQLname, strSQLselectFrom, strSQLWhere
Dim firstSQLwhere As Boolean

' this if condition requires that at least one combobox has a valid entry
If IsNull(cbBrand.Value) And IsNull(cbPhase.Value) And
IsNull(cbLocation.Value) Then
' the following two lines clear out the listbox (I'm not sure it's
the best way to do it, but it works)
lbqrySearch.RowSourceType = "Value List"
lbqrySearch.RowSource = ""
MsgBox "You must select criteria for the search from the drop down
boxes.", vbExclamation, "ATTENTION"
Exit Sub
End If

Set db = CurrentDb
strSQLname = "qrySearch"
Set qdf = db.QueryDefs(strSQLname)
strSQLselectFrom = "SELECT [myTable].* FROM [myTable] "
strSQLWhere = "WHERE "
firstSQLwhere = True

' this multi-nested 'for loop' builds the SQL statement. To add another
search criteria, all the user has to do is add another
' combobox to the form and make sure that the row source is based off a
column in the table
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.ControlType = acComboBox Then
If Not IsNull(ctrl.Value) Then
If strSQLWhere = "WHERE " Then
strSQLWhere = strSQLWhere & Mid(ctrl.RowSource,
InStr(ctrl.RowSource, "DISTINCT") + Len("DISTINCT"), InStr(ctrl.RowSource,
"FROM") - InStr(ctrl.RowSource, "DISTINCT") - Len("DISTINCT")) & " = Forms!"
& Me.Name & "!" & ctrl.Name
Else
strSQLWhere = strSQLWhere & " AND " &
Mid(ctrl.RowSource, InStr(ctrl.RowSource, "DISTINCT") + Len("DISTINCT"),
InStr(ctrl.RowSource, "FROM") - InStr(ctrl.RowSource, "DISTINCT") -
Len("DISTINCT")) & " = Forms!" & Me.Name & "!" & ctrl.Name
End If
End If
End If
Next ctrl

strSQL = strSQLselectFrom & strSQLWhere & ";" ' create the SQL query
string
qdf.SQL = strSQL

'still not totally sure how to list all the controls in the form
Forms!Analytical!lbqrySearch.RowSourceType = "Table/Query"
lbqrySearch.RowSource = "SELECT qrySearch.Location, qrySearch.Brand,
qrySearch.[Part Name], qrySearch.[Stationary Phase] FROM qrySearch ORDER BY
[Brand], [Location], [Part Name], [Stationary Phase];"
lbqrySearch.SetFocus

Set qdf = Nothing
Set db = Nothing

Exit Sub

Err_Product_search_Click:
MsgBox Err.Description
Resume Next 'Exit_Product_search_Click


End Sub
 
*bump*
I know there are some smart people in here, and I know this is a simple
question. I've searched the board and read all of the related posts that I
could find, but nothing has been helpful. Please help if you can.
thanks,
-z

zskillz said:
using access 2003

I've small table that has multiple rows and columns.

I've created a form which has several comboboxes with their control source
based on a column in the table. the user selects options from the comboboxes
and then clicks the 'product search' button. When this button is pressed, a
SQL query is built based on the values in the boxes. Next, the sql query is
run and the results are placed into a listbox (this may be where I'm doing
something wrong) by setting the listbox rowsource to the sql query ordered
by a few of the more important columns _but not all of them_.

Finally - and this is the part I just cannot figure out - I want the user to
be able to double click on an item in the listbox and have all of the columns
associated with that record (which are not all displayed in the listbox) pop
up in a message box. Could someone please tell me how to do this. Does it
have something to do with bookmarks? I've also tried using a recordset, but
I'm obviously doing something wrong there too...

thanks in advance
-z

I've posted the code associated with the "product search" button below:

Private Sub Product_search_Click()
'On Error GoTo Err_Product_search_Click
Dim db As Database
Dim qdf As QueryDef
Dim strSQL, strSQLname, strSQLselectFrom, strSQLWhere
Dim firstSQLwhere As Boolean

' this if condition requires that at least one combobox has a valid entry
If IsNull(cbBrand.Value) And IsNull(cbPhase.Value) And
IsNull(cbLocation.Value) Then
' the following two lines clear out the listbox (I'm not sure it's
the best way to do it, but it works)
lbqrySearch.RowSourceType = "Value List"
lbqrySearch.RowSource = ""
MsgBox "You must select criteria for the search from the drop down
boxes.", vbExclamation, "ATTENTION"
Exit Sub
End If

Set db = CurrentDb
strSQLname = "qrySearch"
Set qdf = db.QueryDefs(strSQLname)
strSQLselectFrom = "SELECT [myTable].* FROM [myTable] "
strSQLWhere = "WHERE "
firstSQLwhere = True

' this multi-nested 'for loop' builds the SQL statement. To add another
search criteria, all the user has to do is add another
' combobox to the form and make sure that the row source is based off a
column in the table
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.ControlType = acComboBox Then
If Not IsNull(ctrl.Value) Then
If strSQLWhere = "WHERE " Then
strSQLWhere = strSQLWhere & Mid(ctrl.RowSource,
InStr(ctrl.RowSource, "DISTINCT") + Len("DISTINCT"), InStr(ctrl.RowSource,
"FROM") - InStr(ctrl.RowSource, "DISTINCT") - Len("DISTINCT")) & " = Forms!"
& Me.Name & "!" & ctrl.Name
Else
strSQLWhere = strSQLWhere & " AND " &
Mid(ctrl.RowSource, InStr(ctrl.RowSource, "DISTINCT") + Len("DISTINCT"),
InStr(ctrl.RowSource, "FROM") - InStr(ctrl.RowSource, "DISTINCT") -
Len("DISTINCT")) & " = Forms!" & Me.Name & "!" & ctrl.Name
End If
End If
End If
Next ctrl

strSQL = strSQLselectFrom & strSQLWhere & ";" ' create the SQL query
string
qdf.SQL = strSQL

'still not totally sure how to list all the controls in the form
Forms!Analytical!lbqrySearch.RowSourceType = "Table/Query"
lbqrySearch.RowSource = "SELECT qrySearch.Location, qrySearch.Brand,
qrySearch.[Part Name], qrySearch.[Stationary Phase] FROM qrySearch ORDER BY
[Brand], [Location], [Part Name], [Stationary Phase];"
lbqrySearch.SetFocus

Set qdf = Nothing
Set db = Nothing

Exit Sub

Err_Product_search_Click:
MsgBox Err.Description
Resume Next 'Exit_Product_search_Click


End Sub
Click to expand...
 
Here's the problem - all of the records are unique when using all of their
fields (at least - i think, i'm checking with the person who would know)...
however, when someone searches for a product that matches certain criteria
(only a small selection of particularly relevant fields), multiple matches
will happen depending on the criteria. In that case, the row source of the
listbox will be set to the query with SELECT and ORDER BY statements. This
makes it so taht the listbox only shows the information that people mostly
care about... then, if they want more information about a particular
selection, then can double click on that entry and all of the other
information will pop up in a message box.

So, it's possible that the information in the 4 columns per item included in
the listbox would be identical to another entry in the listbox, but the
actual record (in the database) is different... does this make more sense?
(for example, it's possible to imagine two 9x3" red boxes with a 1" blue lid
and the only difference between these two things is where each of them is
located - the first in room A, and the second in room B)

more thoughts?
-Z
 
Back
Top