Display all records on form

  • Thread starter Thread starter tm3025
  • Start date Start date
T

tm3025

I am having difficulty with a form I created that is linked to fields in four
tables. I entered in the data for these tables using this single form and the
data now appears in the tables. However, after saving the form and closing
it, I can only scroll through 17 different records (out of ~150) in the
display view. The data still appears in the tables though. I want to be able
to access these forms because I have additional data to enter and new fields
to add, and I don't want to have to go through each table. I have looked up
some solutions for this but none of them have worked.

There is no filter on and I have tried adding a filter and removing it. Data
entry mode is set to no. I also found a suggestion to put in the VBA code:
Private Sub Form_Load()
Me.Requery
End Sub
but this also didn't work. I would really appreciate any suggestions.
 
In the query for the form did you use INNER JOIN for all or did you use LEFT
JOIN as appropriate?
 
I actually didn't use a query to create the form. I inserted existing fields
onto a blank form, but I think at some point I may have selected a LEFT JOIN
option. Hope that helps.
 
I am having difficulty with a form I created that is linked to fields in four
tables. I entered in the data for these tables using this single form and the
data now appears in the tables. However, after saving the form and closing
it, I can only scroll through 17 different records (out of ~150) in the
display view. The data still appears in the tables though. I want to be able
to access these forms because I have additional data to enter and new fields
to add, and I don't want to have to go through each table. I have looked up
some solutions for this but none of them have worked.

There is no filter on and I have tried adding a filter and removing it. Data
entry mode is set to no. I also found a suggestion to put in the VBA code:
Private Sub Form_Load()
Me.Requery
End Sub
but this also didn't work. I would really appreciate any suggestions.
Click to expand...

The Recordsource for the form is evidently a query (probably automatically
generated) that joins the four tables in such a way that you will only see
records with matching values in all four tables.

How are these tables related? Or are they?

Normally you would NOT put four tables jumbled together onto a form, but
instead use a Form based on the "One" side table with Subforms based on the
related "Many" side tables. If your tables aren't related in any defined way,
you probably need to step back and properly normalize your table structure
first. For some hints see

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
I think at some point I may have selected a LEFT JOIN option. Hope that
helps.
No, as I can not see any joins behind your form.
If you create a query then you could post SQL when you have problems.
 
These tables are all related by the same primary key (not an autonumber). I
don't understand how that would affect the form in the way it has. The first
ten records I entered are saved and still show up, but only 7 out of the rest
of the records show up in the form. I also tried creating a form with the
form wizard and this will also only display the same 17 records.
 
Here is the SQL from the form. It appears that I was using INNER JOIN for all.

SELECT [Patient Info].*, SWL.Surgeon, SWL.Date, SWL.Location,
SWL.Anesthetic, SWL.ASA, SWL.Stent, SWL.Gated, SWL.Success, SWL.[Cysto/RPG],
SWL.OpTime, [Stone Info].StoneHeight1, [Stone Info].StoneWidth1, [Stone
Info].Location1, [Stone Info].Shocks1, [Stone Info].ShockPower1, [Stone
Info].ShockRate1, [Stone Info].StoneHeight2, [Stone Info].StoneWidth2, [Stone
Info].Location2, [Stone Info].Shocks2, [Stone Info].ShockPower2, [Stone
Info].ShockRate2, [Stone Info].StoneHeight3, SWL.[Medications Received],
[Stone Info].StoneWidth3, [Stone Info].Location3, [Stone Info].Shocks3,
[Stone Info].ShockPower3, [Stone Info].ShockRate3, SWL.Machine, [Stone
Info].Side1, [Stone Info].Side2, [Stone Info].Side3, SWL.Reason, [Secondary
Procedure].Type1, [Secondary Procedure].Reason1, [Secondary
Procedure].Complications, SWL.Notes
FROM (([Patient Info] INNER JOIN SWL ON [Patient Info].MedicalRecord =
SWL.MedicalRecord) INNER JOIN [Stone Info] ON SWL.MedicalRecord = [Stone
Info].MedicalRecord) INNER JOIN [Secondary Procedure] ON [Patient
Info].MedicalRecord = [Secondary Procedure].MedicalRecord;
 
I changed all of the inner joins to left joins and it is now displaying all
of my data. Thanks for your help.
 
These tables are all related by the same primary key (not an autonumber).
Click to expand...

Such one-to-one relationships are VERY RARE.

If you're not familiar with the terms "Subclassing" or "Table based
field-level security" it's very likely that your tables aren't correctly
normalized.

Why do you have this many tables all with the same PK? Are you perhaps trying
to get around the 255 field limit...?
 
Back
Top