Setting a Combo Box to Null

[Paul C]

I have two combo boxes in a form which compare results. If one does not
match the other, I give the user a message and then want to set the value of
the one in error to null. The message box part works well, but I cannot
clear what is in the box so that the user can make a new entry. I have tried


me.combo=null and me.combo.undo but neither work.

Any ideas?

 

[Allen Browne]

This should work unless the combo is bound to a field that is marked
Required, or contains a value that is invalid (e.g. wrong data type, not in
list):
Me.Combo1 = Null

Undoing the combo could handle the Required case, and also the invalid Text,
though it might depend what event you are working in, or if the record has
already been saved.

 

[Paul C]

Thanks Allen - in theory it should work but it doesn't. The contents of the
field are highlighted, but not deleted. It is not bound to a required field
or an invalid value. The selection made is from the combo list itself, so
cannot be invalid - it just doesn't match the other combo which is what I am
checking for.

The full code on the Combo2 change event is:-

If Combo2<> Combo4 Then

MsgBox "Please select a compatible service", vbOKOnly, "Incompatible
Service"
Me.Combo2.Undo
Me.Combo2.SetFocus

End If

I also have a seperate trap in case Combo4 is null, but I haven't put that in
here. The message box displays, and the focus goes to the field, so I know I
am getting to that peice of code.

Any other thoughts?

This should work unless the combo is bound to a field that is marked
Required, or contains a value that is invalid (e.g. wrong data type, not in
list):
Me.Combo1 = Null

Undoing the combo could handle the Required case, and also the invalid Text,
though it might depend what event you are working in, or if the record has
already been saved.

I have two combo boxes in a form which compare results. If one does not
match the other, I give the user a message and then want to set the value

[quoted text clipped - 6 lines]

Any ideas?

 

[Allen Browne]

In what event are you running this code?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen - in theory it should work but it doesn't. The contents of
the
field are highlighted, but not deleted. It is not bound to a required
field
or an invalid value. The selection made is from the combo list itself, so
cannot be invalid - it just doesn't match the other combo which is what I
am
checking for.

The full code on the Combo2 change event is:-

If Combo2<> Combo4 Then

MsgBox "Please select a compatible service", vbOKOnly,
"Incompatible
Service"
Me.Combo2.Undo
Me.Combo2.SetFocus

End If

I also have a seperate trap in case Combo4 is null, but I haven't put that
in
here. The message box displays, and the focus goes to the field, so I
know I
am getting to that peice of code.

Any other thoughts?

This should work unless the combo is bound to a field that is marked
Required, or contains a value that is invalid (e.g. wrong data type, not
in
list):
Me.Combo1 = Null

Undoing the combo could handle the Required case, and also the invalid
Text,
though it might depend what event you are working in, or if the record has
already been saved.

I have two combo boxes in a form which compare results. If one does not
match the other, I give the user a message and then want to set the
value

[quoted text clipped - 6 lines]

Any ideas?

 

[Paul C]

Hi,

It is in the Combo2_Change event.

In what event are you running this code?

Thanks Allen - in theory it should work but it doesn't. The contents of
the

[quoted text clipped - 42 lines]

 

[Allen Browne]

Okay, that's the issue.

The Change event fires with every keystroke.

Try the AfterUpdate event of the combo.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It is in the Combo2_Change event.

In what event are you running this code?

Thanks Allen - in theory it should work but it doesn't. The contents of
the

[quoted text clipped - 42 lines]

Any ideas?

 

[Paul C]

Sorry, but that has the same effect - highlights the data in the field, but
doesn't remove it. The user is not entering keystrokes, just selecting from
the drop down list.

Okay, that's the issue.

The Change event fires with every keystroke.

Try the AfterUpdate event of the combo.

It is in the Combo2_Change event.

[quoted text clipped - 5 lines]

 

[Allen Browne]

Okay, Paul. It's a matter of simplifing everything possible, and tracing
what's going on.

1. Close any other forms or reports that are open.

2. Make sure this form's Timer event is not running.

3. Change Combo2's code to something like this:
Private Sub Combo2_AfterUpdate()
Stop
If Me.Combo2 <> Me.Combo4 Then
Me.Combo4 = Null
Debug.Print TypeName(Me.Combo4)
Debug.Print Me.Combo4.Value
End If
End Sub

4. Check that Access understands the code, by choosing Compile on the Debug
menu.

5. Switch to Form view

6. Enter a value in Combo4.

7. Then enter a different value in Combo2.

