Text Box Populated With Wrong Column

[John W. Vinson]

By the way, I am using "MsgBox ssql" for a break -- what command can I use
that will output a string that I can copy and paste?

Set a breakpoint in the code by clicking the mouse in the vertical grey bar to
the left of the code window, next to an executable statement. When you run the
code, execution will stop at the breakpoint. You can use the Debug menu
options or their Fkey shortcuts to step through the code line by line, or run
to the current position of the cursor, skip over function calls etc. - it's
essential for debugging!

You can display the value of ssql in the Immediate Window (type Ctrl-G if it's
not visible) by typing

?ssql

in the window, and can copy and paste it.

 

[newt]

I've never debugged before, and I can't get it to work. But I'm not gonna
bother you with that - obviously I need to learn it. But anyway, here's the
code output that causes the error:

update x_task set project_id = 30, task_subcategory_id = 21,
task_description = 'NYC motion', supervisor_id = 5, lead_id = 6, junior_id =
, staffing_notes = '', task_status_id = 1, hot_potato_id = 6,
task_status_notes = 'in progress', date_edited = date(),
date_status_notes_edited = date() where task_id = 77

The error occurs when the output is "junior_id = ," instead of "junior_id =
Null", which does not happen if I choose something in the junior list box and
then delete it before I run it.

Thanks.

 

[John W. Vinson]

The error occurs when the output is "junior_id = ," instead of "junior_id =
Null", which does not happen if I choose something in the junior list box and
then delete it before I run it.

ah... so it's not null, it's an empty string!

Change

Nz(Me.lstJunior, "Null")

to

IIF(Me.lstJunior & "" = "", "Null", Me.lstJunior)

 

[newt]

Thanks - that seems to do the trick! If you don't mind, can you explain what
the problem was?

Also, when I update a record by clearing out in the form the list box for
HotPotato, the record updates successfully, but then will no longer show up
in the form when it is supposed to be selected. It just disappears (although
the row is still present in the table).

 

[newt]

Sorry, I just realized that the disappearing problem is not when the list box
for
HotPotato is cleared out, but rather when a particular choice is selected
from the Me.lstTaskStatus list box before the record is updated.....

Thanks - and I'm sorry for all of the questions. I am extremely grateful
for your help!!

 

[John W. Vinson]

Thanks - that seems to do the trick! If you don't mind, can you explain what
the problem was?

Also, when I update a record by clearing out in the form the list box for
HotPotato, the record updates successfully, but then will no longer show up
in the form when it is supposed to be selected. It just disappears (although
the row is still present in the table).

When you open a listbox or combo box (unless it has a Default Value), the
value of the control is NULL - undefined, unspecified, unknown.

If you select a value and then backspace over it, you replace "ABC" with "" -
a string value of zero length. It IS defined, it IS specified, it IS known -
it's a string of zero length, therefore it is not NULL and will not be seen as
null using IsNull().

 

[John W. Vinson]

Sorry, I just realized that the disappearing problem is not when the list box
for
HotPotato is cleared out, but rather when a particular choice is selected
from the Me.lstTaskStatus list box before the record is updated.....

Now that's got me baffled. What particular choice?

 

[newt]

I've been trying to fix it - I think it's because a defined query that
populates one of the list boxes is set up incorrectly - hopefully I can fix
it myself - if not I'll let you know.

Thanks again for all of your help - don't know what I would do without you
guys!!

Have a great weekend.