Showing all records in Form view

[DesignGuy]

Hello everyone,

I am currently working on a project for work using Access 2007 and I am quite new to the program. I'm having a problem and I can't seem to find the solution despite a lot of online research.

Basically, I've created a database with a total of 500 asphalt mix designs. I created 6 tables to hold different types of technical information, and in each table was a field named "DesiNumb" which would be the primary key for that table. Then, I created a Referencial Integrity-enforced relationship between the primary key "DesiNumb" in a table named "Summary" to the primary key "DesiNumb" in each one of the other five tables. In other words, the DesiNumb field in my Summary table was connected seperately to the primary key with the same name in the five other tables.

Then, I created a form that would let me enter information/data into fields from all of the tables. The primary key "DesiNumb" is entered as well as all of information for the various fields. I noticed that the form would then enter data into the six different tables while keeping the data for each record attached to the corrosponding primary key for each table ("DesiNumb"). This is good!

So here's my problem: I would like to be able to browse through all of the records while they are in form view, but only 142 of the 500 records will display. I'm not sure why this is, as the records that do appear were entered during different sessions over the last 5 months. I've searched online, but I can't seem to find the solution.

Here's some of my current settings: Allow Form View = Yes, Record Source = SELECT [SUMMARY], Recordset Type = Dynaset, Fetch Defaults = No, Filter On Load = No, Order By On Load = Yes, Data Entry = No, Allow Additions = Yes, Allow Deletions = Yes, Allow Edits = Yes, Allow Filters = No, Record Locks = No Locks, Cycle = All Records.

These are all of the relevant options/settings I can think of, although I might be missing the one that is currently causing the problem. How do I get all of the records to become available in form view?

[June7]

Possibly has something to do with jointype of query. Can you provide project for analysis?

[RiVit]

In this situation is better to contruct a query that include all the information than you need in the form.
But if you have a relation 1 to n from a table to another, you have to use subform to present your information

In the form propieties select data, in this part you have to define the source of the data, maybe is a query constructed before, maybe is a query embeded into the form, in any case you have to be carefull with link between two tables, if you select (doble Click) the line, it show you the diferent types of relations between the two tables, you have to select the records of the table that has more record.
Try with the diferents types of relations and analize the result, I think your problem is right there.

[RiVit]

by the way, there is a simple training course in Microsoft page, try to find it

[DesignGuy]

Thanks for the replies, everyone.

@June7: I wish I could post the project, but my manager has advised me not to. What do you mean by a jointtype of query? I'm not very familiar with Access so I'm not sure how I would be able to investigate a jointtype query. Where would I start?

@RiVit: I'm not sure I understand what you want me to do. By entering Properties -> Data -> and then hitting the "..." option on the Record Source option, I'm able to get into Queury Builder. From there, I can see that all of my tables have Referential Integrity-enforced relationship between the primary key in my Summary table and the primary key in each seperate table. Below, on there is a list of all the tables that all have the "Show" box checked off. Each Field is matching the Table below it.

I don't really understand what this Query Table is for. It seems like the Form is doing what I want it to for 142/500 records, so I think the relationships between the tables is fine? I have no idea how to construct a relevant or effective query. I was hoping I could get away without doing that, but I will look into how to build them and hopefully be able to find a solution to this.

Thanks all for your thoughts. I'm still looking for the solution, so if anyone has anything to add to this, it would be greatly appreciated.

-DesignGuy

[June7]

Don't care about data. You could make a copy and remove confidential or all real data. Plug in some dummy records for testing.

Review this http://pcmcourseware.com/blog/2010/1...rosoft-access/

If the jointype is not the issue might be some criteria in query filtering the records.