Textbox Data Transfer not updating when control is active

[lokidog]

I am trying to automatically transfer data from one textbox to another between subforms within a 'main' form. I put this code into the Gotfocus eventprocedure:

Private Sub Date_GotFocus()
If Len(Trim$(Me![Date Fish].Value & "")) = 0 Then
'This checks for blank or space-filled date field - it will only transfer data if it has not already been entered.

Me![Date Fish] = [Forms]![Main Field Data Collection Form]![Field Data Collections Subform].[Form]![Water].[Form]![Date]

SendKeys "{F2}"
SendKeys "{TAB}"
SendKeys "+{TAB}"
End If
End Sub

It works - to an extent. The Sendkeys parts are trying to get the data to show up. It only shows up when I move to another control or subform.

But this is not how I want this to work - and maybe I am not doing this correctly at all.

I enter a date in one subform ("Water" - where data is always collected for that record)

Then I move to another subform ("Fish" where data is only entered for some records). Most often the Date will be the same - but not always. So when I move into the date textbox, I want the date from "Water" to move into the box. Then if it is different, I can edit it, otherwise it is 'defaulted' to the 'Water' date.

Is there a way to do this? I tryed defining the default value, but did not have success - perhaps I did not do it correctly.

The real problem is having the date entered only if data is entered in that subform. If no data is entered, then the date should not be entered either - That means I can not simply populate all the subform dates upon 'Loosing focus' in the 'Water' date subform either.

Hmmmm?

[MMcCarthy]

You should put this in the Before Update event and your syntax for finding the date on the subform is wrong.

If [Date Fish] is not a Date/Time data type it should be. Then you only have to check for null value.

I would also suggest you change the name of the field in Water subform from Date to something else as Date is a function in Access.

 
Private Sub Date_BeforeUpdate()
 
   If IsNull(Me.[Date Fish]) Then
      Me.[Date Fish] = [Forms]![Main Field Data Collection Form]![Field Data Collections Subform].Form![Water].Form![Date]
   End If
 
End Sub
 

[NeoPa]

Do we want ...Form![Water].Form![Date]?
I would have thought ...Form![Water].[Date]
would be the correct format?
(See I'm learning new stuff all the time - a week ago I wouldn't have had a clue even to ask the question)

[MMcCarthy]

Do we want ...Form![Water].Form![Date]?
I would have thought ...Form![Water].[Date]
would be the correct format?
(See I'm learning new stuff all the time - a week ago I wouldn't have had a clue even to ask the question)

We have a subform on a subform with a control. Each subform needs the .Form

Mary

[NeoPa]

We have a subform on a subform with a control. Each subform needs the .Form

Mary

In that case would I be right in thinking the .[Water]. part refers to the second level subform object?
In which case we want :
[Forms]![Main Field Data Collection Form].[Field Data Collections Subform].Form.[Water].Form.[Date]
but (!)s can be used in most cases to replace the (.)s if unsure.

[NeoPa]

Before you answer that one Mary.
I reread the original post and it looks like two sibling subforms rather than a grandfather - father - son relationship.
Are you sure it SHOULD be referenced as it has?

[MMcCarthy]

Before you answer that one Mary.
I reread the original post and it looks like two sibling subforms rather than a grandfather - father - son relationship.
Are you sure it SHOULD be referenced as it has?

I based the code on the original code which indicated that there was a subform Water with a control called [Date] which was on a subform called 'Field Data Collections Subform' which was on a main form called 'Main Field Data Collection Form'.

If this is not the case then the code is wrong.

Just have to wait to find out.

Mary

[MMcCarthy]

I based the code on the original code which indicated that there was a subform Water with a control called [Date] which was on a subform called 'Field Data Collections Subform' which was on a main form called 'Main Field Data Collection Form'.

If this is not the case then the code is wrong.

Just have to wait to find out.

Mary

BTW, in this case it doesn't matter that Fish is a subform as long as the reference is on that form.

[lokidog]

First Thanks much for everything. I changed the Textbox names from date to 'water date' and 'fish date'.

Mary - the code you offered does not work in the Beforeupdate eventproceure area. Nothing happens when put in the Beforeupdate eventprocedure.

The null check is not the problem, This is better than the one I had (I got is from another post here - and seemed sort of unnessesary as date formats won't allow spaces!)

But the Before update event is not registering. Don't know why. Must be a Form Flow Problem.

Maybe this explanation of the forms will help?....

Yes, it's a rather complicated form - but once working it is actually easy to use. The Water form and the Fish Form are 'Sister' forms - on the 'Field Data Collections' Form, which itself is a child form of the 'Main Field Data Collections Form). It allows a data collection of fish and water quality data for a site (field data collection) for numberous years (Main Field Data Collection).

But the dates for each are actually part of the Table that is 'Mother' of both Water and Fish, that is they are on the Field Data Collections Table - there is a 'Water Date' and a 'Fish Date' field on that table. They are entered on the subforms since that is where one enters the water and fish data. The Parent form is for other physical data - so you don't have to jump around a lot on the form.

Maybe this will help?

[NeoPa]

No prizes for guessing your gender then ;).
I should just point out that I was not being sexist when I referred to the term 'grandfather - father - son', it's a common term that's been in use to describe this situation for a long time. Your terms are, of course, quite acceptable and intelligible too :).