Multiple If...Else If Form current event issue

A

Access Greenhorn

I have multiples If...Else If statements in the afterupdate event of a combo
box that enable or disable text objects. This same code is in the form's On
Current event so the same code executes while the user is scrolling thru the
records. The problem is that when the user gets to a new record the debugger
is activated b/c there is no entry yet in the combo box and so according to
the On Current event code there is a Null entry.

One way to fix this would be to use the NZ function (in the Form's On
Current Event Code), but my concern is that this would essentially be
creating an entry and the user would get the 'cannot save this record b/c
there is no primary key....' error message when they close the
form...annoying.

Am I wrong/right? Any solutions?

Thanks,
AG
 
A

Allen Browne

You can enable/disable controls without changing their value.

Unless you assign a value to a bound control, you have not dirtied the
record, and so you will not trigger the problem with the new record.

If you are receiving error 94 at the new record, you are probably trying to
assign values to variables other than Variant. The Variant can be Null.
Others (such as Long, String, Boolean) cannot. Either Dim them as Variants,
or use Nz() to convert the null to a suitable value. Assigning a value to a
variable (as distinct from a Control) will not dirty the record.

IMHO, any code that dirties the record in Form_Current is bad code.

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

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

message
news:[email protected]...
 
R

Rick Brandt

Access Greenhorn said:
I have multiples If...Else If statements in the afterupdate event of a combo
box that enable or disable text objects. This same code is in the form's On
Current event so the same code executes while the user is scrolling thru the
records. The problem is that when the user gets to a new record the debugger
is activated b/c there is no entry yet in the combo box and so according to
the On Current event code there is a Null entry.

One way to fix this would be to use the NZ function (in the Form's On
Current Event Code), but my concern is that this would essentially be
creating an entry and the user would get the 'cannot save this record b/c
there is no primary key....' error message when they close the
form...annoying.

Am I wrong/right? Any solutions?
Click to expand...

You're wrong :)

Using Nz in your code would have no affect on the value of the ComboBox. In the
following code snippet...

If Nz(ComboBoxName, "") = "SomeString" Then...

....the Nz() is only replacing Null with "" *in the code*. The actual value of
the ComboBox is not affected so no new record would be initiated.
 
A

Access Greenhorn

Thanks Rick and Allen,

Allen, I think I got most of what you were saying and I believe my solution
is "kosher", i.e. not dirty. I looked at Rick's bit of code and did the
following:

strItem = Nz(Me.cboItem.Value, "")
strTest = Nz(Me.cboTest.Value, "")

If strItem = "" And strTest = "" Then

It works like a charm now, thanks guys,
AG
 
Top