Passing criteria to a query

[dat_guy]

Greetings all,

My problem is simple, and self-inflicted. First I will explain my setup in a general sense. File.mdb contains forms, queries etc... File_BE.mdb contains tables for current records, and File_BE_Archive contiains identical tables with old records.

I move records between tables File_BE and File_BE_Archive with queries. ie current to archive with append/delete queries.
Additionally, I have an append query to copy an old record from archive to current, when the need arises. This query itself works fine.

The problem occurs when I try to pass criteria to this query via vba code. Let me explain. The user pulls up a record from the archive table into a form, and clicks a button to copy this record to the active/current table. The code for the button is as follows:


Private Sub cmdRollback_Click()
On Error GoTo Err_cmdRollback_Click

Dim stDocName As String
'Dim stLinkCriteria As String

stDocName = "qryBanquetArchiveRollBack"
'stLinkCriteria = "[FunctionID]=" & Me![FunctionID]
Me.Filter = "FunctionID = " & Forms("frmBanquetArchive")!FunctionID
Me.FilterOn = True
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdRollback_Click:
Exit Sub

Err_cmdRollback_Click:
MsgBox Err.Description
Resume Exit_cmdRollback_Click

End Sub



If I try to use stlinkcriteria I get a type mismatch. And with the filter, I get nothing at all. Anyone have an idea what's wrong with my code?

thanx in advance
Dave

 

[dcx693]

You cannot pass critieria to a query like you can with a form. The DoCmd.OpenQuery method does not support a criteria argument.

You should consider creating a parameter query that references your form fields to get criteria. Use expressions like this in your query Critieria line:
Forms!frmBanquetArchive!FunctionID

 

[dat_guy]

Excellent! Exactly what I needed.

thank you much
Dave