Export Records from a Form (1 Viewer)

jeran042 · Dec 14, 2017

I have a continuous for, with a txt search box that contains a sql statement.
the sql statement is as follows:

Code:

strLedger = "Select * from qryLedger_Detail where DESCRIPTION like ""*" _
            & strText & "*"" or VOUCHER like ""*" _
            & strText & "*"" or POLICY like ""*" _
            & strText & "*"" or ACCOUNT_NUMBER like ""*" _
            & strText & "*"" or NOTES like ""*" _
            & strText & "*"" ORDER BY [MO_DAY] DESC"

What I want to do is add a button to the form to export the results to an excel file, but unsure how to accomplish this.

I have tried docmd.OutPutTo by the limitations are, I am not matching a user input to a specific table field.
A second though I had was to use the docmd.setparameters, but am unsure if this is the best way to go, would anyone have any ideas or points in the best direction?

Very much appreciated,

static · Dec 14, 2017

I'd use Excel's copyfromrecordset which would probably go something like

Code:

with createobject("excel.application")
	with .open(filepath)
		.range("a1").copyfromrecordset me.recordset
		.close save:=yes
	end with
	.quit
end with

That's out of my head so probably not 100%.

Mark_ · Dec 14, 2017

I believe you may be appending too many "*"'s to your SQL. I would strongly advise viewing your strText after you've put it together. From the look, you are using

"Select * from qryLedger_Detail where DESCRIPTION like "**" or VOUCHER like "**" or POLICY like "**" or ACCOUNT_NUMBER like "**" or NOTES like "**" ORDER BY [MO_DAY] DESC"

More importantly, why not just pass

"Select * from qryLedger_Detail ORDER BY [MO_DAY] DESC"

as you seem to be accomplishing exactly the same with a much more Byzantine structure.

Export Records from a Form (1 Viewer)

jeran042

Registered User.

static

Registered User.

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread