Displaying the result of a query in a form 1

[BajanPOET]

I'm trying to create an SQL statement that shows all of the records in the Requisition table (and the associated Items table) for Requisitions that are not authorized

I've already gotten the query to display the info that I need - this code shows all the records that are related to unauthorized requisitions

SELECT DISTINCTROW Requisition.ReqNo, Requisition.ReqDate, Requisition.SupplyAt, Requisition.VoteChargeable, Items.Qty, Items.Description, Items.UnitPrice, Items.TotalPrice, Items.Supplier, Items.Comments
FROM Requisition INNER JOIN Items ON Requisition.ReqNo = Items.ReqNo
WHERE (((Requisition.Authorized)=False));

but how do I run the query and bind the result set to a Requisition form with an Items subform?

What? Who? ME????

 

[AlexCuse]

This is a kind of broad question. Do you want the query to display one record at a time, or in the typical query output style?

For one record at a time, which is where I think you're going with this, you need to set up the form with your query as the record source, then set up text boxes and other objects that are bound to fields from teh query to display the data.

Hope this helps,

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[dhookom]

You can set a subform Source Objectproperty to your query:

Me.sfrmYourSfControl.SourceObject = "Query.YourQueryName"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

And furthermore, have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Thanks guys.... setting the query as the record source helped.


I'll check out all the other ideas, too...


Now, what I need is to tweak my code I realize that all the requisition data is repeated for every line item; I mean, when I look at the data from the query all the requisition data like Req#, ReqDate, etc. is repeated for every record when there is more than one related record in the Items table. The problem with this is not evident until I try to navigate to the next record.

Let me try to illustrate exactly what I mean.

Requisition 62
Date today
etc.

Line items
1. Item 1
2. Item 2
3. Item 3.

and
Req 63
Date Today
etc

Line Items
1. Item 1
2. Item 2

My code above will give me these records
Req Date Item
62 Today Item 1
62 Today Item 2
62 Today Item 3
63 Today Item 1
63 Today Item 2

When I try to display this info in the Form that I have created where the controls are linked to the corresponding fields, I have to press the next button 3 times to get past the three Req 62 records.

How do I create a query that shows a requisition at a time so that all the Items info is intact, but it doesn't make my users have to click past all the excess data? If I take out the Items table I don't get the repeated data, but the Items data won't exist.

What? Who? ME????

 

[dhookom]

Consider creating a subform based on this query:

SELECT *
FROM Items;

Then create a main form with a record source of:

SELECT DISTINCTROW Requisition.ReqNo, Requisition.ReqDate, Requisition.SupplyAt, Requisition.VoteChargeable
FROM Requisition
WHERE Authorized=False;

Add the subform to the main form and set the Link Master/Child properties of the subform control to ReqNo.





Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A common way is to have a mainform bound to a query based on Requisition and a continuous linked subform bound to a query based on Items.
Feel free to have a look at the Northwind sample database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

That worked... thanks!

What? Who? ME????