• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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

Hi,
I have three subform within my Main Form in Access 2007, The subform are TrippleK,haidroTen and Emartech.
How do I refresh this form whenever new data inserted in Main Form ? what I'm doing now is everything new records are inserted, I have to close and open the Main form again in order subform to show me with new records

access subform
0
motioneye
Asked:
motioneye
  • 9
  • 6
  • 4
  • +1
1 Solution
 
hnasrCommented:
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.

Remeber Me.Requery command to initiate a form refresh.

Try to upload a reproduced copy of the database demonstrating the issue
0
 
motioneyeAuthor Commented:
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
0
 
hnasrCommented:
Waiting for the db.

Tell me what to do to see the problem: like
open form, named ...
enter data  in ...
 you see ...
but expected ....
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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

Open 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.
0
 
motioneyeAuthor Commented:
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';
0
 
Leigh PurvisDatabase DeveloperCommented:
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.)
0
 
hnasrCommented:
"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
0
 
motioneyeAuthor Commented:
Hi,
Attach the accdb fie
TrippleK-Database.accdb
0
 
hnasrCommented:
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.
0
 
motioneyeAuthor Commented:
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 )
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
hnasrCommented:
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
0
 
motioneyeAuthor Commented:
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.
0
 
hnasrCommented:
Here is the idea applied.
Removed all unwanted objects, subqueries and subforms.
Replaced by controls using DMAX()
The bottom one reflects the current company, the top 3 sets of controls replaced the subqueries.
TrippleK-Database-3.accdb
0
 
motioneyeAuthor Commented:
Hi hnasr,
I really appreciate with your help, its beyond my expectation.. thanks for the good support.
0
 
hnasrCommented:
Welcome!
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
 
hnasrCommented:
To LPurvis,

I agree that my comment was not "The solution" neither any expert can claim his to be.
There may be "a better solution", in condition it is given in right time.
The question was about "How to refresh subform whenever main form inserted with new records". The comment addresses an issue, and not a design project.
In general, given enough resources a better solution may be achieved.
0
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
hnasrCommented:
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"
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now