Subform "Activation?"

[Maklian]

Greetings! I have a small relational db with four tables. The main form has a tab ctrl with 3 imbedded subfrms (one subfrm for each tab). The subfrms have txtboxes that only hold date data. I have built queries to display the date data on reports. Queries are simple - only a DateDiff function to calculate an expiration date (or determine if isnull or "", which is also considered expired by my organization).

The Problem: when the user enters a new record (and saves) but does not place a date in the subfrm, the query does not pick it up. Remember, I need the database to treat null or empty fields as an expired date, so I want it to show up on the query for expired dates.

Here is the dumbfounding part... if the user actually enters data in the subfrm, the query picks it up and the subfrm operates as intended, even if the data is subsequently erased from the fields. It almost seems like the subfrm has to be "activated" somehow to be visible to the query.

I am not an expert by any means, but do have some experience with RDMS, VBA, and C++, but I absolutely just cannot figure out why this behavior is occuring.

I would be indebted for any assistance provided. Product is MS Access 2007. Thanks!

 

[dhookom]

Can we assume there is a main table as the record source for the main form? Also, I expect the subforms use the Link Master/Child properties to maintain the relationship between the tables.

There is not a related record in the child tables until you enter a value into a new record. This happens when you enter a date. If you delete the date value from a field in the child table, it doesn't delete the record.

A date field will be either null or a date. I can't be "".

What specifically is your problem? Do you simply need to use a LEFT or RIGHT JOIN in some record source?

Duane
Hook'D on Access
MS Access MVP

 

[Maklian]

Can we assume there is a main table as the record source for the main form? Also, I expect the subforms use the Link Master/Child properties to maintain the relationship between the tables.

There is a main table (tblPersonnel) with typical Last, First, SSAN -type info. It is linked to another table (tblTrng) with the date fields. Master/Child fields are linked.

There is not a related record in the child tables until you enter a value into a new record. This happens when you enter a date.

Aha! I didn't know this. It would seem this is most likely the source of my problem. Using this as an excellent opportunity to learn more, I coded a field on each of the main and subfrm to accept a default value of "" on new record event and sure enough, it worked; the query picked it up.

But it does seem to me that this is not the most graceful way of solving this though - rather grubby. The code I used is:

Private Sub cmdNewRecord_Click()
DoCmd.GoToRecord , , acNewRec
Me.txtNotes = ""
Me!subfrmTrng.Form.txtLevel1trng.Value = ""
DoCmd.RunCommand acCmdSaveRecord
End Sub

If there is a better way of accomplishing this, I would love to learn how.

A date field will be either null or a date. I can't be "".

This made sense after I read your response, but the field seems to accept "" as a default value as per my code above. Does Access convert to Null behind the scenes?

Thanks again.