Selecting Records through a form for a Query

[susanmgarrett]

I have a problem I should be able to solve, but I can't think my way around it.

I need to automate a series of tasks to perform the following:

1. The user clicks a button on a menu form and arrive at a select records form.
2. The user makes choices regarding the contents of certain fields (blank, not blank, equals a text string, etc.) and presses OK.
3. On the OK click, the fields are applied to a query, the results of which are exported in an excel spreadsheet.

I've been doing step 2 and 3 by hand in that I alter the selections in the design view of the query and save it. I then run a macro that deletes the data in the current table, runs the append query with the new criteria, and creates an excel spreadsheet of the new data.

However, I now have to make this functionality available to a non-tech person, thus the need to create a GUI menu that can launch a form on which the user can select options and with a single click, launch the macro.

I've tried adapting select forms that I've used with forms and reports using the doCmd.openquery, but they will only work for select or crosstabs, not appends. I considered having the form create the query and then qrite the append query off that, but I can't seem to get that to work.

Can anyone give me some direction on where I can take this?

Thanks.

Susan

 

[llkhoutx]

Dynamically build the SQL for the form filter based upon user selections, e.g.

dim sfilter as string
sfilter =""
if me!Field1 <> null then _
sfilter = "[fieldname1]=" & me!field1

if me!field2 <> null then
if len(sfilter) > 0 then
'sfilter already has a field filter
sfilter = sfilter & " AND [fieldname2]=" & me!field2
else
'nothing stored in filter yet
sfilter = "[fieldname2]=" & me!field2
end if
end if

etc. 'note different datatypes require different concancenation

'check you work with the debugger
debug.print sfilter

me.Filter = sfilter
me.FilterOn = True
me.requery