Apply filter error by append query

[tomtom1126]

Dear All,

I am the new in VBA.

Below VBA use to filter the Query or Table by using the FORM with the combobox, it's fine until I changed to open (Append Query), the below error message coming up.

( Then action or method is invalid because the form or report isn't bound to a table or query).

Anyone can help, please !

Private Sub cmdUpdateData_Click()
On Error GoTo Errorfound
RecordSource = tblHandlingTemp
Const MESSAGETEXT = "Please Input the Date Range or Select the Vessel Information."
Dim strCriteria As String
If IsNull(Me.txtStartDate) Or IsNull(Me.txtEnddate) Or IsNull(Me.txtVsl) Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
Else
strCriteria = BuildCriteria("hkycd", dbText, Me.txtTml) & _
" and hketb >= #" & Format(Me.txtStartDate, "yyyy-mm-dd") & _
"# and hketb < #" & Format(Me.txtEnddate, "yyyy-mm-dd") & "#+1"
strCriteria = strCriteria + " And " + BuildCriteria("hkvsl", dbText, Me.txtVsl)

DoCmd.OpenQuery "qrytest"
DoCmd.ApplyFilter , strCriteria

Errorfound:
MsgBox Err.Description

End If
End Sub

 

[Minty]

What does the BuildCriteria() function do? The syntax for a select query and an Append query are very different, you couldn't simply replace Select with Append.

 

[tomtom1126]

HI Minty,

The "BuildCriteria() function" is the filter to look for the combobox (txtTml), below is fine with run this (DoCmd.OpenQuery "Normal Query"), but when I replace the "Append Query", error come up.

Any suggestion?

In this VBA, I want to filter the temp table by running the query to result the period of time, those data should be update to the existing table.

Thanks for your hlep!

Tom

 

[Minty]

Can you debug.print strCriteria and also your finished sql - I can't see the append query so can't guess why it won't work.
The code posted doesn't make much sense.

 

[tomtom1126]

HI, Minty
code above filter the selection in the combobox, append query set with the name "qryTest" and I try to call it by DoCmd.OpenQuery "qrytest"
DoCmd.ApplyFilter , strCriteria

If the above is not much sense, can you help to advise the way to filer and call append the result to the existing table.

Thanks you so much for your inforamtion.

Tom

 

[Minty]

You can't apply a filter to an append query after it's been run, if that's what you are trying to?

You need to apply your criteria to the append query then run it. Like I said we will need to see the SQL of the Append query, and you will either need to add parameters to it or build the SQL for it based on your existing function Then run it as an append query.

 

[tomtom1126]

Dear Minty,

Thanks for your reply, let forget the code before.

Can you simply advise me the code as below criteria with million thanks.

1. Table "Temp" data range (15Jan ~ 15Mar)
2. I want to filter the "Temp table" by select fm the combobox (e.g. date range fm 01Feb~28Feb).
3. Filter result will be append to the existing table "Database Table".

Thanks again for your great hlep.

Regards
Tom