How To Refresh The SubForm?

[Mahendra1000]

Hello,



I have created a form and a subform from a table.

Initially I created a table with 11 rows. After completion of my project I added an extra of 480 rows to the main table. But the newly added rows are not coming in my subform.

Attached is my project. Could you kindly have a look into it.

Main : Main table (It consists of around 491 rows)
MForm : Main Form (It shows of only 11 records which I was 1st created)

How to refresh my subform inorder to display all the rows.

Could you kindly help me out

[ItsMe]

Closing the form and then reopening your form will "Refresh" your form.

If this is not showing all of your records you need to understand how your form is querying the table. I suggest you look at the form's recordsource.

If you have trouble determining what the record source is through the main form.... you need to focus on the Master and child field links between main and subform.

Try opening your subform ONLY. Does it display the results you need?

[Mahendra1000]

No, My subform was not containg all the records. It just contains the records when it is created and the records which are added later are not replicated in the subform.

Could you have a look into this please.

[ItsMe]

I have not looked at your DB but I suspect your problem lies within your subform's property settings. Change your form's "Data Entry" property to "No". It is on the property's "Data" tab.

When you build a form using the wizard, the wizard will ask you how you will use the form for. When you reply, it will be for data entry the wizard will adjust the "Data Entry" property to "Yes".

[RuralGuy]

PMFJI but the latest db you posted *only* has 11 records in the Main table.

[RuralGuy]

Your Add Table Form adds records to the Add Table table but not the Main table. The Results subForm only displays records in the Main table.

[June7]

Should not have both author name and author ID in Main table, only the author ID as foreign key. It is not necessary to use a form/subform arrangement. Can use one form with the search controls in the form Header section and set the form as Continuous view. Ungroup the controls (you must have created the subform with wizard) and can rearrange and resize individually. Select all controls, right click > Layout > Remove.

The MForm has DataEntry property set to Yes but Results has set to No. Even after changing MForm to No, still not able to add new records into subform. It's because SQuery is a GROUP BY query. Aggregate queries are not editable. Why did you set this query as a GROUP BY?

Author name parts should be in separate fields in Author table like FirstName, LastName.

You have macros and VBA code. Why not just use VBA? I never use macros.

What is purpose of Add Table and resultSubFrm tables?

[Mahendra1000]

Should not have both author name and author ID in Main table, only the author ID as foreign key. It is not necessary to use a form/subform arrangement. Can use one form with the search controls in the form Header section and set the form as Continuous view. Ungroup the controls (you must have created the subform with wizard) and can rearrange and resize individually. Select all controls, right click > Layout > Remove.

The MForm has DataEntry property set to Yes but Results has set to No. Even after changing MForm to No, still not able to add new records into subform. It's because SQuery is a GROUP BY query. Aggregate queries are not editable. Why did you set this query as a GROUP BY?

I Used GROUP BY query for granting Read Only permissions to the front end users.

Author name parts should be in separate fields in Author table like FirstName, LastName.

Thanks. I changed Author name into two columns FirstName, Lastname

You have macros and VBA code. Why not just use VBA? I never use macros.

I don't know how to code through VBA. Am still in the initial stages of learing MS Access

What is purpose of Add Table and resultSubFrm tables

Add Table - It is the secondary table. If an user wants to add data. Then he should enter the data into this table and later the backend department will verify the data and then they will copy it into the main table. I mean the front end users shouldn't have the write permissions towards the Main table.

I suppose resultSubFrm is dummy file (Not Sure).

I got my subform page refreshed. I used "Or is NULL" in my query. My subform is now able to show all the records from the Maintable.

I was unable to create the relationship between AuthorID (Author table) and Author (Main table). Could you kindly establish a proper relationship between those two tables.

Your modifications to my tables/project is most welcome.

Can you make a perfect relationship between those two tables please.

see my comments in your above post marked in Red/Green.

Attached is my project

Thanks

[Mahendra1000]

Your Add Table Form adds records to the Add Table table but not the Main table. The Results subForm only displays records in the Main table.

Add Table - It is the secondary table. If an user wants to add data. Then he should enter the data into this table and later the backend department will verify the data and then they will copy it into the main table. I mean the front end users shouldn't have the write permissions towards the Main table.

I request you to go through my above post No : 8 for my queries marked in GREEN.

Your suggestions/Modificatios for my table are welcome

[June7]

I had no problem setting link between the tables in Relationship builder. But for some reason it insists on having link between Authors and FirstName fields, even after removing Authors from queries and forms. It insists there is a relationship somewhere. Maybe corruption. Make a copy of Main. Delete Main. Delete Authors from copy. Rename the copy.

Users should not work directly with tables and queries, only interact with forms and reports.

I would not have a 'secondary' table and therefore not deal with copying records.

[Mahendra1000]

I had done your points now.

Copied main table and then deleted the main table.

Deleted the column Authors from the copied table.

renamed the copy table as Main table again.

Attached is the new sheet.

[June7]

Now that the author name is no longer in Main, will have to base Results form on a query that joins tables so that the names will be available. Modify SQuery to:

SELECT [Last Name] & ", " & [First Name] AS AuthorName, Main.Title, Main.Language, Main.Pages, Main.Location, Main.[Contributed Authors], Main.Keywords, Main.Email, Main.University, Main.Published, Main.Abstract, Main.ID
FROM Main LEFT JOIN Author ON Main.[Author ID] = Author.[Author ID]
GROUP BY [Last Name] & ", " & [First Name], Main.Title, Main.Language, Main.Pages, Main.Location, Main.[Contributed Authors], Main.Keywords, Main.Email, Main.University, Main.Published, Main.Abstract, Main.ID
HAVING ((([Last Name] & ", " & [First Name]) Like "*" & [Forms]![MForm]![Authors] & "*" Or ([Last Name] & ", " & [First Name]) Is Null) AND ((Main.Title) Like "*" & [Forms]![MForm]![Title] & "*" Or (Main.Title) Is Null) AND ((Main.Language) Like "*" & [Forms]![MForm]![Language] & "*" Or (Main.Language) Is Null) AND ((Main.Pages) Like "*" & [Forms]![MForm]![nPages] & "*" Or (Main.Pages) Is Null) AND ((Main.Location) Like "*" & [Forms]![MForm]![Location] & "*" Or (Main.Location) Is Null) AND ((Main.[Contributed Authors]) Like "*" & [Forms]![MForm]![Contributed] & "*" Or (Main.[Contributed Authors]) Is Null) AND ((Main.Keywords) Like "*" & [Forms]![MForm]![Keywords] & "*" Or (Main.Keywords) Is Null) AND ((Main.Email) Like "*" & [Forms]![MForm]![Email] & "*" Or (Main.Email) Is Null) AND ((Main.University) Like "*" & [Forms]![MForm]![University] & "*" Or (Main.University) Is Null) AND ((Main.Published) Between Nz([Forms]![MForm]![start],1700) And Nz([Forms]![MForm]![end],3000) Or (Main.Published) Is Null) AND ((Main.Abstract) Like "*" & [Forms]![MForm]![Abstract] & "*" Or (Main.Abstract) Is Null) AND ((Main.ID) Like "*" & [Forms]![MForm]![ID] & "*" Or (Main.ID) Is Null))
ORDER BY Main.ID;

Fix the ControlSource for Authors textbox in Results to AuthorName.

Table Main has 492 records but because of the GROUP BY query, Results shows only 440.