Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using multiselect listbox to get return for query (Access03)

    I have to combine data from two tables so I can use the information in a report. To do that I created a make table query from one table and an append query from the second table. Everything works except for the For Each section. This is an area I have limited experience with. What am I missing?


    Dim stDocName As String
    Dim strWhere As String

    'Error message if there are no selected records in the classes
    If Forms!frmReports!lstClasses.ItemsSelected.Count = 0 Then
    MsgBox "No classes are selected!", vbInformation
    Exit Sub
    End If

    For Each varItm In lstClasses.ItemsSelected
    strWhere = strWhere & ", " & Chr(34) & Me.lstClasses.ItemData(varItm) & Chr(34)
    Next varItm
    strWhere = "ClassName In (" & Mid(strWhere, 3) & ")"

    DoCmd.SetWarnings Off
    stDocName = "qryMakeTableCombined"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    stDocName = "qryAppendTableCombined"
    DoCmd.OpenQuery stDocName, acNormal, acEdit


    Thank you. Fay

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    You create a string strWhere that in itself is OK as far as I can see, but you don't do anything with it in the code as posted. Did you want to use it in the make-table query, or in the append query, or both? Or something else?

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    It needs to be used in both queries. I thought the strWhere = "ClassName... was where the selected material was passed to the query. So in fact what does that line do for me. I was trying to use code that you helped me with before. I thought I understood it, but I guess I didn't.

    You will be pleased that I was able to figure out a nonworking query based on the work you helped me with last weekend.

    Thank you.

    Fay

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    The queries won't pick up a string you create in code - they don't know it exists. But we'll work something out. Could you do the following?
    - Open the query qryMakeTableCombined in design view.
    - Select View | SQL.
    - Copy the SQL text you see to the clipboard (Ctrl+C) and paste it into a reply.
    - Do the same for the query qryAppendTableCombined.
    Thanks in advance.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    qryMakeTableCombined

    SELECT tblRegistrationLearner.LearnerID, tblClasses.ClassName, tblRegistrationLearner.ClassNumber, tblRegistrationLearner.ClassID, tblRegistrationLearner.CancelledNoShow, tblRegistrationLearner.DateTimeRegistered, tblRegistrationLearner.DateOfClassStart, tblRegistrationLearner.Grade INTO tblCombinedRegistration
    FROM (tblClasses INNER JOIN tblSession ON tblClasses.ClassID = tblSession.ClassID) INNER JOIN tblRegistrationLearner ON tblSession.ClassNumber = tblRegistrationLearner.ClassNumber;

    qryAppendTableCombined

    INSERT INTO tblCombinedRegistration ( LearnerID, ClassName, ClassNumber, ClassID, DateOfClassStart, Grade )
    SELECT tblRegIndepStudyLearner.LearnerID, tblClasses.ClassName, tblRegIndepStudyLearner.ClassNumber, tblRegIndepStudyLearner.ClassID, tblRegIndepStudyLearner.DateOfClassStart, tblRegIndepStudyLearner.Grade
    FROM tblRegIndepStudyLearner INNER JOIN tblClasses ON tblRegIndepStudyLearner.ClassID = tblClasses.ClassID;

    Thanks Fay

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    Try this. It constructs the SQL strings including the WHERE part in code, and uses DoCmd.RunSQL to execute them.

    Dim varItm As Variant
    Dim strSQL As String
    Dim strWhere As String

    'Error message if there are no selected records in the classes
    If Me.lstClasses.ItemsSelected.Count = 0 Then
    MsgBox "No classes are selected!", vbInformation
    Exit Sub
    End If

    For Each varItm In Me.lstClasses.ItemsSelected
    strWhere = strWhere & ", " & Chr(34) & Me.lstClasses.ItemData(varItm) & Chr(34)
    Next varItm
    strWhere = "ClassName In (" & Mid(strWhere, 3) & ")"

    DoCmd.SetWarnings False

    ' SQL for make-table query
    strSQL = " SELECT tblRegistrationLearner.LearnerID, tblClasses.ClassName, " & _
    "tblRegistrationLearner.ClassNumber, tblRegistrationLearner.ClassID, " & _
    "tblRegistrationLearner.CancelledNoShow, tblRegistrationLearner.DateTimeRegistered, " & _
    "tblRegistrationLearner.DateOfClassStart, tblRegistrationLearner.Grade " & _
    "INTO tblCombinedRegistration FROM (tblClasses INNER JOIN tblSession ON " & _
    "tblClasses.ClassID = tblSession.ClassID) INNER JOIN tblRegistrationLearner ON " & _
    "tblSession.ClassNumber = tblRegistrationLearner.ClassNumber WHERE " & strWhere
    ' Execute it
    DoCmd.RunSQL strSQL

    ' SQL for append query
    strSQL = "INSERT INTO tblCombinedRegistration ( LearnerID, ClassName, " & _
    "ClassNumber, ClassID, DateOfClassStart, Grade ) SELECT " & _
    "tblRegIndepStudyLearner.LearnerID, tblClasses.ClassName, " & _
    "tblRegIndepStudyLearner.ClassNumber, tblRegIndepStudyLearner.ClassID, " & _
    "tblRegIndepStudyLearner.DateOfClassStart, tblRegIndepStudyLearner.Grade " & _
    "FROM tblRegIndepStudyLearner INNER JOIN tblClasses ON " & _
    "tblRegIndepStudyLearner.ClassID = tblClasses.ClassID WHERE " & strWhere
    ' Execute it
    DoCmd.RunSQL strSQL

    DoCmd.SetWarnings True

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    That worked exactly like I needed it to. Thank you.

    I want to make sure that I am learning not just copying material.

    Needed to Dim a string to hold the information for the query. I only need one because first the make query is run and the string created there can safely be replaced with the string created in second query.

    You added the Where part to the Sql statement and finished by the DoCmd to run the sql.

    Question: When you helped me before to create a report we used the basic code that I sent in the first message. But in that case there was only one query involved. This time there will be three, the make query, append query, and report query. Why did we have to put the actual sql into the VBA this time?

    Would it be smart to change this to a Public Sub and call it when needed as I need to in different reports?

    Thanks for your help.

    Fay

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using multiselect listbox to get return for query (Access03)

    > Needed to Dim a string to hold the information for the query. I only need one because first the make query is run and the string created there can safely be replaced with the string created in second query.

    Correct.

    > You added the Where part to the Sql statement and finished by the DoCmd to run the sql.

    Correct.

    > Why did we have to put the actual sql into the VBA this time?

    Because the SQL is not fixed. There are other ways to do this, but this is a reasonably straightforward approach.

    > Would it be smart to change this to a Public Sub?

    As it is now, it's specific to one set of queries and one list box. It could be made more general, but whether that is worthwhile depends on your exact needs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •