Listbox and query issue

[Rat1sully]

It's been a long time since I've used Access but I now find myself producing a database at work and i'm provided with Access 03

I'm currently trying to run a query from a value selected in a list box which I will want to enable multiselect on but as I can't even get it to run on one value I will worry about that later

Option Compare Database
Option Explicit
Private Sub ClassSelect_Click()
On Error GoTo Err_ClassSelect_Click
    Dim db As DAO.Database
    Dim param As String
    Set db = CurrentDb()
 
 
    Dim qdf As DAO.QueryDef
        Set qdf = db.QueryDefs("Class Output")
        qdf.Parameters("SlctClass") = Forms!ClassOutput!List0
        qdf.Execute
 
Exit_ClassSelect_Click:
    Exit Sub
Err_ClassSelect_Click:
    MsgBox Err.Description
    Resume Exit_ClassSelect_Click
 
End Sub

apologies for being a bit of a n00b here but i haven't used Access since 2003

edit: error it displays is cannot execute select query

 

[spikepl]

1. Don' panic but think
2. What does a SELECT query do, and where does the output go (hint: read the error message again)
3. A listbox set to multiselect will have Null as value. It therefore takes more than that. Try Google, it's not that difficult

Kudos for actually listing the error messsage. So many ppl post "I got an error" and leave it as an exercise for the few willing readers to guess what and where.

 

[Rat1sully]

ta for the quick reply

I'm guessing I need something like qdf.OpenRecordset where I've got qdf.Exercute? but then it does nothing think I'm missing something here

I'm only trying to get it to open for now to do a visual check that it's pulling through the expected results.

So far today I've managed to teach myself to get a button press to clear all the tables then import updated data from .xls files. So I'm quite pleased with my progress given it's been nearly 10 years since I had to deal with the horrors of VBA

I've got a guide open for the multiselect but not going to worry about that until i can get it to report 1 item properly

 

[spikepl]

It seems you are muddling through in the right direction quite well on your own. Good on yer

 

[Rat1sully]

perseverence seems to have got me through in the end managed to produce the following, seems like a clunky way of doing things though any advice on neatening it up a bit?

Option Compare Database
Option Explicit
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
    Dim db As DAO.Database
    Dim varList As Variant
    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim strCriteria As String
 
    For Each varList In Me!lstClass.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lstClass.ItemData(varList) & "'"
    Next varList
 
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list", vbExclamation, "Nothing to find!"
    Exit Sub
    End If
 
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Class Output")
    strSQL = "SELECT [ME List].[Equipment Tag], [Platform List].Vessel, Class.Class, [Ship Fit Data].[Quantity fitted]" & _
        "FROM ([ME List] INNER JOIN [Ship Fit Data] ON [ME List].[Equipment Tag] = [Ship Fit Data].Equipment)INNER JOIN (Class INNER JOIN [Platform List] ON Class.Class = [Platform List].Class) ON [Ship Fit Data].Vessel = [Platform List].Vessel;"
 
    qdf.SQL = strSQL
    DoCmd.OpenQuery "Class Output"
    Set db = Nothing
    Set qdf = Nothing
 
Exit_Command2_Click:
    Exit Sub
Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
 
End Sub

Edit: besides using a proper naming convention for things well aware of that

 

[Rat1sully]

Ok not as solved as I thought having problems getting the SQL statement right any ideas?

Private Sub butDisplay_Click()
On Error GoTo Err_butDisplay_Click
    Dim db As DAO.Database
    Dim varList As Variant
    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim strCriteria As String
                 
    For Each varList In Me!lstClass.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lstClass.ItemData(varList) & "'"
    Next varList
    
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list", vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    
    strSQL = "SELECT * FROM tblClass.Class " & "WHERE tblClass.Class IN(" & strCriteria & ")"
    
    strSQL = strSQL & "SELECT [tblMEList].[Equipment Tag], [tblPlatformList].Vessel, tblClass.Class, [tblShipFitData].[Quantity fitted]" & _
            "FROM ([tblMEList] INNER JOIN [tblShipFitData] ON [tblMEList].[Equipment Tag] = [tblShipFitData].Equipment)INNER JOIN (tblClass INNER JOIN [tblPlatformList] ON tblClass.Class = [tblPlatformList].Class) ON [tblShipFitData].Vessel = [tblPlatformList].Vessel;"
               
   
   
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryClassOutput")
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qryClassOutput"
    Set db = Nothing
    Set qdf = Nothing
Exit_butDisplay_Click:
    Exit Sub
Err_butDisplay_Click:
    MsgBox Err.Description
    Resume Exit_butDisplay_Click
    
End Sub

 

[Rat1sully]

Having re written the SQL as follows it now runs but returns no results even though the listbox is populated from tblClass anyone?

    strSQL = "SELECT [tblMEList].[Equipment Tag], [tblPlatformList].Vessel, tblClass.Class, [tblShipFitData].[Quantity fitted]" & _
            "FROM ([tblMEList] INNER JOIN [tblShipFitData] ON [tblMEList].[Equipment Tag] = [tblShipFitData].Equipment)INNER JOIN (tblClass INNER JOIN [tblPlatformList] ON tblClass.Class = [tblPlatformList].Class) ON [tblShipFitData].Vessel = [tblPlatformList].Vessel "
               
    strSQL = strSQL & "WHERE [tblClass].Class="" & strCriteria & "";"