Subforms Are Sleeping... 1

[jnuus]

Greetings experts,

Stupmed on this one, and a good scouring of the internet has unfortunately turned up little.

I am developing a simple DB in ver 2007. It consists of a main form which contains routine data like last name, first name, etc. On my frmMain is a single tab control with 3 subforms, one for each tab. The subforms have date fields that correspond to an employee's training dates. I have queries built to return data from the subforms like "expired" or "current" training dates, and if a date field does not contain data, I set the queries to tell me that too.

Problem is, when I create a new record, my queries will not see the new record unless data is entered somewhere into the subform. At that point, all of the queries for that tab run perfectly, even for the empty fields.

Merely giving focus to the subform does not resolve the issue.

This is problematic for my DB because brand-new employees will have not training dates of course, but I need to track those empty fields and populate them as their training occurs.

Any nudges in the right direction from the Access gods would be much appreciated. Thx.

 

[MazeWorX]

what event are you using to fire the code?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[jnuus]

There is no event associated with this issue.

I may have not done a good enough job explaining the problem, so here is a watered-down, generic version of my DB...

frmMain has:
[tt]LAST
FIRST
SSAN
DIVISON[/tt]

A tab control is on frmMain with subforms under each tab. The subforms contain text boxes that hold dates:

[tt]Primary Training (Tab0)
txtPrimaryTrainingItem1
txtPrimaryTrainingItem2
txtPrimaryTrainingItem3

Auxiliary Training (Tab1)
txtAuxTrainingItem1
txtAuxTrainingItem2
txtAuxTrainingItem3[/tt]

The primary table has a one-to-many relationship with the two training tables.

I then built a query to return the entire roster for say, [tt]AuxTrainingItem2.[/tt]

My problem is that unless I go to the tab and enter data into the subform under that tab, that person will not be caught by the query. I need my queries to return everyone for each training item, regardless of whether they have a date in the field or not.

I hope this is a little clearer - sorry if I muddied the issue.

 

[PHV]

that person will not be caught by the query
Use a LEFT JOIN

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jnuus]

PHV,

You are, of course, correct. Kind of feel like I wasted your time on that one - all those stupid trees were blocking my view of the forest...

But thank you very much, and have a star.