Even Microsoft Couldn't Solve This One...Can Anyone?

[italianfinancier]

Hi, I just got off the phone with Microsoft Technical Support and the tech said he didn't know how to get my issue solved. All I want to do is have a form that a user enters a customer number into and from that a report generates showing just the information about that customer. I have attached detailed screen shots for your review if you are kind enough to take on this challenge and possibly upstage Microsoft . Please provide detail in your response, as I am somewhat new to Access.

 

[boblarson]

First off, you have a problem with your query. You have no joins between the tables so you will be getting a Cartesian Product which means you will get a massive amount of records since it will be the number of records in one table times the number of records in another and so on and so on.

Second, your Report is whacked out as well. You cannot set the control sources to things like

=[Query1]!Offenders by Group]!...etc.

You set the report's RECORD SOURCE property to your query and then just select the field from the property you want displayed at the control source of each control.

Third - To open the report by customer you would simply use


DoCmd.OpenReport "Report1", acViewPreview, , "[Customer]=" & Chr(34) & Me.Text4 & Chr(34)

but you have to fix your query and report first before it will even come close to working.

 

[italianfinancier]

Well that would explain many things! lol

What is the purpose of joins? What data would you join together in the query?

There is no Record Source in Access 2010 that I can find...only a "Control Source". Is that the same thing?

 

[boblarson]

Well that would explain many things! lol

What is the purpose of joins? What data would you join together in the query?

The purpose of a join is to link together the primary key with the foreign key (or in the absence thereof, fields which would have the same data and be related to the other table). So in this case the Customer field is what appears to be the common field. However you can't necessarily just link them together. If you just did an Inner Join where the fields are linked, one-to-one, then the only way data would be returned is if there is a record for that customer in every table. You can select a table which would have data for all customers for sure and then you can use an OUTER JOIN to join that table with one or more of them so as to gather ALL RECORDS FROM THE ONE TABLE and any records that match from the others.

I think it may be good for you to read this (go through it all very carefully as Queries are one of the main things in Access which you must get to know well in order to be able to report out):
http://office.microsoft.com/en-us/access-help/join-tables-and-queries-HA010096320.aspx

There is no Record Source in Access 2010 that I can find...only a "Control Source". Is that the same thing?

No, it isn't the same thing and you have to select REPORT from the drop down in the properties dialog which selects the report object itself. Then you should see Record Source under the DATA TAB in the properties Dialog.

This is an example from Access 2003 but it is similar if you go to the properties dialog which is on the far right of the screen in Access 2007/2010.