Need subreport on form to show one record (1 Viewer)

ZenDiagram

Registered User.
Local time
Today, 08:50
Joined
Aug 28, 2018
Messages
37
Hello,

I posted yesterday about not being able to edit my form because it was based on a read only query.

Now that I have my form edit-able again, I need to show fields from other tables on the form.

I made a report with the owner's information. I dragged it onto the form as a subreport. But, the subreport shows all records for each owner. I just want to show one per form.

How can I do this without screwing up my form again ?

Thanks.
 

Ranman256

Well-known member
Local time
Today, 07:50
Joined
Apr 9, 2015
Messages
4,339
Dragging the table to the form doesn't do it all.
You must set the property of the subForm to show only that master key.
Set subForm properties:
Master form field,
And
Child form field

Then the subForm only shows the records that belong to the master id.
If you only want to see 1 record (in a many record set)
The sub query show be set to :top 1
 

ZenDiagram

Registered User.
Local time
Today, 08:50
Joined
Aug 28, 2018
Messages
37
Ok - I have linked master and Child fields. Now I want to show only one record per owner.

How and where do i set the sub query to: top 1?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
did you make the master/child link fields to the Sub Report?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 19, 2002
Messages
42,981
Dragging a report onto a form probably isn't the solution. Forms go on forms. Reports go on reports. Although putting a report on a form may work, you will probably end up with subtle issues.

Go back and make a query that joins the two tables correctly (if it's more than 2, post back because we'll need to understand the relationships)

Use that query as the RecordSource for the form.

You may end up needing to use a query with a join as well as one or more subforms.
 

ZenDiagram

Registered User.
Local time
Today, 08:50
Joined
Aug 28, 2018
Messages
37
Pat Hartman,

I have tried that but it made the form "read only". Correct me if I'm wrong, but if I make a query that joins two or more tables together as the form's record source, it will make the query un-editable (or "read only").

Also I'm not really sure what a "join" is. Is that when the record source has two or more tables with a relationship?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 19, 2002
Messages
42,981
The number of tables/queries in the join is not the constraint. I have lots of queries with a dozen or more tables/queries in them and they are updateable.

The problem as I suggested is the RELATIONSHIP between the tables. There are two general patterns where you can have multiple joins (or 3 types if you count the fact that you can use both patterns in the same query).
1. a Hierarchy such as tblCustomers --> tblOrders ---> tblOrderDetails --> tblProducts
2. multiple lookups
tblEmployees --> tblDepartment
tblEmployees --> tblSalaryGrade
tblEmployees --> tblEmployees.Supervisor (self lookup)
tblEmployees --> tblJobTitles

So, post a picture of the relational diagram. Make sure we can see all the tables and how they are connected. Post the actual query so we can see the joins.

Relationships are different from joins although the graphic representation is similar. Relationships are defined in the BE database using the relationships window. You add tables to the grid and then draw join lines connecting them from Foreign key to Primary Key. So in the first example above. tblEmployees would contain a column named DepartmentID. It would be the foreign key (join field) to the primary key of tblDepartment which should also be named DepartmentID. in tblDepartment, the DepartmentID is defined as autonumber but in tblEmployees, DepartmentID is defined as Long Integer. A table can have only ONE autonumber and if you have one, it should be the primary key.

Joins are the connections between tables/queries in a query. Normally joins in queries will mimic the relatioships but technically any two tables/queries can be joined on any pair of similarly defined columns. So you could join tblEmployees to tblVendors on AddressLine1 because both tables have a column that is defined as short text. Even though you would never define this as a relationship, it makes sense as a potential join if you are trying to do some analysis and determine if any employees are also vendors based on them having the same address. You could join tblStudent to tblClasses on Student.FirstName --> tblClasses.ClassName. It doesn't make any sense but the query engine doesn't have any way to know that. As long as the two columns are compatable data types, the query will attempt to match the two tables on the specified values.
 
Last edited:

ZenDiagram

Registered User.
Local time
Today, 08:50
Joined
Aug 28, 2018
Messages
37
Here is a photo of the form property query showing the relationships between my tables/ query.
 

Attachments

  • Access DB Query.jpg
    Access DB Query.jpg
    96.4 KB · Views: 48

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 19, 2002
Messages
42,981
Please reread my instructions.
1. The autonumber should be the primary key.
2. The foreign keys point to the primary key.

If you want to use strings as the PK, that's OK but remove the autonumbers.

If you use strings as the PK's then, your combos will only select the one string column and you won't have any trouble with the combos.
 

Users who are viewing this thread

Top Bottom