Feeding a 'live' query...

[stevenb82]

Ok,

So this might be a little bit complicated, but I'm sure it must be possible..

I have a form that displays a customer and a subform that displays their purchase orders. If I wanted to be able to print a customers details and purchase orders on the fly, how would I do that?

I'm thinking maybe a query that is fed by the selected customer, that in turn feeds a report, that in turn can be printed..?

Make any sense? Hope so...

Thanks in advance

 

[boblarson]

1. A Query is always "live" unless it is set to be a Snapshot type.

2. Create a generic report which displays purchase orders.

3. Then you can open the report and pass a WhereCondition to it:

DoCmd.OpenReport "ReportNameHere", acViewPreview, "[CustomerID]=" & Me!CustomerID

See a sample of a generic report which can be opened in multiple ways here:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[Isskint]

You just answered your own question.

Create a report (same as your subform), then a report same as your Main form. Put the sub report on your main report (same process as forms) identifying the linked fields. Then on your FORM place a command button to open the report and set the criteria = to the link id on the main form (EG if your main form is customers, use the customer ID).

EDIT:

Evening Bob

 

[plog]

Actually you essentially recreate the form/sub-form as a report/sub-report. Include a button on your form to open that report to the ID that matches the form and you have done it.

 

[stevenb82]

yea, new to access guys, learning as I go, so I understand what is possible but not how to implement it in access, getting there though. I'll try your suggestions see if I can get it to work. Thanks guys!

 

[stevenb82]

Ok, so that advice isn't working.. Do I need to add an after update event or something?

If so, can someone give me the syntax.. New to VB, know it goes in there somewhere but I don't have the time to learn the entire language to get this down..

Thanks

 

[Isskint]

What have you got so far?
You need;
Report with sub report
Command button to print report (or preview)
OnClick VBA for command button to print (view) report EG

DoCmd.OpenReport "ReportNameHere", acViewNormal, "[CustomerID]=" & Me!CustomerID

Assuming [CustomerID] is the PK of your customer table.

 

[stevenb82]

Thanks for this mate, I know you don't have to take the time out of your day to help strangers, so mucho appreciated!

Ok, so far I have a table with multiple fields called tblCustomers, with CustID as the PK. Then I have a table called tblProducts with ProdID as the PK. Then I have an tblOrderlines table that combines both of those tables using the CustID and ProdID to record the info of each customers purchase.

I then have a form, one call CustomerForm (tblCustomers), that has an embedded subform called CustomerPurchases (tblOrderlines).

I have tried to make a report from the form, but I cannot link a field in the report to the form as a recordsource in the form properties.. has to be either a table or a query. I have also tried to link the CustID to a query using the simple [Forms]![tblName]![FormControl] command but it will not work..

What I have done is managed to make a report and link it to a command button on the form and print a report, but it prints every record in the tblCustomers instead of the single customer

 

[Isskint]

OK the basics are in place. From what you have said, you are trying to create a report based on the form. Is that correct? If so, as you have found, you need to base it on a table/query. So create the reports based on the same tables as your forms(main and sub). Put the subreport on the main report and you should be in business.

 

[stevenb82]

That's correct. Thanks again! Will try to get that going

 

[stevenb82]

Yea, so that works perfectly, thanks. But, do again, when I make the reports that way, the subreport displays the customers purchases, and the main report displays their details. But because I've based the reports on the entire table I can't limit the results of the report to a single customer, when I run it, it shows all of the customers in the table tblCustomers.. Do you know how to do that?

 

[Isskint]

have you included the WHERE criteria in the OpenReport command?

Can you post the OpenReport code?

 

[stevenb82]

No, I just put in the code that you gave me a couple of posts up.. I can see now when I check in the VB editing window that there are other criteria that I need to enter like the WHERE keyword etc. As I said, I don't know VB, if this were JAVA or SQL this would not be a problem, but I have been asked to do this in Access.. And I can't go against the person who has asked for it. If you could explain the syntax to me, I'm sure I could figure it out.. Really sorry about this..

 

[Isskint]

the format is as follows;

DoCmd.OpenReport "ReportNameHere", acViewNormal, "LinkField=" & Me!LinkField

Where LinkField is the name of the field that is unique to the main form and main report (EG. Customer ID). Dont forget if the LinkField is a string then you would need to use quotes.... "LinkField='" & Me!LinkField & "'"

 

[stevenb82]

Cool beans man..

Your teaching me a lot here, thanks..

Just one question about the "LinkField=" & Me!LinkField

Should I put "LinkField=CustomerID" & Me!LinkField

Or, do I need to add/change the Me!LinkField part? Or is that a generic keyword that basically links the preceeding code to this (Me) particular form control?

 

[stevenb82]

No I substitute LinkField= to CustomerID=... Is that right?

 

[Isskint]

yep, that should do it

 

[stevenb82]

Ok, that works perfectly, it prints the report from the form using a button..

But.. Thats actually not the question I asked, even though its very useful, so thanks..

What I wanted to do was this..

I have a form for inputting a customers details, including a subform for a customers purchases. The customer details part of the form are link to the customers table, and the customers purchases form is linked to the customers purchases table.

This form only shows a single customers details at a time.. even though there are multiple customers in the table, what I want to be able to do, rather than print the details of every customer I want to print just the customer which is displayed in the form at the time that I want to print it..

I think at this point I am just going to start learning VB..

 

[Isskint]

The OpenReport method paramaters are as follows;
OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

You want to set the WhereCondition - eg open this form where CustomerID = X. However the code i posted earlier put the criteria in the FilterName paramater, sorry.

So there is a comma missing. there should be 2 comma's after the acViewNormal, so try;
DoCmd.OpenReport "ReportNameHere", acViewNormal, , "CustomerID=" & Me!CustomerID

 

[stevenb82]

Ok, thanks..

Everything you have shown me so far is 100%..

I have figured out what is going wrong for me.. I am using forms that are embedded in navigation forms. When I try to use the functionality from a form outside of the navigation form everything works perfectly. When I use that same form from inside the navigation form it all goes to shit. For example. I have a criteria reference [Forms]![FormName]![FormControl].. the usual. But this will only work when the form is open/active. Inside a navigation form, a form isnt active! Whatever that is all about Microsoft!

Have you had any experience with this?