After spending a full day trying to get this done, i finally got this (unoptimized) code to work:
I know it's not the most efficient code, but then, I am not a programmer (Mech actually), so I'm happy it works. Its not too slow either. This was my first time using VBA, and definately my first function.
HOWEVER, any suggestions on how to make it better will still be appreciated.
In the end, I had to make 2 new tables, and the results go into a table, not a query. This is less thatn ideal, but it was the only way I could think to do it.
Code:
Function NextAs()
'variables here
Dim FormPartnoBox As String
Dim delSQL As String
Dim apndSQL As String
Dim subcritSQL As String
Dim subcritNullSQL As String
Dim i As Integer
Dim c As Integer
'variables end here
On Error GoTo NextAs_Err
'code starts here
DoCmd.SetWarnings False
'main table erase
'once only
delSQL = "DELETE NextResults.Results " & _
"FROM NextResults;"
'end once only here
FormPartnoBox = "Forms![Switch]![Search Materials]"
'loop here
'main table apppend
apndSQL = "INSERT INTO NextResults ( Results ) " & _
"SELECT Next.PARTNO " & _
"FROM [next] " & _
"WHERE (((Next.NEXTASMBLY) Like " & FormPartnoBox & "));"
DoCmd.RunSQL (delSQL)
DoCmd.RunSQL (apndSQL)
'for null test
delSQL = "DELETE NextResultsNullTest.Results " & _
"FROM NextResultsNullTest;"
apndSQL = "INSERT INTO NextResultsNullTest ( Results ) " & _
"SELECT Next.PARTNO " & _
"FROM [next] " & _
"WHERE (((Next.NEXTASMBLY) Like " & FormPartnoBox & "));"
DoCmd.RunSQL (delSQL)
DoCmd.RunSQL (apndSQL)
delSQL = "DELETE NextResultsNullTest.Results " & _
"FROM NextResultsNullTest " & _
"WHERE NextResultsNullTest.Results IN ( " & _
"SELECT NextResultsNullTest.Results " & _
"FROM NextResultsNullTest LEFT JOIN NextResults ON NextResultsNullTest.[Results] = NextResults.[Results] " & _
"WHERE (((NextResults.Results) Is Not Null)));"
DoCmd.RunSQL (delSQL)
subcritSQL = "IN (SELECT [NextResults].Results FROM [NextResults])"
subcritNullSQL = "IN (SELECT [NextResultsNullTest].Results FROM [NextResultsNullTest])"
'loop here
i = 1
c = 0
Do While Not i = 0
'main
apndSQL = "INSERT INTO NextResults ( Results ) " & _
"SELECT DISTINCT [NextResultsNullTest].Results " & _
"FROM [NextResultsNullTest] " & _
"WHERE ([NextResultsNullTest].Results Is Not Null) ;"
DoCmd.RunSQL (apndSQL)
'for null
apndSQL = "INSERT INTO NextResultsNullTest ( Results ) " & _
"SELECT Next.PARTNO " & _
"FROM [next] " & _
"WHERE (((Next.NEXTASMBLY) " & subcritSQL & ")) ;"
DoCmd.RunSQL (apndSQL)
'for null test
delSQL = "DELETE NextResultsNullTest.Results " & _
"FROM NextResultsNullTest " & _
"WHERE NextResultsNullTest.Results IN ( " & _
"SELECT NextResultsNullTest.Results " & _
"FROM NextResultsNullTest LEFT JOIN NextResults ON NextResultsNullTest.[Results] = NextResults.[Results] " & _
"WHERE (((NextResults.Results) Is Not Null)));"
DoCmd.RunSQL (delSQL)
'if not null then i = 0 here (if dcount implemented... could be slow)
If DCount("Results", "NextResultsNullTest") = 0 Then
i = 0
End If
'i = i - 1
c = c + 1
Loop
'''''''Debug tool:
'MsgBox ("Loops performed: " & c)
DoCmd.SetWarnings True
'code ends here
NextAs_Exit:
Exit Function
NextAs_Err:
MsgBox Error$
Resume NextAs_Exit
End Function