Solved Running a report based on fields from a main form and a subform

[DeWaal]

Hi Experts

I need to get a report to work properly.

I have created a Query that contains fields of both my main table "TblCustomerInfo" that is my main form "FrmCustomer Info" as well as my subform tabel "TblCalloutsDetails" that is my subform "FrmCalloutsDetailsSubform"

The Query "QryCalloutandCustReport" consists of both the tables mentioned above AutoNumber Fields, "CustomerID" and "CalloutID"

The report is linked to a button and the Query has the following Criteria [Forms]![FrmCalloutDetailsSubform]![CalloutID] under the "CalloutID"

When i run the report it pops up this blok asking me to Enter Parameter Value.

When i type in the Parameter Value of my Subforms AutoNumber field then only the report runs.

Why does it do this and how can i fix it to automatically insert the current subforms AutoNumber?

Your help will be greatly appreciated.

 

[GaP42]

When you created the expression in your query: [Forms]![FrmCalloutDetailsSubform]![CalloutID]
Did you use the expression builder and intellisnse to help build it? If you did then is would have confirmed that you need to reference the CalloutID through addressing the CustomerInfo form first then the subform.
See this reference:
http://access.mvps.org/access/forms/frm0031.htm

 

[DeWaal]

Hi there

Thank you very much for your reply.

I'm pretty new to all this so I'm not 100% sure what you mean here.

How would i go about using the expression builder to sort this out?

 

[GaP42]

Do you have that expression in your query? Select it, right click - select build from the menu that appears - that is the Expression Buiilder.
Use the guide ref supplied to figure out how to build the expression in the buuilder.

Just an additional note - the query needs the explicit reference to the forms - ie do not use the shortcut "Me!"

 

[DeWaal]

Okay so i have changed it a bit, i have now put the expression under "CustomerID" instead of "CalloutID"

It looks like this...

It all works perfectly now.

Thank you very much.

 

[GaP42]

You are welcome .. good luck

 

[Pat Hartman]

Your problem initially was that the subform control reference was not valid. It didn't include the parent form name.

 

[DeWaal]

Your problem initially was that the subform control reference was not valid. It didn't include the parent form name.

Hey Pat

I can assure you next time ill remember. Guess that's how we learn.

 

[Pat Hartman]

I can assure you next time ill remember. Guess that's how we learn.

I just wanted you to know how close you were to getting it right the first time.

We all learn from our mistakes and I can tell you about some doozies that I've made, but you actually have it easier than I did You get far better intellisense. That is why someone asked if you let Access help you to build the where clause. Lots of people will discourage you from using the QBE (query by example) but when you are unfamiliar with SQL, it is a godsend. And now that it includes help with referencing form fields, it is even more helpful.

 

[DeWaal]

Indeed, i have been watching quite a bit of Richard Rost stuff. I'm thinking about watching his training stuff from A-Z and practicing on training Database.

I love access its done so much for me in my career.

 

[Mike Krailo]

Indeed, i have been watching quite a bit of Richard Rost stuff.

You should correct that spelling of his name before he see's it. Use the edit button. --- Fixed name - ph

 

[Pat Hartman]

Crystal Long has some good beginner videos on her youtube channel also and she is more low key than Richard is. @UncleGizmo has a lot of interesting "solution" videos. Look for Nifty Access. I've created a number of "solution" databases. Look in the Databases forum here.

 

[DeWaal]

You should correct that spelling of his name before he see's it. Use the edit button.

Whooops. Fixed it, thank you Mike.

 

[Gasman]

Whooops. Fixed it, thank you Mike.

Mike would need to change his quoted text as well?