Using multiselect listbox to get return for query (Access03)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote