Thanks!
Public Function SimpleCSV(strSQL As String, _
Optional strDelim As String = ",") As String
No, you don't modify the module/function at all. You just pass the delimiter you want to use. The default is a comma. To use a new line, you can use Chr(13) & Chr(10). For example:
SimpleCSV("SELECT...", Chr(13) & Chr(10))Perfect thank you. Lastly, I want to include the combo box selections as part of the WHERE clause in the SimpleCSV() function. Currently it is concatenating all ASDs associated with the control, regardless of the filters.
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & "' & (([Pivoted Data].Place)=[Forms]![Pass Percentage Form]![Place_combo]) AND (([Pivoted Data].Unit)=[Forms]![Pass Percentage Form]![U_combo]) AND (([Pivoted Data].[The_Date])=[Forms]![Pass Percentage Form]![Date_combo])",Chr(13) & Chr(10)) AS ASDsHi. What I would do is use the AfterUpdate event of each Combobox to update the Record Source of the subform. You would want to concatenate the value from the combo and not just include a parameter in the SQL statement (or you will go back to the original error that started this thread).
Hey there. Currently, for each combo box AfterUpdate even I have a:
No, you would replace it with something like:
Dim strSQL As String
'need some logic stuff in this section for constructing the ASDs based on selected comboboxes
strSQL = "SELECT..." 'incorporating the result of the above logic test
Me.RecordSource = strSQLHmmm, I'm sorry I guess I'm just a little lost on the logic I would use there.
Okay, based on the original file you sent me, here's my best guess as well. I only added the first combo. I can't tell if the result is correct though, since I am not familiar with your data. Hope it helps...
Hey. I looked at the results, I don't think anything changed from before. Probably because the ASDs mostly rely on the Unit combo box, so code for that box might have more of an effect on the results.
Right. Don't look at the result; but rather, on the technique I tried to implement. Remember, your final SQL was different than mine because you're using a different set of data. For example, when I tried to copy the "final SQL" you posted earlier into the sample file I am using, nothing showed up. So, try to disregard what my data result is, but concentrate on how you can apply what I'm trying to do on this sample db into your db. As a test, you could try trimming down the data in the sample db to see if anything changes. For example, remove some records for a specific place for a specific Control ID. One thing I can tell in the last file I posted is the Pass% column is changing, so it is having "some" effect to the data behind the form. Cheers!
Yes its definitely changing to filter the right things! Just not working correctly on the ASD column.
SimpleCSV("SELECT DISTINCT ASD FROM [Pivoted Data] WHERE [Control ID]='" & [Control ID] & " AND [Unit] = '" & [Unit] & "'",Chr(13) & Chr(10)) AS ASDsHi. As much as possible, I would not advise modifying the function. You should be able to pass to it a valid/modified SQL statement and just let it do its work. Let me see if I can play with the data I have to see if the ASD part changes based on places.
Alright thanks for the help - I'll default to your expertise.
What exactly did you change?
I removed some records to make a big difference in what could show up on the form, just to show that the code is doing "something." However, I guess I didn't remove enough. Try the attached file instead. When you first open the form, you get 14 rows and 5 ASDs for CA-001. If you select Place 1 from the Combobox, you get 11 rows and only 4 ASDs for CA-001.
Ah okay yes I see that now. So for more context, when you first open the form nothing should display. Only when you select the Place/Unit/Date boxes should it display the results for the controls tested at that Place, only for the selected unit and date.
Hi. Understood. But, I was just trying to show you how you could apply your own "rules" to the code. So, you should be able to adapt what I did to the next combobox, right? Since I only did the first combo, which has nothing to do with Units, I had to mess with the data. Once you apply the same technique to the next combobox, you should be able to use the full set of data. Since I didn't know the actual requirements in the first place, I just decided to work on the first combobox. To display nothing on the form until after a selection is made in the combobox, just delete the Record Source for the form in Design View.
Ah yes I gotcha. I implemented the same code to the Unit combo box and it worked! The ASDs were displaying correctly, for the correct units, for the correct places. The only thing thats wonky now is the scores are totally over the place, but I will keep looking into this. I tried implementing the code for the date combo as well but that did not fix it.
