On Timer event with less interval e.g. 1000 milli seconds (1 Viewer)

[taifoor]

Dear All,

I am facing a problem. It may be a bug.

I have used timer events on my forms with interval of 1000. On timer event has very small code i.e. me.textbox1.columnhidden = false.

Now the problem is that due to this timer event, some code in my other forms and reports not working. I mean that if any of the form which have timer event as said above is opened and simultaneously a report is also opened, when user export to excel that report with a button having vba code of OutputTo excel procedure, then that do not work. But when the timer event form is closed, it works OK.

So can i solve this or it seems a bug of Ms Access

 

[Minty]

This sounds like possibly a poor design. I can't see why you would need set a column property every second. What does this actually achieve?

There is a issue in VBA that timer events can cause other events not to fire if the form is loaded.

 

[taifoor]

This sounds like possibly a poor design. I can't see why you would need set a column property every second. What does this actually achieve?

There is a issue in VBA that timer events can cause other events not to fire if the form is loaded.

Thanks for ur great reply. I need that timer event so that a field column in my datasheet can never be hide by user. GOT IT?

You said timer event cause other events not to fire . I think due to this i am facing problem. This makes times event a bad thing for me as it also cause other events to run partially and it leads to security issues in my code. Any alternative for this that u suggest or shall i withdraw timer event use?

 

[Minty]

In that case don't give your users a normal datasheet view. Create a continuous form that looks like a datasheet where you control what they see, and the column widths. You will also gain extra control over the appearance.

 

[CJ_London]

you can disable the rightclick menus for datasheet forms so users cannot select Hide etc by putting

Me.ShortcutMenu = False

in the form open event.

But this will not stop users adjusting columns widths to 0 (which means columnhidden =true)

but there is a way to lock this down here - (not tried)

http://www.lebans.com/autocolumnwidth.htm

Otherwise, usual solution is to use a continuous form which you can make look like a datasheet.

I have seen suggestions elsewhere about using the form timer event, but believe me, it sucks

 

[taifoor]

Yes!! I know that continuous form will solve my issue. But i dont wanna use this as i also allow user to show hide column based on their need ... but they are not allowed to hide/makeWidthToZero of specific columns. Thats y i used timer event technique.

So continuous form does not acheive my objective. BTW thanks for ur suggesstions. I appreciate that.

 

[CJ_London]

rather than use the time event, why not put the code in each controls got focus event instead

 

[taifoor]

rather than use the time event, why not put the code in each controls got focus event instead

Thats very difficult and time taking for each control.

 

[CJ_London]

Thats very difficult and time taking for each control.

rubbish.

In a module create a function

Function ColumnRestore(frm as Form)

frm.textbox1.columnhidden=false

End function

In your form, highlight all relevant controls and against the gotfocus event put

=ColumnRestore([FORM])

i.e. where you would normally see [Event Procedure]

 

[taifoor]

Dear CJ_London,

I also have some other code in timer event of some forms.

The code is

'----------------------------------------------------------
' To refresh form after print preview closes
If PrintPreviewClicked = "Yes" Then
DoCmd.Echo False
DoCmd.RunCommand acCmdRemoveAllFilters
GotoLastUsedRecord
PrintPreviewClicked = "No"
DoCmd.Echo True
End If
'---------------------------------------------------------

the above code is used to take back form to last record after print preview button on form is clicked. As when user clicksprint preview, i made form filter to current record only and show print preview via code. But when user closes print preview, the timer event triggers and removes all filter and move record to last used record.
So in this case, got focus event of controls do not work. So Can u suggest any other option for this code.

 

[Minty]

This looks like a poor process - if they are on the record you are using to open the report why do you need to filter the form just to that record?

If the user has applied a filter to get to that record, then just add a command button to remove the filters that is only enabled if the filter is applied.

 

[missinglinq]

How is

frm.textbox1.columnhidden=false

going to work for all relevant Controls? Won't it only reset textbox1?

In a Standard Module, create this Function:

Public Function ReturnColumn()
  Screen.ActiveControl.ColumnHidden = False
End Function

Save and name the Module anything except ReturnColumn!

Now, in Design View for your Form,

1. Hold down <Shift> and Left Click on each appropriate Control in turn.
2. Go to Properties - Events
3. In the OnLostFocus event enter =ReturnColumn

To prevent the ColumnWidth from being set to Zero, you can return it to being the correct width for showing the longest value by adding this

