Question How To Refresh The SubForm?

[Mahendra]

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

 

[Roku]

Hi
In your main form, you can use

Me.SubForm.Requery

(where 'SubForm' is the name of the Subform control in your form).
HTH

 

[Mahendra]

Thanks for your valuable reply. Am Novice to MS Access.

Could you kindly let me know where to use that code in the main form MForm

 

[Roku]

Hi
I have had a look at your DB, but I can't relate the tables to your question.
I assume the Main from is 'MForm' and the SubForm is 'Results'. There is no linkage between parent and child forms in the SubForm control ('Link Master Fields' and 'Link Child Fields'), which would explain why your subform does not synchronise with the main form. Also, the main from and subform both use SQuery as their data source, which is not normal (but not impossible) .
Why do you have all of the 'Like' clauses in the query relating to the main form? Why are the main form TextBoxes unbound?
If you can describe what exactly you are tyring to achieve, maybe we can provide more relevant help.
Your problem seems to be in the relationship between the main- and sub-forms, so once that is sorted out, the refresh aspect will become irrelevant.

 

[Mahendra]

Hello Roku,

Kindly have a look into your messages. I have sent you a message regarding this.

 

[Roku]

Hi
I'll have a look in more detail when I get time - later today or tomorrow. In the meantime, can you describe what the DB is aiming to achieve in business terms, please? I can see publication details in the tables, but how are these to be used? When searching, what types of item should be returned? I'm trying to get away from the simply technical in order to understand the context of operation - this will help get to a more appropriate solution.
Do you have any grouping requirements (e.g. All publications by author, subject and so on)?

 

[Mahendra]

Yes, The Author should be related to Unique Authór ID. It is the only criteria for me.

Publication is nothing but the year in which the book is published.

If I search for "A" in author column then it should return all the authors whose name contains "A" at the bottom of the form. It means the searched results should be displayed in the subform. It should display the title, publication, language along with Author field.

If I click on any one of the results in the subform then the selected result should be displayed in the new page.

Once click on the ID number in the subform you will be redirected to the new form.

 

[Roku]

Hi
I have made an example DB with some of your data to show how you can simplify and consolidate the structure.
I have created four tables: tPublication, tTitle, tAuthor and tRole. The purpose of tPublication is to link the other tables together to allow a single title for a publication, but with multiple authors, each with a different role (Author, Contributor etc).
There is one query, qAuthorByTitle, which takes fields from publication, author and role tables. This query is the data source for form sfAuthor (only).
There is one main form, fPublication which contains two subforms (sfTitle & sfAuthor). The linkage is provided by the table tPublication.
My purpose here was to get your data structure normalised for the data I saw in your original DB. I have not included everything from yours (e.g. fields and table content), but picked a few for demonstration purpose.
You mentioned the need to select titles by author, which is relatively straight-forward in this structure. I say 'relatively' because there is no direct link between tAuthor and tTitle, but this can be achieved by another query.
As you navigate the publication form, the title details and author/contributor data will tally. Thus the Refresh is automatically built-in.
I have not put in maintenance code for the publication table, but that's easy to do. I'll leave that to you, but if you get stuck, please shout.
I suggest you have a look at what I've done and borrow or adapt as you want.
There is scope to add different author attributes (e.g. Reviewer), so you are not limited to two author roles.
This was quite an interesting exercise for me, so even if it doesn't help you, the time was not wasted! Have fun!

 

[Allan.Day]

sometime i confused when to use me and when to use full path eg Forms!Form1!Subform.requery

 

[Roku]

When using 'Me' in VBA in a form's module, it refers to that form object. You use the Form!<name> when making reference to another form object.
When a subform is present within the form, it will be contained within a SubForm control. That control has a Form property, so Me.SubForm.Form is the way to make reference to the child form properties.

 

[Allan.Day]

thanks Roku, clear for now

 

[Mahendra]

Hello Roku,

Thanks for your reply.

Am figuring it out now

 

[Mahendra]

Am doing changes to this now. Thanks for your support

 

[pr2-eugin]

Mahendra, the problem you have is because you have used GroupBy clause, which would eliminate the Duplicates. For example the records..

Title           Abstract        Authors            Location        Author ID        Published        Language        Pages        Contributed Authors    Keywords    Email    University
Introduction    Dummy        Ingrid De Wolf                        5                1996            German            5                Mnop                TUC
Introduction    Dummy        Ingrid De Wolf                        5                1996            German            5                Mnop                TUC
Demo            McD            Michel Ignat                        6                2000                              1                Mnop                TUC
Demo            McD            Michel Ignat                        6                2000                              1                Mnop                TUC

For the above records, when using GROUP BY only one row for each will be returned, so if there are 100 Introductions the result will be only one..

Also I see so many columns having Null values, to include the Null values you need to add the Or Is Null condition to each of the Where clause.. Something like..

Like "*" & [Forms]![MForm]![Location] & "*"[B] Or Is Null[/B]
Like "*" & [Forms]![MForm]![Contributed] & "*"[B] Or Is Null[/B]