Clear all boxes in form

neilCarey

New member
Local time
Today, 01:57
Joined
Oct 5, 2021
Messages
9
Hi guy's

I have a form called 'costings' which works out cost - VAT - PLUS VAT ect..

I have the problem where in for example -Property Sheet - Data - Control Source =[CRMCost]*0.2

Because it doesn't have a name 'txtCost' I can not write VBA to clear these boxes 'TextBox,ComboBox,CheckBox,ListBox'

I'm not interested in Me.TxtCost.Value = ""

I just want a click button that I can 'Call' from a Public Function that will clear all boxes from any form that I would like to clear, on any given form.

Kind regards,

Neil.
 
Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!
 
Hi. Welcome to AWF!

An unbound textbox with an expression for its Control Source cannot be edited. The only way to clear it is to remove the Control Source expression. Is that what you want?
 
Hi

Thanks for getting back so soon.

I need the control source there as it works out mathematical costs.

When a user goes into the form and works out costings they may need to reset the form and clear it to re put the the new cost in.

if that makes sense.
 
Are you clearing crmCost if so this would clear naturally by the expression.
When you clear the controls make sure you set them to NULL not ""
 
I've tried that and it will clear a box that has txtCost for example, but because it has an expression in it, it will not clear.
 
If crmCost is not one of the cleared controls at what point do you want to start calculating again.
But my guess you bigger problem is your are storing calculated data. You should never do that. Calculate data for display dynamically do not store it in a table.
 
Hi

Thanks for getting back so soon.

I need the control source there as it works out mathematical costs.

When a user goes into the form and works out costings they may need to reset the form and clear it to re put the the new cost in.

if that makes sense.
Okay. One way to do this would be to use the Tag property of each control to identify which ones need to be cleared. Then, you would have to pass the name of the form to your function.
 
Untitled.png
 
If you Set CRM cost to NULL then all controls that use CRM cost in the calculation likely go to NULL
NULL * X = Null
Null +/- X = Null
X/null = Error

Lets see your method for clearing. You tag the proper controls like @theDBguy said. And only clear those fields with a bound field reference.
 
Okay. One way to do this would be to use the Tag property of each control to identify which ones need to be cleared. Then, you would have to pass the name of the form to your function.
And what VBA would you suggest?
 
If you Set CRM cost to NULL then all controls that use CRM cost in the calculation likely go to NULL
NULL * X = Null
Null +/- X = Null
X/null = Error
OK what VBA would you suggest.
 
can you post a sample?
 
can you post a sample?

Code:
Option Compare Database
Option Explicit

Public Function ClearAll (frm As Form)

Dim ctl As Control

For Each ctl In frm.Controls
   Select Case ctl.ControlType
      Case acTextBox
           ctl.Value = ""
      Case acOptionGroup, acComboBox, acListBox, acCheckBox
          ctl.Value = Null
      Case acCheckBox
         ctl.Value = False
   End Select
Next
End Function
 
Last edited by a moderator:
Code:
Public Function ClearAll (frm As Form)

  Dim ctl As Control
  For Each ctl In frm.Controls
    if ctl.tag = "CLEAR" then ctl = null
  Next
End Function

Tag only the clearable controls, not the ones with calculated values.
 
I'm trying to 'Call ClearAll' when I click the clear button and nothing is working.
 
When you tagged the controls did you type
CLEAR
or
"CLEAR"
in the tag property? Should be the prior
 
Maybe the best thing to do is write VBA for all boxes to work out cost and VAT, Plus VAT and total amount with a button that clears all boxes if a user needs to.

At the moment nothing is working.

Kind regards.
 
Did you try any debugging?
1. Is it looping the fields?
2. Is it finding the tagged fields?
Simple debugging should show that?
 
Maybe the best thing to do is write VBA for all boxes to work out cost and VAT, Plus VAT and total amount with a button that clears all boxes if a user needs to
Having functions as control sources are nice because they automatically recalculate the values with changes in the inputs. If you set it in vba then you have to make sure to call the procedure to set the values on the afterupdate of the controls holding the inputs.
 

Users who are viewing this thread

Back
Top Bottom