Unbound Text Box and Append Query Issues

[dmeiser]

I have a form with an unbound text box control. At the time of form
close I run an Append Query that appends the value of some other
controls and the value of that text box into a table. The append query
only appends data if the text box is not empty.

Excellent. Except, when I check the table to look at the data, there's
no comments. There's records but not comments. I've checked
everything out and narrowed it down to this unbound text box. I've
spent over an hour googling and found many people with this issue, but
no resolutions except to change it to a bound text box. The problem
with that is that I would have to create a record first and then delete
if there is nothing in the text box, which would only contribute to
database bloat.

I've discovered that, if I move into another control that the value
does get inserted to the table. I figured that I could use this to my
advantage and just use code to change the focus to a different control.
This didn't work either. So, basically, I think my options are to
either bloat the DB by creating a record upon form open and binding to
the text box - only to delete if the record is blank - or, worse yet,
create a check box that users have to check if they want to leave a
comment.

Has anyone out there experienced this problem? How did you solve it?

 

[dmeiser]

Found my own answer! It took a little bit more troubleshooting, but I
finally got it. The problem was actually caused by failed typecasting
in a known issue:

http://support.microsoft.com/default.aspx?scid=kb;en-us;125259

It's an old issue, apparently. It also proved a bit more difficult for
me, since even explicit type-casting failed using the CStr() function.
Here's how to fix it:

Go to Query -> Parameters as a parameter enter
[Forms]![yourForm]![yourControl] and then the data type. And no,
entering without bracketing doesn't work. Now, enter
Forms!yourForm!yourControl, with or without brackets, as a column.