When you press Enter, you should see the Code window open, with the Stop
line highlighed. Single-step through the code, by pressing F8 and seeing
which lines are executed. After checking that the 2 most indended lines do
execute, press Ctrl+G to open the Immediate Window. You should see these
lines there:
Combobox
Null

Do not includes any error handling in the routine above while debugging.
Make sure you use the *name* of the combo in the code, even if that is
different from the name of the field the combo is bound to.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry, but that has the same effect - highlights the data in the field,
but
doesn't remove it. The user is not entering keystrokes, just selecting
from
the drop down list.

Okay, that's the issue.

The Change event fires with every keystroke.

Try the AfterUpdate event of the combo.

It is in the Combo2_Change event.

[quoted text clipped - 5 lines]

Any ideas?

 

[Paul C]

Hi Allen,

Thanks for all your time on this.

I have tried your code, and I get a different error message. At the point
where I try to set the value of Combo2=null, I get "Error 3162 - You tried
assign a null value to a variable that isn't a variant data type."

I will be finishing in about 30 minutes, but rest assured will read any
further posts on Monday.

Okay, Paul. It's a matter of simplifing everything possible, and tracing
what's going on.

1. Close any other forms or reports that are open.

2. Make sure this form's Timer event is not running.

3. Change Combo2's code to something like this:
Private Sub Combo2_AfterUpdate()
Stop
If Me.Combo2 <> Me.Combo4 Then
Me.Combo4 = Null
Debug.Print TypeName(Me.Combo4)
Debug.Print Me.Combo4.Value
End If
End Sub

4. Check that Access understands the code, by choosing Compile on the Debug
menu.

5. Switch to Form view

6. Enter a value in Combo4.

7. Then enter a different value in Combo2.

When you press Enter, you should see the Code window open, with the Stop
line highlighed. Single-step through the code, by pressing F8 and seeing
which lines are executed. After checking that the 2 most indended lines do
execute, press Ctrl+G to open the Immediate Window. You should see these
lines there:
Combobox
Null

Do not includes any error handling in the routine above while debugging.
Make sure you use the *name* of the combo in the code, even if that is
different from the name of the field the combo is bound to.

Sorry, but that has the same effect - highlights the data in the field,
but

[quoted text clipped - 13 lines]

 

[Allen Browne]

Ask it what Combo2 is:
Debug.Print TypeName(Combo2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for all your time on this.

I have tried your code, and I get a different error message. At the point
where I try to set the value of Combo2=null, I get "Error 3162 - You tried
assign a null value to a variable that isn't a variant data type."

I will be finishing in about 30 minutes, but rest assured will read any
further posts on Monday.

Okay, Paul. It's a matter of simplifing everything possible, and tracing
what's going on.

1. Close any other forms or reports that are open.

2. Make sure this form's Timer event is not running.

3. Change Combo2's code to something like this:
Private Sub Combo2_AfterUpdate()
Stop
If Me.Combo2 <> Me.Combo4 Then
Me.Combo4 = Null
Debug.Print TypeName(Me.Combo4)
Debug.Print Me.Combo4.Value
End If
End Sub

4. Check that Access understands the code, by choosing Compile on the
Debug
menu.

5. Switch to Form view

6. Enter a value in Combo4.

7. Then enter a different value in Combo2.

When you press Enter, you should see the Code window open, with the Stop
line highlighed. Single-step through the code, by pressing F8 and seeing
which lines are executed. After checking that the 2 most indended lines do
execute, press Ctrl+G to open the Immediate Window. You should see these
lines there:
Combobox
Null

Do not includes any error handling in the routine above while debugging.
Make sure you use the *name* of the combo in the code, even if that is
different from the name of the field the combo is bound to.

Sorry, but that has the same effect - highlights the data in the field,
but

[quoted text clipped - 13 lines]

Any ideas?

 

[Paul C via AccessMonster.com]

Hi again,

Combo2 is a Combobox

Ask it what Combo2 is:
Debug.Print TypeName(Combo2)

Hi Allen,

[quoted text clipped - 51 lines]

 

[Allen Browne]

That makes no sense to me, Paul.

The error message you quoted previously indicates you were trying to assign
a value to a non-variant, which means that VBA interpreted is as a variable.
Yet now the name is recognised as a combo box. I don't follow that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi again,

Combo2 is a Combobox

Ask it what Combo2 is:
Debug.Print TypeName(Combo2)

Hi Allen,

[quoted text clipped - 51 lines]

Any ideas?