I'm not sure if this is possible --

I have a form and a subform. The form contains general treatment information, and the subform contains each individual treatment session information, so it's a one-to-many relationship. When I put in the general end date of the treatment in the main form, I want the end date in the first record in the subform to also fill with the same value, because the people doing the data entry are a little lax about filling that. I don't want to make it a fixed linked parent/child field, because sometimes is does get changed to the precise date that it should be. You can do this on just a plain old form, but I can't figure out how to get it to work with a subform.

On the On Exit event from the date field, I've tried:

If IsNull(Forms![tbl_ChemoTx].TreatmentDate = True Then
       Forms![tbl_ChemoTx].TreatmentDate = Me.EndDate
End If

But I get error 2450. Any ideas?

Thanks,
Jocelyn

There are a couple ways to do this. I hate bang dot notation, but you will at least need to do this:

If IsNull([Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = True Or Then [Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = "" Then
       ([Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = Me.EndDate
End If

That being said, I am not sure if this will work on the On Close event since the database may not see them as closed.

Another option is to go to Table design for the SubForm table, and make the TreatmentDate a required field, so that the database will prompt the user for a value if they try to close the main form without supplying the closed date.

What I would do is create a Module with This sort of code:

Public pChemoID As Integer

This will create a Public variable that you can then set on your main form's On Current Event:

pChemoID = Me.ChemoID

This will change it every time your main form goes to a new record. I am assuming that you are using a PK of autonumber.

Then, what I would do is on the On Close event of the form, I would run this sort of code. I am assuming again that in your sub form, you have a one to many, and that each TreatmentDate should have a value, otherwise it is the TreatmentDate Field you want to populate from the main form. So what you would do is check the main form table for a value in the TreatmentDate field, then take that value, take the pChemoID, then check the sub form table for all records where the FK = pChemoID, then check the resutling recordset for TreatmentDate IsNull() or = "", then put your TreatmentDate from the Main table using Update.

If the last one is what you want to do, please post the table structures as needed, and I can post some code.

Did that give you any ideas?


mmcdonal

Sorry, left in the first "Then". It should be:

If IsNull([Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = True Or [Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = "" Then
       ([Forms]![frmMainFormName].[sfrmSubFormName].TreatmentDate = Me.EndDate
End If


mmcdonal

I am having a bad day. This:

That being said, I am not sure if this will work on the On Close event since the database may not see them as closed.

Should have been:

That being said, I am not sure if this will work on the On Close event since the database may not see them SINCE THEY ARE closed.


mmcdonal

Cripes. Sorry for themega-posts. I also meant:

What I would ALSO TRY IF YOU WANTED TO AUTOMATE THIS is create a Module with This sort of code:


mmcdonal

Thanks for all the advice! Ideally I would have built the database differently to either require TreatmentDate or to structure so it wasn't such as issue. However, I didn't build it, I'm just trying to add a few refinements at someone else's request.

I tried out the first piece of code, and I put it on the After Update event, and it's mystifyingly reading a "|" in the code somewhere according to the run time error (I think instead of the "!"). I've retyped it a few times, and get the same results.

If I can't get it to work, I'm going to stick with a strongly worded Message Box.

Thanks again,
Jocelyn

Hi

I know that another more experienced forum member has offered a solution.

Can I offer mine also..I have tried this out and it does work

I have a form subform set up which is bog standard
Customers (Main Form) Orders Subform in a working database

if you set the following code to the after update event of the control where you enter your END DATE it will copy that date to the first line of the records in the sub form

Private Sub Date_AfterUpdate()
If IsNull(Forms![frmPurchOrders]![frmPurchOrdersSub]![Received]) _
And Not IsNull([Date]) Then
Forms![frmPurchOrders]![frmPurchOrdersSub]![Received] = [Date]
End If
End Sub

The first line checks to see if subform date (received) is filled in
The And Line checks ig your END DATE is filled in

If it is then the date is transferred to the first record in the many form

As far as I can tell it does not cause any other problems and of course this will not over write a date correctly entered in to the sub form first record should that happen

Regards

Man Friday

See, there is that bang dot. I have not been very successful getting that to work. I even emailed Allison Balter and after a few attempts to get this sort of thing to work, she just ignored me. So I like this:

Forms![frmPurchOrders]![frmPurchOrdersSub]![Received]

Bang bang bang, forget the dots.

Thanks for that.


mmcdonal

mmcdonal
Hi

I could never remember bangs or dots either. Because of my limited experience I tend to keep this "refer to forms dialogue" in a text file on my desktop.

Not to mention one or two others which are easier to find than in my code library

MF

I just tend to take the PK and look up the FK and fix things with ADO and without the forms. I will try this now. This is a real bug-bear with Access.

mmcdonal