How to Query on a forms custom recordsource?

[markmarko]

On our Sales Order Entry Form, I've built a comprehensive 'filter' based on
concatenating strings into the WHERE of sql, which is then used as the
recordsource for the table, thereby 'filtering'. (I did this since some of
the criteria that's being filtered on is in subforms and other related tables
that aren't directly involved with the Sales Order form). I'll refer to this
as the Recordsource-Filter.

Now I need to build an append (make table) query, that makes a record in a
temp table, one record for each Sales Order, and then totals various
Quatities for various Sales Products (from tblSalesDetails) into appropriate
columns. This temp table will then be exported into an excel spreadsheet to
provide to our client.

I have built the Make-Table Query. Currently, it uses all the Sales Order
records. I need to somehow combine that query with the Recordsource-Filter
so that I can use the Recordsource-Filter to narrow down the list of jobs to
send through the Make-Table Query.

The sql of the Make-Table Query is already tremendously complex, and the
Recordsource-Filter sql is somewhat complicated as well...

And thoughts on how to go about this?

 

[Jeanette Cunningham]

Markmarko,
If possible, it will be much easier if you build the temp table manually
with all the necessary fields set up as you want.
Then it is reasonable easy to build the append query using the recordsource
filter you have already created.
Empty the temp table just before you run the append query each time.

Jeanette Cunningham

 

[Jeanette Cunningham]

Markmarko,
When you have the temp table setup, the next step is to work out how to get
the records you want to append to it.
I have found with experience, that is easier to learn how to do something by
making a test with a simple case.
I suggest you try to create an append query that uses a simple filter on the
subform.
When you have this working smoothly, then make the filter more complicated
to suit your situation.
If there are problems with the complicated scenario, you at least know that
the process works, you just need to adjust the sql for the filter.

Jeanette Cunningham

 

[markmarko]

Jeanette,

Are you saying the an append Query is easier than a Make-Table query?

I originally tried to make the table first, and append to it, but only one
field would appear in the end result. Many of the columns in my query were
expressions... Not sure if that made the difference. I imagine it was an
issue with field data-type mismatches, though I received no error indicating
so. Therefore, I went with the make table query, which performed with no
problems.

But, my big question is how to limit the record the query operates on, using
that sql RecordSource Query I have already built (which handles many funky
criteria choices).

 

[Jeanette Cunningham]

Yes,
the problem is with the append query you have built.
To troubleshoot, make the query simpler by eliminating the fields that are
likely to cause a problem.
If you don't have a clue as to which fields might be causing the problem,
simplify the append query right down to one field.
When this successfully appends to the temp table, add one more field.
When both fields append succesfully, add one more field and so on.
Eventually you will have an append query that works and appends just the
data you want.
If you find difficulty with a particluar field, post back and someone will
help.

Jeanette Cunningham

 

[markmarko]

In what sense is the Make-Table query more troublesome? I'm actually not
seeing any problems with it.

 

[Jeanette Cunningham]

I would expect that the problem is with the append query. I meant that I
wouldn't bother going to the trouble of creating a make table query if I
could simply create the table manually. Creating the table manually, it is
easier (in my opinion) to get the fields' data types, allow zero length
strings, any validation or indexes etc, set up how you want them.
Good luck on building the append query.

Jeanette Cunningham

 

[julie.vaillancourt]

I am trying to create a filter that sounds very similar to your
Recordsource-Filter. Would you be willing to share your code for it
with me?
Many thanks,
Julie