Individual cells in 'Datasheet View' - any associated events ?

[liamfitz]

Here's the situation. I'd like to get soem derivative information from a field/cell, as soon as the user exits that cell, having entered ( in this case ) a date value. As this also automatically creates a new record ( i.e. generates an Auto_Number for the P.K. field ), I know it is the 'Current Record' therefore, in my sub-form ( sfrmRefs ). Any ideas, what if any event fires at this point ( The subform only appears to have two associated events - 'On Enter' and 'On Exit' ) ? Is it an event on the Main Form ? R.S.V.P.

 

[rodmc]

if your updating the field the I would think it is after update event

 

[liamfitz]

Thanks. I'll check it out ( on Main Form presumably ).

 

[boblarson]

( The subform only appears to have two associated events - 'On Enter' and 'On Exit' ) ?

You are mistaking the subform control (control that displays/holds the subform) with the subform itself. The subform has the same events that the main form has but On Enter and On Exit do not work on the subform when it is on a form as a subform.

rodmc is correct, you can use the events of the individual control (Lost focus, before update, etc.) if you wish.

 

[liamfitz]

I take your point, but there are two things you should know (I'm wondering if the latter, is causing this difficulty ) Firstly, the actual control i.e the sub-form itself ( in my case it's named sfrmRefs, ONLY has two events assocaited with it in the properties sheet i.e 'On Enter' and 'On Exit' neither of which work ) Secondly, my subform is not linked with the Main Form ( in the 'normal' way at design-time, i.e. linking fields Parent/child etc. ) I 'link' them programmatically at Run-time only. Does this clarify anything ? ( After_Update doesn't work either, but then for these reasons possibly, it's an event only avavilable and associated with the Main form, not Sub form at the moment ) Thanks.

 

[boblarson]

I take your point, but there are two things you should know (I'm wondering if the latter, is causing this difficulty ) Firstly, the actual control i.e the sub-form itself ( in my case it's named sfrmRefs, ONLY has two events assocaited with it in the properties sheet i.e 'On Enter' and 'On Exit' neither of which work )

Yes, the subform control only has two events but that isn't even applicable to your situation. You can still use the events on the SUBFORM ITSELF (like I wrote earlier).

Secondly, my subform is not linked with the Main Form ( in the 'normal' way at design-time, i.e. linking fields Parent/child etc. ) I 'link' them programmatically at Run-time only. Does this clarify anything ?

Show me the code that you are using to do that, please. It might help.

( After_Update doesn't work either, but then for these reasons possibly, it's an event only avavilable and associated with the Main form, not Sub form at the moment )

Again, your subform has all of the events that the main form has. You can either get to them by opening the subform in design view (not from the main form) or you can click on the little square in the corner of the subform control to select the subform and then properties for it (including the After Update event will show in the Events tab.

See:

 

[liamfitz]

I'm running ACCESS 2010, and my subform does not look like the sub-form you use to illustrate your point ( if it did i.e. very much like the design view of a main form, I would have found these events/properties ) My sub form is a simple rectangle, with the name of the Query in the corner, that it uses as its recordsource. You've been very helpful, but this looks like an older version of Access. Let me try deleting the current subform, and adding another from the ribbon, to see if the design resembles what you're showing. I know it would work then ..

 

[boblarson]

Your subform is just the table. Create a form from the table and then add it as a subform. Don't drag and drop. Microsoft screwed up that part (it used to work just fine) as well as other stuff each time they do a new version.

 

[liamfitz]

When you say don't 'drag and drop', I woudn't know how to. Do you mean creat a new 'Main Form' based on a table ( query )

 

[boblarson]

When you say don't 'drag and drop', I woudn't know how to. Do you mean creat a new 'Main Form' based on a table ( query )

Instructions:

1. Click on the table you want to have as the subform's data source. No need to open it just click on it to select it.

2. Go to the CREATE tab and select FORM (not Form Design). It will automatically create the form for you.

3. From that form's design view change the Default View property to Datasheet.

4. Save the table with a name like sbfrmXXXX so that you know it is a subform.

5. Open the Main form in design view.

6. Drag and drop that new subform onto the main form.

7. It will come up with the link dialog so you can make sure that the correct linking is provided.

8. Save the main form.

9. There you go.

 

[liamfitz]

Thank you very much, I'll try this on Monday, when I'm back at work.

 

[liamfitz]

Thank you, that appears to have worked, up to and including, the 'drag & drop'. It didn't prompt me to link the fields ( I know there are properties which I'll try working on though... Parent/Child etc. ) Thanks again. Easy when you know how eh ?

 

[liamfitz]

The new Subform is working as it should, BUT although I've set the default view to Datasheet it includes ALL the fields from the TABLE. I have to hide them in design view ( so it is clearly not 'adapting the Fields, to only those selected in my Query. In fact it ignores the query COMPLETELY. ) This is important because, I would like to Add to the number of fields shown, triggered by adding data to existing ones 'on the fly' ( changing SELECT statement programatically. ) Any suggestions - can I base my Form at creation time on my BASE QUERY, rather than on a TABLE. ( Boblarson ? )

 

[boblarson]

You should be able to set the .Visible property of the controls bound to those fields to False so they don't display on the subform.

 

[liamfitz]

Many thanks again for your response ( clever - I take it you mean when it first creates the Form in 'Normal view', before setting Default view to datasheet ? ) I've worked out a 'better' way though - I created another Form in exactly the same way, but used my 'BASE' query instead of a TABLE ( which obviously filters the data to the view required only ) I can now adapt this in the run-time environment to be adapted as and when required. Your tips and tricks are very helpful.