Prevent copying from textboxes on a form 1

[maccess]

My Access 2000 database has a number of continuous forms. Some users want highlight records using the record selector, and paste those records into MS Excel or e-mail messages. This is done either by copying and pasting or by choosing Tools > Office Links > Analyze It with MS Excel from the toolbar. However, some values from unbound text boxes in the form footer are also being added as additional columns to every record that is copied. These unbound text boxes contain sums and count totals for all records on the form, and do not contain data about each individual record. Is there a simple way to prevent these controls (or the values contained in them) from being added on to each record copied to another program?

Max

 

[CaptainD]

Have you tried changing the "Lock" or "Enable" property?

 

[maccess]

The form was setup with the Enabled property set to "no" and the Locked property to "yes", for the all text boxes in the form footer.

Max

 

[maccess]

I should have explained in my post above, that I tried various combinations of "yes" and "no" settings for the Enabled and Locked properties but the text box information from the form footer is still copied.

Max

 

[ProgramError]

change it to a label

Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[maccess]

That's a great idea Ian. Thanks.
I ran with it and added some VB code to update the label captions in the forms On Current event.

Bundles_Count.Caption = "Total Bundles: " & Format(Me.RecordsetClone.RecordCount, "#,###")
(The label won't update however, if the form is filtered and the filter returns no records.)

Having tried that for the form footer unbound controls, I now want to do something for some bound controls in the detail section. That includes some text boxes and two check boxes. The check boxes are for the user to choose records to print and to indicate which records have already been dealt with by the user.
I created a command button for test purposes, which set the visible property to False for some controls, then reset them to True after copying. This command button only allows copying for the current selected record, not a range of records highlighted with the record selector.
Is there a way in VBA to temporarily set the visible property to False, when copying records on a continuous form whether using the Menu bar Edit > Copy or Tools > Office Links > Analyze It with MS Excel or copying from a right click shortcut menu?

Max

 

[ProgramError]

I don't know of a property which returns those records which have been selected using the record selector as you describe. otherwise you could have used and append query to just copy the fields you want without having to hide those that you don't want copying.
You could always use the mouse down event to hide the fields like you did with the command button. Then unhide them when the form has focus again.


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[ProgramError]

Just checked some of work where I prevented changes or highlighting details in a textbox and used the settings below which should work fine.

enabled no
locked yes


Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[maccess]

Well, I gave up on this line of thought.

Instead, I now have users highlight the records they intend to copy, using the record selector. They can then switch from the Form view to Datasheet view. From there they use CTRL+C or Edit>Copy from the menu bar and then paste into another program.
This way the user can align or hide columns the way they want and save that layout. Also, it avoids having to code in the forms timer event to capture what records are being selected. Any time a user clicks anywhere else on the form, the record selector loses focus, and the selected records are deselected. However, clicking on the menu bar or changing to the Datasheet view with the toolbar does not cause the record selector to deselect the records.
Incidentally, I'm using Access 2000, and in order to change datasheet column names I had to attach labels to some of the fields on the main form (reference thread702-496824) with the names I wanted. Then I set those labels Visible property to 'No'. I did this because I have a continuous form styled as a datasheet, meaning , no labels in the detail section. However, I still wanted some form elements such as totals and buttons in the form header and footer sections, along with the spreadsheet styled view.

(Apparently, after a lot of trying, putting a caption in a fields caption property in a query or table, was not propagating to form datasheet column names. I'm sure others know about this, I just couldn't find this anywhere, so I hope the above info helps someone.)


Max