Access SubForm slow to fill

[notconfident]

I have a MainForm with a RecordSource of Current_Marshal.
In that there is a SubForm with a RecordSource of WWtable
They are linked with Ref_No.
On the SubForm there are 5 Controls, 3 come direct from the WWtable and fill fine of course,
these are 2 comboBoxes and a TextBox.The other 2 are TextBoxes but are slow to fill.
The SubForm has about 14 records.
When I move from one record to another on the MainForm all the 3 good controls fill for all records
and in the first record the other 2 problem controls fill.
Then there is a pause of a couple of seconds then the other SubForm records fill.
These 2 problem controls are populated from their ControlSource. here is one :-

=DLookUp("[DescriptionForCard]",
"tblSession" & DLookUp("[EventYear]","MiscTable","[MiscTableID] = 1"),
"[SessionID] = " & [Forms]![Normal View Form]![SubWW]![SessionID])

I had a similar slow problem on the MainForm and put the DLookup in vba as in the MainForm OnCurrent Event.
This was quicker !!
I cant get the same trick to work in the SubForm OnCurrent event.

This is slower in practice as it is over a network. My tests are just local.
Any Ideas??
Thanks

 

[MajP]

Dlookups are somewhat slow, and you have a strange nested dlookup. It appears that your tables are not normalized. Do you have multiple table session for each year. You should not. If you cannot fix that then normalize the data with a union query, and include the session I'd referenc from the form in the query. That would create a query with one record. Then you could do a simple dlookup on the stored query.

 

[notconfident]

Hi MajP
Thanks for your reply.
I assume that Normalized is the same as Relational.
This database is for the Marshals of the TT Races here on the Isle of Man. I am a self taught amateur who designed the Office Database. I share and link information (data) with a friend who looks after the website side. He is a professional and so criticises me (in a reasonably kind way) for, as you spotted, storing similar data in different tables. I am at a stage now where I am nervous to completely restructure the database as there is vba all over the place referring to my tables as they stand. There are 2 events a year so for this year I have created a TblSessionTT14 and tblSessionGP14. These basically store the Description of a Practice or Race Session for lists or reports. There is a matching TblWWTT14 and TblWWGP14 which stores all Marshals who have signed on for each Session and where they will Marshal. The 37.75 mile Course is divided into 12 Sectors, then they divide up to named Corners, which I called Sections. So a record will say Marshal (Ref_No) 8000 will be at Section 5 in Sector 3 for SessionID 14101. The matching TblSessionTT14 will hold a long and short description of the Session for use in Forms or reports. 14101 just means 14 = Year, 1 = TT (2 = GP) then 01 is the first of 18 Sessions. I store the tables going back to 09 when we started this format. My friend uploads the TblWWTT14 into his Website database every 20 minutes for other functions and at the same time the Secretary in the Office uses my database.
Sorry that is long winded and personalised description but the database performs many other tasks.
I realise straight away from your comment that I could have just one TbleSession as SessionID will identify the Year / Event / Session and so I don't need a unique table.
As a quick test I just went into Table relationships and linked the SessionID fields in TblWWTT14 and TblSessionTT14. Then I went back to my troublesome SubForm which has a RecordSource of TblWWTT14 in my [Session Name] Textbox I was hoping that in its ControlSource I would see the fields from TblSessionTT14 in the dropdown but I still only see the ones in TblWWTT14.
I then tried to create Query using the 2 tables:-
Col 1 was TblSessionTT14.DescriptionForCard
Col 2 was TblWWTT14.SessionID with Criteria = [Forms]![SubWW]![SessionID]
(I don't know how to show you the real query in this reply)
If I try to run this as a test it prompts me for [Forms]![SubWW]![SessionID], if I type in 14101 I expect to see the Description with the words "Sat 24th May (Practice)" but don't get anything.
Am I close or is that too big a question for a simple answer

Thanks

 

[notconfident]

I am now trying something different, MajP replied to my original question with a comment about the way I have built my database. I agree but couldn't use his advise for my problem.
I think my database is quite good, 9000 Main records, but I do struggle to get some bits to work.
I am good at Google combined with copy / paste but am having no luck this time !!??

The 2 Textboxes in the Subform use two different tables than the one that the SubForm is linked to to populate themselves
but both use SessionID as a reference which is a textbox (integer) on the SubForm and changes with every Subform record (unique).
I tried to populate them in the OnCurrent Event of the SubForm but with some checks of my own and Googling I discovered that a SubForm doesnt have an OnCurrent Event for every record so the value in the 16 records in the SubForm were all the same. Good thing was that although the value was no good it was fast and thats what I want !!.
Using the 2 Dlookups in the ControlSource box works but was too slow.

Any ideas on this new attack, as to what event I can populate the controls by referencing SessionID on the Subform
Thanks