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 :).

[MMcCarthy]

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?

At what stage do you want this event 'triggered'?

After the date is entered in the Water Subform or when user clicks into Fish subform or when the main form is opened or record changes?

Knowing when you want to trigger the event will determine where the code goes.

Mary

[lokidog]

So I put the new blank test - for a null value in the data into the gotfocus event procedure and it works - but again like it did before. You move into the 'fish date' text box control, and nothing is there. Once you move away, up pops the date from 'water date'.

I want the date to show up when I move into the textbox, then allow editing if necessary - or simply tab to the lower subform where you enter data for fish.

Could if be the complicated way these forms are referenced and linked. The Fish subform is actually 2 forms (in a Tab) - The main one has the date on it - and is linked to the main form via a query. This was perhaps my most difficult thing to understand. I am still not sure I DO understand it - but it works. Its a query that's first field is:

Field Data Collections.*

The second field is 'Keynumber' - the number that links the field data collections with the sub tables/forms.

It has a criteria of:
[Forms]![Main Field Data Collection Form]![Field Data Collections Subform].[Form]![Keynumber]

Here is the SQL code:

... SELECT DISTINCTROW [Field Data Collections].*, [Field Data Collections].Keynumber
FROM [Field Data Collections]
WHERE ((([Field Data Collections].Keynumber)=[Forms]![Main Field Data Collection Form]![Field Data Collections Subform].[Form]![Keynumber]));
 

Each subform uses this Query as its Form Record Source.

Then each subform is linked to it's respective table (Fish to the Fish table), and linked child and master by 'keyvalue'.

The sub subforms - under the 'date' form is also linked by the keyvalues.

It works really well and looks very nice!

[lokidog]

Mary -

I thought I had said the problem was with the 'when' of the the event already. But I guess I did not make that clear. I can get it to work - but it's when that is the problem.

There are really a couple points when it could be triggered - the main thing to maintain is that the date should remain blank - unless data is entered in the subform. So - if there is no fish data, then no date is entered.

So - practically, the date could be transfered into the control - that is the event made to work - ideally when the 'Fish Date' text box control is entered - or when data is entered in the Fish Data subform. Perhaps it should occur upon either event to give the user most flexiblilty.

Now another possibility I've thought of, but messy, is to populate the 'Fish Date' text box control immediately upon clicking on the subform Tab(I think I've been able to do this), then deleting the data if no Fish Data is entered. It would work, but there is always the possibility that quick or unusal navigation through the forms could result in messy data.

[lokidog]

Could there be something like Me.Refresh? An update command? Using the .value property of the Control? That I could use in the evenprocedure of the 'Fish Date' Textbox control - or in an eventprocedure of the form, subform, etc.?

[MMcCarthy]

Have you tried putting ...

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

into the default value.

BTW, Don't put square brackets around the .Form commands as they are not fields.

Mary

[lokidog]

me.refresh !

That was it! Now when I click on the 'Fish Date' control, it puts in the date from 'Water Date' and it shows up instantly without having to leave the control. That way if it is correct I simply tab or click to the next data entry point, or I edit the date to the correct one! Perfect. Now I have to add that to several other forms as well. Thanks!

Who knows why it was not refreshing (Updating was the wrong syntax for the action I desired)

What did I do? - I added the me.refresh command to the GotFocus eventprocedure, resulting in:

Private Sub Fish_Date_GotFocus()
 If IsNull(Me.[Date Fish]) 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]![Water Date]
    Me.Refresh
End If
End Sub
 

I woke up with this idea and did a Web search - just goes to show what 'sleeping on it' can accomplish with a problem. My other ideas concerned ways to refer directly to the Query that the subform was based on..... But I had no idea if that line of thought would have led to anything useful either!

[MMcCarthy]

I'm glad its working for you.

me.refresh !

That was it! Now when I click on the 'Fish Date' control, it puts in the date from 'Water Date' and it shows up instantly without having to leave the control. That way if it is correct I simply tab or click to the next data entry point, or I edit the date to the correct one! Perfect. Now I have to add that to several other forms as well. Thanks!

Who knows why it was not refreshing (Updating was the wrong syntax for the action I desired)

What did I do? - I added the me.refresh command to the GotFocus eventprocedure, resulting in:

Private Sub Fish_Date_GotFocus()
If IsNull(Me.[Date Fish]) 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]![Water Date]
Me.Refresh
End If
End Sub
 

I woke up with this idea and did a Web search - just goes to show what 'sleeping on it' can accomplish with a problem. My other ideas concerned ways to refer directly to the Query that the subform was based on..... But I had no idea if that line of thought would have led to anything useful either!

[NeoPa]

Me.Refresh is good, but if there is any chance that data is updated elsewhere (someone else running the same database for instance) the Me.ReQuery may suit you better.
Refresh will show changes to individual records, but will not reflect additions or deletions done elsewhere.