'On The Fly' Queries

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

Hi

I am trying to create a query that can be updated 'on the
fly'. What I want to happen is for the user to select an
option from a pop-up box and then the query pull out all
the records that relate to that particular option. The
hope then is that this can be tied into a mail merge to
make everything perfect.

Is this possible? I'm fairly new to this query stuff so
how would I do it?

Thanks for your help

Martin
 
Martin

One approach would be to create a form you use to "order" the report (a set
of mailing labels in Access would be a report). The report is based on a
query. The query points back to the "order" form to get its criteria. The
expression you'd use in the criterion cell of the field in the query's
design mode would look something like:

Forms!YourOrderFormName!YourSelectionControlName
 
Hi Jeff,

Just a little note to say thanks. That's working
perfectly now, you're a star!

Cheers

Martin
 
Hi

Maybe not as perfectly as I thought :(

I can't seem to tie in the 'on the fly' query I have with
the mail merge document I need. When I connect the
document to the database it shows all the tables and
queries apart from that one. Any ideas why this is?

Thanks

Martin
 
Martin

Are you saying that when you are in Word, you can't see the query? I don't
recall any mention of Word before. When you described doing a mail merge,
since you posted in an Access query newsgroup, I figured you were doing the
mailing labels in Access.
 
Hi Jeff

I am using word to do my mail merge. Basically I want to
store outstanding items on some applications we have in
the access database and then tie this into a word
document to create completed chase letters (with the
outstanding items and the address/name of the client).
This has been pretty easy to do so far through the use of
insert word fields, mauil merge fields, etc. The hard
part is this on the fly query.

I have overcome the issue relating to Word not seeing the
query by exporting the data through a macro to an excel
spreadsheet and running the mail merge from there. The
problems now are:

1.) The form I am using to specify the criterion in the
query is also updating the records - can this be
prevented.

2.) Is there an easier way to tie the query into word?
Is it possible to get rid of the excel "middle man"?

Any help would be greatly appreciated.

Thanks

Martin
 
Martin

You can use Access to automate Word directly, including a merge.

If your form on which you set selection criteria is updating the underlying
tables, unbind the fields/forms from the table.
 
Hi Jeff,

I managed to fix the problem using an overly complicated
system of creating a new record, letting the user input
the search, having it checked by the query and then
deleting the record when they exit the search form. I'd
like to know how this unbinding though as it sounds like
it will do the same job and yet be much less complicated.

How do I go about unbinding the fields on the form and
will the pop-up lists, etc, still have the values from
the original tables so the users can select them easily?

Sorry for my ignorance and thanks for the help

Martin
 
Martin

Each form and each control has a "source" property. A "bound" form points
to a table or a query as a source, and a bound control on the form points to
a (table or query) field, in that "source" property.

You can take a form that already is bound and unbind it by deleting the
source in that property, or you can create a new form without a source (a
typical "menu" form is an example of an unbound form).

You can add an unbound combobox to a form and make it's RowSource the same
as you would have if you had bound it. RowSource and ControlSource are two
different cats.
 
Hi Jeff,

Just a note to say thanks. That's working even better
than I had hoped now. You're a star!

Cheers

Martin
 
Back
Top