Screen.ActiveControl.ColumnWidth = -2

to the above Function.

BTW, Using the Timer event, continuously, set at one second intervals, is going to bog everything down...all the time...period! You really need to rethink your approach, here.

Linq ;0)>

 

[CJ_London]

How is

frm.textbox1.columnhidden=false

going to work for all relevant Controls? Won't it only reset textbox1?

Because according to the OP's original post he only wanted to prevent one or two columns from being hidden.

there may well be a textbox2 and 3 for all I know.

With regards your solution, not tested but if a control is hidden - can it still be the activecontrol? . And you hide a column by using the header row, either by right clicking or by adjusting the border - does this select the column as the activecontrol?

 

[isladogs]

Excuse me for butting in but to confirm a point in the last post

A control cannot be hidden if it is active
If a control is already hidden it can't be made active

Similarly a hidden column isn't the active column

 

[taifoor]

Its NOT working if i put code in lost focus event. I need only to always show a single specific field that i named here as TextBox1. Also i dont want to put code on all controls got focus event as this is poor procedure and needs to be always checked/monitored.

I think its better to add code in after update event of form or you people may suggest any other better event.

And also please suggest in respect of my printing code in timer event. Is there any event that triggers when print preview is closed?

 

[CJ_London]

think its better to add code in after update event of form or you people may suggest any other better event.

the after update event of the form is not triggered until the data is saved (i.e. go to a different row and the user has changed something to save). If that works for you then go for it.

Otherwise, for datasheets you need to use a control event -which means applying to all controls. You don't have to use the gotfocus event, you could use the enter or exit events, or if you know users are always using the mouse a click, mousedown, mousemove, mouse up event. But you will need to apply to all controls.

Or continue to use the timer event and live with the consequences

Or use a continuous form and create code to replicate the features you want from a datasheet

Its NOT working if i put code in lost focus event

I was suggesting the got focus event but either way 'not working' does not help us help you. Which code? mine or linq's? code fails with error? - in which case what error? nothing happens? Where have you applied it? We provided 'air code' which you need to fix for field names etc so what is the actual code used?

Also i dont want to put code on all controls got focus event as this is poor procedure and needs to be always checked/monitored.

why is it poor procedure and needs to be always checked/monitored?

I don't think I can contribute any more to this thread. So will not be responding again.

Good luck with your project

 

[taifoor]

CJ_London: Your code works as u suggested to use the code in Got Focus event of all controls.

I tried the sugguesstion of Linq who suggested to put code on Lost focus event of control that needs to be always visible. Thats not wokring means no code is run as control lost focus event do not trigger when u hide column by dragging header.

As i said, putting code in got focus event of all controls needs to be always monitored. Means i have to see every time i add controls in form. I have a lot of forms where i need to not to hide a specific control.

BTW Thanks for ur great ideas and suggesstions. I am working on it and i think key down event can solve my prob more better than after update event.

 

[Minty]

Taifoor - you shouldn't be adding loads of controls to your forms all the time. It should be done at design and possibly tweaked after testing. After that it's done. Constant design change means poorly thought out initial design, or poorly explained requirements.

You seem to be trying to make your form work exactly like a spreadsheet - which isn't really possible and isn't normally the best way. If someone hides a column in a spreadsheet they know they have done it. If your users want to manipulate the data they see in such a way then simply allow them to export all the data to a local spreadsheet and they can mess about with it, hide/filter as many rows columns as they like.

 

[CJ_London]

Means i have to see every time i add controls in form. I have a lot of forms where i need to not to hide a specific control.

write some code in the form open event to loop through all the controls - and if a textbox add the call to the function got focus event.

If you have many forms, with differently named controls to remain visible you can modify the ColumnRestore function to have a case statement either based on the form name (which has been passed with the form object) or the name of the controls to remain visible.

 

[missinglinq]

Sorry, CJ, the Op's statement

Thats very difficult and time taking for each control.

made me think they were talking about a number of controls.

The posted code was tested and works as requested.

Actually, the original code

Screen.ActiveControl.ColumnHidden = False

prevents the column(s) from being hidden, and, if the Width is set to Zero, the column will disappear, but reappear when any other column is selected. This behavior is somewhat startling, however, hence the suggestion of adding the the second line

Screen.ActiveControl.ColumnWidth = -2

Linq ;0)>