How to clear a bound control in a Form?

[Guest]

New to Access - need help!

I have two Combo Boxes, Sector and Site on a Form for inputting data.
Sector is unbound while Site is bound.

I use the Sector to select a sector.
Next, I filter the list for Site based on the value selected in the Sector.

After selecting the Sector as well as Site, the user may realize that s/he
made a mistake and want to change the Sector. If so, the filtered list for
Site will change. I requery the Site after changing the Sector using
AfterUpdate event for the Sector. This gives me the updated filtered list in
Site (as desired).

However, the previous value in Site "hangs around" till I select a new value
from the Combo.

What I would like to do is clear the previous value in Site as soon as
update the value in Sector so that the user sees an "empty" field rather than
seeing the previous value.

I have tried the following code in the AfterUpdate_Sector, without success.

1. Me.Site.Undo ; nothing happens
2. Me.Site = Null ; this gives me the following error.
run-time error '3162'
You tried to assign the Null value to a
variable that is not a variant data type.


Will greatly appreciate any help pointer to get the desired behavior.

M. Arshad

 

[Ken Snell \(MVP\)]

This should work in your AfterUpdate code:

Me.NameOfComboBox.Value = Null

If it also throws the error, try this:

Me.NameOfComboBox.Value = ""

(use the actual name of your combo box, of course).

 

[Guest]

Ken,

Thanks a lot for the response.

Me.NameOfComboBox.Value = Null

does not work.
I get exactly the same error as I got with 'Me.NameOfComboBox = Null' and
that is what you would expect anyway, right?

Me.NameOfComboBox.Value = ""

does work provided "Allow Zero Length" property of the 'Control Source' for
the 'NameofComboBox' is set to "Yes".

Is it at all possible to have the 'Control Source' properties:

"Required" set to "Yes", and
"Allow Zero Length" set to "No"

and still clear the control?

Or is it a limitation (i.e., set "Allow Zero Length" to "Yes") that I need
to live with, to get the desired behavior?

Would appreciate your insight.
Thanks a lot.

By the way, others have reported "Me.NameOfComboBox = Null" work for them.
Any idea why "Me.NameOfComboBox = Null" or "Me.NameOfComboBox.Value = Null"
does not work in my case?

Thanks again.

Regards,

M. Arshad

 

[Ken Snell \(MVP\)]

Because the Null code step is not working, there must be an issue with the
data type of the field that is bound to the combo box.

Go to design view of the table that contains that field, and tell us what
the properties of that field are. Perhaps your field won't accept Null
values.

Also, give us the following properties for the ComboBox itself:
Format
ColumnCount
ColumnWidths
RowSourceType
RowSource
ControlSource
Bound Column

 

[Guest]

Properties of the field in the table are:

Field Name: SITENUM
Data Type: Text
Field Size: 10
Caption: SITE NUMBER
Required: Yes
Allow Zero Length: No
Indexed: No
Unicode Compression: No
IME Mode: No Control
IME Sentence Mode: None

The rest of the properites are blank.

The properties for the ComboBox itself:
Format: 'blank'
ColumnCount: 1
ColumnWidths: 1"
RowSourceType: Table/Query
RowSource: SELECT tblSites.SITENUM FROM tblSites WHERE
(((tblSites.SECTOR)=Forms!frmCreateImStat!SECTOR));
ControlSource: SITENUM
Bound Column: 1

'frmCreateImStat' is the name of the Form.
SECTOR is unbound control and SITENUM is a bound control in the Form.

'tblSites' is a static table that lists all the sites (alphanumeric)
alongwith the SECTOR and some other information.

Thank you so very much.

Best regards,

M. Arshad

 

[Rick Brandt]

Properties of the field in the table are:

Field Name: SITENUM
Data Type: Text
Field Size: 10
Caption: SITE NUMBER
Required: Yes
Allow Zero Length: No
Indexed: No
Unicode Compression: No
IME Mode: No Control
IME Sentence Mode: None

If the field is required you cannot clear it. The one exception is if you have
filled it on a new record that is not yet saved you can use Undo to clear it.
On a saved record a required field is required to have something in it even when
the record is dirty.

 

[Guest]

Hi Rick,

This is a record which is not yet saved.

'Undo' was my first choice but I could not get it to work for the individual
control.

I used the following code

Private Sub SECTOR_AfterUpdate()

Me.SITENUM.Requery
Me.SITENUM.Undo
...

Any ideas why the Undo would not undo.
I have used Undo at the Form level and it works fine - clears all the entire
record.
But I have not been able to make it work at the control level.


Regards,

M. Arshad