Parameter Query No Results - Needs to Auto Create Record

[JudyKemp]

I have a parameter query works as long as there are related records in the
tables. My problem is I when the query results are "null" or no records
exist I want the query to automatically create a new record based on the
parameter input. Is this possible?

Example:

[CustomerID] criteria shows Like "*" & [Enter Customer ID:] & "*"

If this is a new record for this Customer my query returns no values. I
want the query to automatically create a new record for this Customer or show
all records related.

Your help is GREATLY appreciated!

 

[golfinray]

A query is not going to do that for you. You need an update or append query
to add the record for you. You would have to write some code to automate
that, but there is a simpler way. Build your append query and if your query
returns no results, have a command button to run the append query.

 

[John Spencer]

Not in a query. If you are using a form, you can do things there (with VBA)
to go to a new record and populate it with some data based on the value of the
parameter.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

It is possible, yes, first thing which come to mind is :


SELECT * FROM query1
UNION ALL
SELECT MAX([Enter Customer ID:]) FROM someTable WHERE NOT EXISTS (SELECT *
FROM query1)


Which assumes that your actual query, query1, has only ONE column AND that
there is at least one record in the someTable you will refer to (but
otherwise, it is irrelevant of which table you use, at that point., but you
CANNOT use query1, for someTable, since that query MAY not return even one
record).



Vanderghast, Access MVP

 

[Tom van Stiphout]

On Wed, 19 May 2010 16:21:01 -0700, JudyKemp

No, you'll have to do that in two steps. First test if records exist,
then based on the answer show the first record or a new record.

Depending on your exact situation sometimes you can get lucky. For
example if you have a form in FormView and you apply a filter, it will
automatically show the first matching record or if none found it will
show an empty record where you can enter new data.

-Tom.
Microsoft Access MVP