Clearing specific cells with command button

[Kevin Field]

Is it possible, on the click of a button, to clear the contents of a particular cell(s) in a table?

I have a data entry form. On the click of a button i want to be able to purge all of the information in the underlying table except the clients ID and Name etc.

I dont want the whole record to be deleted, just clear the individual cells...

Any help out there would be greatly appreciated!

 

[Trevor G]

Create a command button on your form but don't let the wizard start you selecting anything.

Then open the properties of the button and go to on click event and then open the VBA screen.

All you need to do then is add some of the following

me.textboxname1.value = ""
me.textboxname2.value=""
me.textboxname3.value=""

And so on, each object on the form will have a name so you need to know them first. If you have numeric fields the value should be set to Null

And you have a solution created.

 

[Kevin Field]

Thanks for thsi Trevor. However, when i run it i get the following message:

"You tried to assign the null value to a variable that is not a variant data type"

Any suggestions?

Ive googled the error and come up with mumbo jumbo.

Thanks!

 

[Trevor G]

Just use the "" as the value then

me.textboxname.value=""

 

[Kevin Field]

Sorry,

What was different about that code to the ones above it?

Or am i just being stupid?

 

[Trevor G]

I'm suggesting you don't use .value=Null or perhaps you do if its a numeric field you want to empty.

You have to be aware of the datatype for each field.

 

[vbaInet]

Just adding a little to this, if you don't like the zero-length string, i.e. "", you could use vbNullString

 

[Kevin Field]

Thanks for the help Trevor and VB,

I worked out that the issue was primarily through the table links. Once i rectified them, the script worked perfectly.

I have gone with vbNullString only because i saw that suggested somewhere else as well.

Again, thanks for both your help.

 

[Kevin Field]

As an extra, im trying to work out how to requery a subform on the 'on click' event of a command button. The command button is located on the main form "AssignedItems" and the sub form to that is called "AssignedItemsByUserSub"

Ive tried some VB but i am poor at it at best.

I have also tried the requery option in macros but still no joy.

Thanks!

 

[vbaInet]

Me.NameOfSubformControl.Requery

 

[Trevor G]

you would refer to the subform name change the red to your sub form name

Me.frm386PolicySearchNew.Requery

When you use me. it should then list all objects in the main form, so the subform name should appear.

Glad you sorted the null issue.

Have a good weekend.