Form Value to Parameter in Query

P

Pete Dochers

I am using 2 input boxes (startdate and enddate) on a Supplier Form as
criteria in a query so that I can display purchases between these dates.

I also have the above showing by supplier.

When the queries are first created, it all seems to work ok and reports can
be run off the queries.

However, since closing and then opening the dbs, the queries prompt me for
criteria - instead of taking them off the open Supplier Form.
All parameters are asked for - Supplier_ID, startdate and enddate.

As if the Supplier form isn't open.

What can I do? It happens every time.

Cheers
 
J

John Vinson

However, since closing and then opening the dbs, the queries prompt me for
criteria - instead of taking them off the open Supplier Form.
All parameters are asked for - Supplier_ID, startdate and enddate.

As if the Supplier form isn't open.
Click to expand...

Please post the SQL view of the query. The correct syntax for the
criteria is

[Forms]![NameOfTheForm]![NameOfTheControl]

and if any of these are misspelled or do not refer to a control on an
open form, you'll get a prompt.

John W. Vinson[MVP]
 
P

Pete Dochers

SELECT Stock.Stock_ID, Stock.Artist_ID, Stock.[Picture Name],
Stock.Description, Stock.[Supplied Price ex-VAT], Stock.[Ticket Price
ex-VAT], Stock.[Date Arrived], Stock.Sold, Stock.[VAT Rate], Purchase.[Sold
Price], Purchase.Date AS pDate, Purchase.Customer_ID, Purchase.Purchase_ID,
Stock.[VAT For Supplier], Supplier.[Supplier Name], Supplier.[Contact Name],
Supplier.[Number/Name], Supplier.Phone, Supplier.Supplier_ID
FROM Supplier INNER JOIN (Stock INNER JOIN Purchase ON Stock.Stock_ID =
Purchase.Stock_ID) ON Supplier.Supplier_ID = Stock.[Supplier_ ID]
WHERE (((Purchase.Date) Between [Forms]![Supplier]![startdate] And
[Forms]![Supplier]![enddate]) AND
((Supplier.Supplier_ID)=[Forms]![Supplier]![Supplier_ID]));


This query shows the purchases by supplier and between dates on the form.

Another thing I have noticed is that command buttons on the Supplier form
don't work and say that they need a Return has no GoSub. These were also
working correctly before and are command buttons for queries that also run
off the Supplier form.

Hope you can help.

Peter

John Vinson said:
However, since closing and then opening the dbs, the queries prompt me for
criteria - instead of taking them off the open Supplier Form.
All parameters are asked for - Supplier_ID, startdate and enddate.

As if the Supplier form isn't open.
Click to expand...

Please post the SQL view of the query. The correct syntax for the
criteria is

[Forms]![NameOfTheForm]![NameOfTheControl]

and if any of these are misspelled or do not refer to a control on an
open form, you'll get a prompt.

John W. Vinson[MVP]
Click to expand...
 
P

Pete Dochers

Right here is the bizarre thing - when I open up the vb page to view the
macro problem with any button through the event procedure - i close that and
the queries work...........!!!!!!!

What is going on? I can't expect users to do this every time surely?..
 
Top