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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|