A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parameter Query No Results - Needs to Auto Create Record



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 12:21 AM posted to microsoft.public.access.queries
JudyKemp
external usenet poster
 
Posts: 2
Default Parameter Query No Results - Needs to Auto Create Record

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!
Ads
  #2  
Old May 20th, 2010, 01:45 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Parameter Query No Results - Needs to Auto Create Record

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.
--
Milton Purdy
ACCESS
State of Arkansas


"JudyKemp" wrote:

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!

  #3  
Old May 20th, 2010, 01:48 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Parameter Query No Results - Needs to Auto Create Record

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

JudyKemp wrote:
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!

  #4  
Old May 20th, 2010, 02:01 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Parameter Query No Results - Needs to Auto Create Record

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


"JudyKemp" wrote in message
...
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!


  #5  
Old May 20th, 2010, 02:41 PM posted to microsoft.public.access.queries
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Parameter Query No Results - Needs to Auto Create Record

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

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


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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.