Subform Won't Display All Records

[lacey]

I have a form that shows data from tables tbl_AgencyInfo, and a subform showing data from tables tbl_CourseInfo & tbl_StudentInfo. Each agency can have mutliple students and multiple courses, so that is why it is set up this way.

My subform will only display one record at a time, however, even though there are multiple students/coursess that relate to the agency. I am thinking something is off with my relationships but I'm not totally sure. In previous databases I have linked my tables together in a central table, but that table in this case does not seem to be populating.

Attached is a pdf of my relationships report.

Anyone know why this is happening? Thanks.

View attachment 47679

 

[CJ_London]

Is your subform set to Single Form?

If so, change to datasheet or continuous form

 

[lacey]

It is already set to Datasheet.

 

[CJ_London]

OK, then it must be something to do with the query on the subform or perhaps the linkmaster/child field setting for the subform

Can you post your recordsource?

 

[lacey]

This?

SELECT tbl_StudentInfo.StudentInfoID, tbl_StudentInfo.[Last Name], tbl_StudentInfo.[First Name], tbl_StudentInfo.[POST Reimbursable], tbl_StudentInfo.[POST ID], tbl_StudentInfo.Rank, tbl_CourseInfo.[Course Info ID], tbl_CourseInfo.[Course Type], tbl_CourseInfo.[Course Date], tbl_StudentInfo.Processor, tbl_StudentInfo.Notes, tbl_StudentInfo.Cancelled, tbl_StudentInfo.[Date Cancelled],

 

[CJ_London]

sorry - not all come through - can you surround in code brackets (use the advanced reply)

 

[lacey]

Oops!

SELECT tbl_StudentInfo.StudentInfoID, tbl_StudentInfo.[Last Name], tbl_StudentInfo.[First Name], tbl_StudentInfo.[POST Reimbursable], tbl_StudentInfo.[POST ID], tbl_StudentInfo.Rank, tbl_CourseInfo.[Course Info ID], tbl_CourseInfo.[Course Type], tbl_CourseInfo.[Course Date], tbl_StudentInfo.Processor, tbl_StudentInfo.Notes, tbl_StudentInfo.Cancelled, tbl_StudentInfo.[Date Cancelled], tbl_StudentInfo.[Cancelled By] FROM tbl_CourseInfo INNER JOIN tbl_StudentInfo ON tbl_CourseInfo.[Course Info ID] = tbl_StudentInfo.StudentInfoID;

 

[CJ_London]

Hi,

Looking at your code and your relationships

[FONT=Calibri]tbl_CourseInfo INNER JOIN tbl_StudentInfo ON tbl_CourseInfo.[Course Info ID] = tbl_StudentInfo.StudentInfoID[/FONT]

Is not linking like for like, you need to be going through your tbl_Agency_StudentCourseInfo table to link them

Got a meeting to go to, so can you take it from here?

 

[lacey]

Thank you! I am not quite understanding though. I will mess with it and try to figure out what you mean. Hopefully by the time your meeting is over I will have figured it out

 

[lacey]

I don't understand what to change in my code to make it link like for like as you stated. Can anyone advise?

 

[CJ_London]

I'm Back

if you replace - do check names for spaces and spelling!

From tbl_CourseInfo INNER JOIN tbl_StudentInfo ON tbl_CourseInfo.[Course Info ID] = tbl_StudentInfo.StudentInfoID

with

FROM tbl_StudentInfo INNER JOIN (TblCourseInfo INNER JOIN tbl_AgencyStudentCourseInfo ON TblCourseInfo.[Course Info ID] = tbl_AgencyStudentCourseInfo.CourseInfoID) ON tbl_StudentInfo.StudentInfoID = tbl_AgencyStudentCourseInfo.StudentInfoID

should solve the problem

 

[lacey]

Still not working

When I put in that code it gives me the following error: "The record source 'FROM tbl_StudentInfo INNER JOIN..." specified on this form or report does not exist. The name of the recordsource may be misspelled, the recordsource was deleted or renamed, or the record source exists in a different database. In the Form or Report's Design view or Layout view, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query."

And yes, I checked the spelling/spacing in the codes... it all matches.

Any idea?

 

[CJ_London]

I took the spellings from the details in your relationships you posted at the start of your thread

however tbl_AgencyStudentCourseInfo is not fully represented so may be mispelt.

Also just noticed I have put TblCourseInfo and should be Tbl_CourseInfo

try correcting the above and checking my other spelling

 

[lacey]

I double-checked all the spelling, there are no errors in the spelling. What else would be causing that error?

 

[CJ_London]

This is usually pretty accurate!

"The record source 'FROM tbl_StudentInfo INNER JOIN..." specified on this form or report does not exist

Can you confirm that all record sources (tables, references to forms etc) are all present and correct

 

[lacey]

I double checked it all again... all names and record sources are correct... I still get the same error. I'm about ready to tear my hair out and start over!

 

[CJ_London]

can you post a cut down copy of your db - bet I can find the problem!

 

[lacey]

Here it is.

View attachment Law Enforcement DB - Copy.accdb

I copied my database and deleted all the student information... but now I can't even click into the subform to enter data. Really confused as to how that happened since all I did was copy and delete data.

MUCH thanks for your help!

 

[CJ_London]

Have sorted it.

There are a number of problems - I've sent back the corrected db plus some images to explain - but would be good practice for you to update your db rather than just copying the attached.

Problems were
1. Your tbl_AgencyStudentCourseInfo table was not populated which I have now done for the data supplied - see tblAgencyStudentCourseInfo image. This has to be populated for your whole db to work - again, I refer you back to the image you originally posted at the start of this thread.
2. Your subform query had an unnecessary link - see images before and after
3. Your subform control linkchild field was wrong - see images linkchildbefore and linkchildafter
4. Your query did not bring through the AgencyInfoID so the subform would not link even having corrected in the previous point - see image linkchildquery.

Told you I could sort it! Let me know how you get on

 

[lacey]

Oh, perfect! Thank you, you are my hero! It got to the point where I was thinking about it way too much and my brain was totally clouded. I ignored that project for a day and it all makes sense now, hah. Thanks again!