Insert Into / Append Query using Filtered VBA/SQL as Recordsource

[RangerAlex]

I wrote this code a while ago and am now picking up this project again; however, I'm not well versed in VBA, but at least then I was good enough to do some basic things. Here is an overview of what I am trying to accomplish:

I have a primary form and a subform. The primary form has three combo boxes which filter the data on the subform through VBA code. The relevant portion of the code is shown below. My goal is to allow the user to filter the data through the combo boxes and then export the filtered results to a separate table through an append query. This will stage the filtered results to be exported to excel.

I cannot figure out through VBA how to do this. It cannot be that difficult, but I have not been able to find a clear answer. The trick is I want the results to be filtered based on the combo boxes. All append or INSERT INTO articles I have found show how to do it using hard coded results.



Any help would be greatly appreciated. I've been stuck on this for about two weeks or so.






'Primary SQL Code that serves as the subforms recordsource

Private Sub BackdaterSQL()

SQL = "SELECT tblBackdater.BackdaterID, tblBackdater.Backdater_Project0IDfk, tblBackdater.Backdater_Project1IDfk, tblBackdater.Backdater_Project2IDfk, tblBackdater.Backdater_Task, " _
& "tblBackdater.DaysPriorIDfk_StatDate, tblBackdater.DaysPriorIDfk_DueDate, tblBackdater.DaysPriorIDfk_SnoozeUntil, tblBackdater.Backdater_DaysAhead_ReminderTime, tblBackdater.Backdater_OwnerIDfk, " _
& "tblBackdater.Backdater_IncludeInExport, lkqDaysPrior.DaysPrior_Number FROM qryBackdater " _
& WhereStatement & "ORDER BY lkqDaysPrior.DaysPrior_Number ASC;"

Debug.Print SQL

Me.frmEntry_sub.Form.RecordSource = SQL
Me.frmEntry_sub.Form.Requery

End Sub

'Checks to see if combo boxes are activated and then combines result into a where clause

Private Sub ComboBoxFilter()
Dim Part1cbo As String
Dim Part2cbo As String
Dim Part3cbo As String

If Nz(Me.[cboProject0], "") <> "" Then
Part1cbo = "tblBackdater.Backdater_Project0IDfk=" & Me.[cboProject0] & " And "
Else
Part1cbo = ""
End If

If Nz(Me.[cboProject1], "") <> "" Then
Part2cbo = "tblBackdater.Backdater_Project1IDfk=" & Me.[cboProject1] & " And "
Else
Part2cbo = ""
End If

If Nz(Me.[cboProject2], "") <> "" Then
Part3cbo = "tblBackdater.Backdater_Project2IDfk=" & Me.[cboProject2] & " And "
Else
Part3cbo = ""
End If

strcboWhere = Part1cbo & Part2cbo & Part3cbo

If Nz(strcboWhere, "") <> "" Then
strcboWhere = Left(strcboWhere, Len(strcboWhere) - 5)
Else
strcboWhere = ""
End If

WhereStatement = "Where (" & strcboWhere & ")"

Debug.Print WhereStatement

End Sub

[davegri]

Unless you have a global variable defined for WhereStatement, one sub has no idea of the other sub's value given to it. Try using a tempvars as indicated, as tempvar values are available to all subs and functions.
It also appears that you do not have OPTION EXPLICIT as the second line in the form code module. (strcbowhere is not dimmed)

I wrote this code a while ago and am now picking up this project again; however, I'm not well versed in VBA, but at least then I was good enough to do some basic things. Here is an overview of what I am trying to accomplish:

I have a primary form and a subform. The primary form has three combo boxes which filter the data on the subform through VBA code. The relevant portion of the code is shown below. My goal is to allow the user to filter the data through the combo boxes and then export the filtered results to a separate table through an append query. This will stage the filtered results to be exported to excel.

I cannot figure out through VBA how to do this. It cannot be that difficult, but I have not been able to find a clear answer. The trick is I want the results to be filtered based on the combo boxes. All append or INSERT INTO articles I have found show how to do it using hard coded results.

Any help would be greatly appreciated. I've been stuck on this for about two weeks or so.






'Primary SQL Code that serves as the subforms recordsource

Private Sub BackdaterSQL()

SQL = "SELECT tblBackdater.BackdaterID, tblBackdater.Backdater_Project0IDfk, tblBackdater.Backdater_Project1IDfk, tblBackdater.Backdater_Project2IDfk, tblBackdater.Backdater_Task, " _
& "tblBackdater.DaysPriorIDfk_StatDate, tblBackdater.DaysPriorIDfk_DueDate, tblBackdater.DaysPriorIDfk_SnoozeUntil, tblBackdater.Backdater_DaysAhead_ReminderTime, tblBackdater.Backdater_OwnerIDfk, " _
& "tblBackdater.Backdater_IncludeInExport, lkqDaysPrior.DaysPrior_Number FROM qryBackdater " _
& Tempvars!tvWhereStatement & "ORDER BY lkqDaysPrior.DaysPrior_Number ASC;"

Debug.Print SQL

Me.frmEntry_sub.Form.RecordSource = SQL
Me.frmEntry_sub.Form.Requery

End Sub

'Checks to see if combo boxes are activated and then combines result into a where clause

Private Sub ComboBoxFilter()
Dim Part1cbo As String
Dim Part2cbo As String
Dim Part3cbo As String

If Nz(Me.[cboProject0], "") <> "" Then
Part1cbo = "tblBackdater.Backdater_Project0IDfk=" & Me.[cboProject0] & " And "
Else
Part1cbo = ""
End If

If Nz(Me.[cboProject1], "") <> "" Then
Part2cbo = "tblBackdater.Backdater_Project1IDfk=" & Me.[cboProject1] & " And "
Else
Part2cbo = ""
End If

If Nz(Me.[cboProject2], "") <> "" Then
Part3cbo = "tblBackdater.Backdater_Project2IDfk=" & Me.[cboProject2] & " And "
Else
Part3cbo = ""
End If

strcboWhere = Part1cbo & Part2cbo & Part3cbo

If Nz(strcboWhere, "") <> "" Then
strcboWhere = Left(strcboWhere, Len(strcboWhere) - 5)
Else
strcboWhere = ""
End If

Tempvars!tvWhereStatement = "Where (" & strcboWhere & ")"

Debug.Print WhereStatement

End Sub