A quandary regarding form events

[GrunleStan]

I have a bound form that opens and loads all its wonderful data in its wondrous glory for all to see.
However there are some bits of data that have to be displayed. Now if I were to bind this form into a query, this bound form then becomes un-editable, as the query will have to make use of SQL aggregate calls and such.

Hence I use some dlookups for the information of these fields. and its all nicely bundled in to a procedure that is called on form_current ()

What irks is that the first time the form is loaded, these fields are not filled up.
But if I were to navigate to the next record and back... Magic !! it gets filled up...

What do I need to do to get it filled up. the Dlookups are based upon the Primary key; so that means the record has to be loaded first...
isn't the onCurrent suppose to run when the data is loaded ?
do I need to force my form to refresh ??
What can I do to have my linked fields populated in the first opening ?

 

[Uncle Gizmo]

You need to add a setup routine to your Form. Then open your Form from a "Menu Form", and in the process of opening it, run the setup routine.

If you look at my video here:- Form Load, Activate, Open, Issue - Nifty Access you will see how you call the set-up function. It's the function in green. You don't have to name it "function setup" "fSetUp" you can give it any name you like... In the video you can see the code that calls it.

The way MS Access Form load events work is very strange, and causes lots of problems. I discuss it on my video blog here... Form Load, Activate, Open, Issue, – Form Load Events

Calling a Setup Routine

Private Sub btnNewHistEvnt_Click()
Dim strFrmName As String
strFrmName = "frmClientFoldHistEvntDetl"
    DoCmd.OpenForm strFrmName
    
    With Forms(strFrmName)
    '.prpClientID = Me.ClientID
    '.fSetUp
    '.Caption = "I CAN CHANGE THE CAPTION"
    End With
End Sub

 

[Uncle Gizmo]

Reading your question again, I realise that the first time I read it I assumed that you had tried calling the code from the Forms load event, and it failed to run. So that's what my 1st answer refers to. However reading your question again, it looks like you are calling it from the on current event, and it's not running the first time. In that case call the code from The onload Event. If it doesn't work, you might have to do something similar to what I said in my previous post...

 

[Minty]

Create your calculated fields in a separate query with the ID field.
Left Join that query to your basic table, and with a bit of luck you should be able to edit the resultant data sheet.

Probably

 

[isladogs]

Suggest you put your additional data in a subform.
As it sounds like this data is independent of your man data, the subform and main form do not need to be linked.

 

[The_Doc_Man]

the Dlookups are based upon the Primary key; so that means the record has to be loaded first...

as the query will have to make use of SQL aggregate calls

So instead of using DLookup on your aggregate query, could you have just used DSum, DCount, DMax, etc. in the Form_Current routine? If you are worried about performance, then consider this:

Every time you run a DLookup to get a value from your aggregate query, DLookup has to generate a hidden query to the named aggregate query, so that is a query of a query. The SQL processor runs the aggregate query behind the scenes, then the DLookup hidden query plucks one (aggregated) field out of the result. Two queries get run.

Every time you run a direct DSum, DCount, etc. to pick up the aggregate, if you base it on the original source of your aggregate query (bypassing the middle-man), the Domain Aggregate function builds ONE hidden (aggregate) query. Only one query gets run for each Domain Aggregate function.

If you can make it work with DLookups, you can probably make it work with the other Domain Aggregate functions.

What irks is that the first time the form is loaded, these fields are not filled up.
But if I were to navigate to the next record and back... Magic !! it gets filled up...

Yeah, that would irk me too. Since I can't see (and really don't want to see) your code, all I can suggest is that you have an implied side-effect somewhere. If those aggregate fields filled by your DLookup subroutine don't immediately fill in on first load, it is because something used in that subroutine doesn't get filled in either.

It might be tedious, but I would put a breakpoint at the first instruction of that routine and then single step through each part to see what ISN'T defined. As I said, might be a bit tedious, but you should only need to do it once to figure out what isn't quite right with that call.

 

[GrunleStan]

Hey guys thanks for all the suggestions.
I'm really learning more just by reading this forum compare to the stack one....
I went through the code and suddenly realised I had called the code under a condition.
Since the first condition did not apply it was skipped during the onload event.

I have since split the code and loaded it separately.