Cant seem to get DoCmd.ApplyFilter to work

[Runawaygeek]

Hi all,

This is my code:

Private Sub Command10_Click()

Dim Path As String
Dim Af As String
Dim AL As String
Dim DT As String



Path = "C:\"
Af = Affiliatebx  'A box on the form that give the client name
XL = ".xls"
DT = Format(Now, "DDMMYYYY")


DoCmd.Close 'this is here as the Readonly, does not seem to be working

DoCmd.OpenQuery "ImplementationAndDevelopment_Output", acViewNormal, acReadOnly
DoCmd.ApplyFilter , "[Aff_ID] = IDBX" 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ImplementationAndDevelopment_Output", Path & Af & "_" & DT & XL, True

DoCmd.Close
DoCmd.OpenForm "Implementation_And_Development", acNormal

Shell "C:\Windows\explorer.exe """ & Path & "", vbNormalFocus

End Sub

So the file outputs fine, with the exception of the filter not working.

DoCmd.ApplyFilter , "[Aff_ID] = IDBX"

[Aff_ID] = the Column on the Query that contains the ID

IDBX = The Box on the form that contains the ID

The form only shows one client record at a time, the table shows the history of details associated with that Client.
My code, exports the entire Query.

Apart from the odd code layout, what have i got wrong?

Thanks,
Ben

 

[Runawaygeek]

Also, which acSpreadsheetTypeExcel12 should i be using?

It gives me errors, but if i use .xlsx as the extention, Excel wont open it?

Office16

Thanks

 

[Runawaygeek]

Not to worry, I tried another approach and it worked.

Private Sub Command10_Click()

DoCmd.SetWarnings False


Dim Path As String
Dim Af As String
Dim AL As String
Dim DT As String

Dim SQLDelete As String
    SQLDelete = "Delete * from ImpAndDevTbl"
    DoCmd.RunSQL SQLDelete

DoCmd.OpenQuery "ImplementationAndDevelopment_Output", acViewNormal, acAdd

Path = "C:\"
Af = Affiliatebx
XL = ".xlsx"
DT = Format(Now, "DDMMYYYY")

DoCmd.OpenTable "ImpAndDevTbl", acNormal, acEdit
DoCmd.ApplyFilter , "[Aff_ID] =" & Affiliate_ID_BX

Dim SQLEDIT As String
    SQLEDIT = "DELETE * FROM ImpAndDevTbl WHERE [Aff_ID] <> " & Affiliate_ID_BX
    DoCmd.RunSQL SQLEDIT


DoCmd.TransferSpreadsheet acExport, , "ImpAndDevTbl", Path & Af & "_" & DT & XL, True
DoCmd.Close

Shell "C:\Windows\explorer.exe """ & Path & "", vbNormalFocus

DoCmd.SetWarnings True

End Sub

I am sure the code could be better, but it works!

 

[MarkK]

That approach may cause your Db to bloat, since it looks like you are copying, deleting, exporting, and finally deleting records. Space will be allocated for all the copies, and not de-allocated without a compact & repair.

The approach I would take is to programmatically create exactly the query I need, and use it to export the records it selects from my permanent storage, so consider....

Private Sub Command10_Click()
   [COLOR="Green"]'this is our query name[/COLOR]
   const QN as string = "Query99"
   [COLOR="Green"]'this is the exact SQL text we need, except for the ID value we'll specify later[/COLOR]
   const SQL as string = "SELECT * FROM YourTable WHERE YourID = "

   [COLOR="Green"]'check if the query we want exists, and if so, delete it[/COLOR]
   if dcount("*", "MSysObjects", "Name = '" & QN & "'") then currentdb.querydefs.delete QN
   [COLOR="Green"]'create the new query, with the exact SQL we need, tacking on the ID in the WHERE clause[/COLOR]
   currentdb.createquerydef QN, SQL & me.txtYourID

   [COLOR="Green"]'execute the export using the new custom query...[/COLOR]
   docmd.transferspreadsheet acExport, , QN, ...
End Sub