Access: How to refresh subform whenever main form inserted with new records

If you have defined the parent/child link fields between main form and subforms, then subforms are automatically updated with every new main form record.

I cannot use this method since the new number can either greater or lower from it previous.

Remeber Me.Requery command to initiate a form refresh.

Try to upload a reproduced copy of the database demonstrating the issue

I attached with my access db and form, hoepfully someone expert can guide on my issues right now

Waiting for the db.

Tell me what to do to see the problem: like
open form, named ...
enter data  in ...
 you see ...
but expected ....

I cannot use this method since the new number can either greater or lower from it previous

.
That shouldn't matter to the LINKS between the Parent and Child records. A Child record should be related to it's Parent record by storing the PK of the Parent in the Child. That should not change, and if you add NEW records to the Parent form, then your Child form would show no related records.

It sounds like this subform isn't displaying child records but a list of records from the main form - is that accurate?
If it is child data, then, as stated, the Master and Child link fields would take care of filtering.
If the former, then you can always update it as required in code.
It would make sense to have as below to begin with.

Private Sub Form_AfterInsert()
    
    Me.SubFormControlName.Form.Requery

End Sub

Select allOpen in new window

A subform for this purpose is a little unusual though.  List controls would be more common - and even then, you need to consider how many rows you're offering the user before it becomes an unproductive UI choice.

Hi,
In each subform, I bind them with a sql query like below so when it display records, it takes/display  only 1 records which always higher then the other.

The problem is, when I entered new records,  I have no way to refresh the subform unless I close and open a Main form again..


SELECT Max(QuoNumber) AS QuoNumberTK, max(DONumber) AS DONumberTK, max(InvNumber) AS InvNumberTK
FROM Invoice
WHERE company='TrippleK Multi Ent';

So it's more than just a count you're looking for.  This is related, but summary, data.   Not parent/child.
You are reflecting the latest enetered data, and nothing more?

I'd have to just repeat my earlier suggestion then. ;-)

(If you can change these values after entry - then you might want to use AfterUpdate instead, as not only record inserts could affect your values.)

"The problem is, when I entered new records,  I have no way to refresh the subform unless I close and open a Main form again."

This means a design issue, More info is required, see comment: http:#38290292

Hi,
Attach the accdb fie
TrippleK-Database.accdb

Tell me what to do, and where to look,  say enter new record with values.
Press save record.
Expected result is .... but the result is ...
Close main form, and reopen, see the record, expected output is shown.

Hi,

I need a solution that when u insert new QuoNumber,DoNumber,InvNumber, we can have like a "refresh" button on subform  with field quonumbertk,dnumbertk,invnumbertk to refresh my display recrds with new records being inserted

U can try with inserting values in QuoNumber,DoNumber,InvNumber open and reopen form the u will see different, hint ( just make sure ur number is higher than current values u see in the form )

I don't quite understand, and your instructions for using the database are not clear. Please provide accurate instructions, and please don't use "text speak" when doing so - write out the instructions in clear, eacy to follow language.

However: Is there some reason you cannot simply re-apply your queries to those subforms when you need to? For example, if you're doing this:

Me.MySubformControl.Form.Recordsource = "SELECT blah blah"

Could you not just enter those commands again after your mainform action?

After a second thought:

You don't need the subforms, and the subqueries.

Just add 3 text boxes as shown in the sample database and set their respective control source as shown at the bottom of the main form.
TrippleK-Database-2.accdb

Hi hnasr
Have tested with the edited accdb by you, unfortunately I don't see any solution being provided.

You have create new control source as shown at the bottom of the main form, but when I save new records then click "new record" here is the problem begin, your control source missing the record values, again I have to reopen form in order to refresh the values.

Hi hnasr,
I really appreciate with your help, its beyond my expectation.. thanks for the good support.

Welcome!

I can't wholeheartedly endorse that as the solution.
It's a very fixed set of data (requires application maintenance for data alterations in the future).
(I assume that "Company" isn't really supplied by a Value List in that combo - it really should be a table of its own. See the alternative suggestion later.)

With the DMax control solution - you're getting implict updates performed with navigation yes, but you're performing 9 queries to provide the displayed data! (That's 9 queries with each record navigation.)

The solution I still maintain comes back to my original suggestion early in this thread.
Requery the subform when appropriate.  But make the subform appropriate in itself.
In this case, a single query grouping the company options.

Consider the files attached.  Firstly as an example of the issue at hand.  Secondly (the "-rel" suffix) as how the tables would be better structured.
TrippleK-Database.accdb
TrippleK-Database-Rel.accdb

To LPurvis,

There was no slight intended on my part.

I have often posted in (seemingly) already closed threads when I think there's something worth saying (knowing full well that the OP is likely to completely ignore the advice, but future readers may yet learn.)
(Consequently I don't get the point of "There may be 'a better solution', in condition it is given in right time." I don't see timing having anything to do with it - unless one cares in some way abouts points :-s.)

I can't recall such an occasion where I've said that my interjection is the solution... but I'd be disappointed if it's ever something not worth thinking about or their learning from.

If my earlier comment was ignored/overlooked, well it isn't the first time.  If my latter efforts to demonstrate the implementations I was discussing are never read, well that's a shame. I just wanted to put them out there as they felt worth making clear.

Cheers.

We, sometimes, ask for a sample of the work (i.e database, worksheet, ...) just because typing is short of conveying the message.

I am sorry if  my comment intention was even slightly misunderstood!

let me comment, so not to stamp me with overlooking your comments.

"There may be 'a better solution', in condition it is given in right time."
If we ignore right time (the time slot for the author to accept a solution) and go back to our early solutions to questions in EE, we are certain to present a better solution to many of them.

"I can't recall such an occasion where I've said that my interjection is the solution"
I did not say that had, but I was talking in general commenting on 1st line of your comment:
"I can't wholeheartedly endorse that as the solution"