Any way to clear field data when using unbound control to update values

[tagteam]

I have a form that has several unbound fields in it. These fields are used in an update query to update missing information in the database. That all works fine.


The problem is if there is existing data and the user wants it to be blank.

I use and iff statement to say if the field is blank, dont update values. If the field is not blank then update the database with its value.

Based on that is there any way to put something in a field that will clear it. Half of them are date fields and half are not. I tried " " in the date field and that didnt work, however, it does work for the short text fields.

Is there any way to add something in this date field that will essentially blank it out?

[CJ_London]

on the face of it, why not just clear the control so it is null? but it is not at all clear to me what you are asking - suggest provide some example data to illustrate what you are asking.

[tagteam]

Sorry, I cant clear the control because the form is designed to allow the user to just update what they want. I have over 100 fields in the form. The user might update 20 fields but wants the rest of them to stay the same. So when i first created the form i had it set up to clear and then of course you lost all the other data that you were not updating. Very bad.

So that is why I created the if statement. in the form, if you leave it blank, then it basically updates with the existing value, so in effect, no change. If the field is not blank then it updates with the new value.

So when a user is on a text field then can put " " and it will make that field blank. They would do this if they want to remove existing data. However, that doesnt work with dates. So instead of " " i was hoping there was maybe some thing like # # that might clear date fields.

I tried # # and it kind of works. Basically it says " The database didn't update 1 field due to a type conversion failure, blah blah blah" It then clears out the field because it cant read it. But that is confusing to my users so I was hoping there was a cleaner method.

Thanks

[Minty]

Simply delete the date from the date unbound date control or set it to Null - A date control will only accept either Null or a Date.

Then in your IF statement use If IsDate(Me.YourDateControl) then ....

[ssanfu]

<snip> I have over 100 fields in the form. The user might update 20 fields but wants the rest of them to stay the same.

100 controls??? You have a table with 100 fields?? Doesn't sound normalized....

<snip>I tried # # and it kind of works. Basically it says " The database didn't update 1 field due to a type conversion failure, blah blah blah" It then clears out the field because it cant read it.

The error is because hash mark "#" is a date delimiter. So you must have been trying to store an invalid date in a text or number type field.


I made an unbound form with 3 unbound controls: "Date1", "Text#" and "Number4".
I used the double click event to run code to clear the text box controls.

Code is

Option Compare Database
Option Explicit

Private Sub Date1_DblClick(Cancel As Integer)
    ClearCntl ("Date1")
End Sub

Private Sub Number4_DblClick(Cancel As Integer)
    ClearCntl ("Number4")
End Sub

Private Sub Text3_DblClick(Cancel As Integer)
    ClearCntl ("Text3")
End Sub


Public Sub ClearCntl(pCntl As String)
    Me.Controls("" & pCntl & "") = Empty
End Sub

By double clicking in a control the data is cleared.

[tagteam]

I dont have 100 fields in the table, just that form. It is used to update 5 different tables at once. The unbound fields start as blank because they are not tied to the table and are unbound. I think Minty may be onto something if there is a way to set a field to Null so that it is not just blank but you are actually setting a null value. Is there a way to attach setting a null value to a button. That way if the user wanted to delete the data in the table (not just the unbound field because that has no bearing on the table at this point) they could click the button and set that field to null and not just blank.

[June7]

I never allow empty strings in text fields.

Certainly can set field to Null.

Me!fieldname = Null

[ssanfu]

I dont have 100 fields in the table, just that form. It is used to update 5 different tables at once. The unbound fields start as blank because they are not tied to the table and are unbound. I think Minty may be onto something if there is a way to set a field to Null so that it is not just blank but you are actually setting a null value. Is there a way to attach setting a null value to a button. That way if the user wanted to delete the data in the table (not just the unbound field because that has no bearing on the table at this point) they could click the button and set that field to null and not just blank.

Very confusing!! I think it is because of your terminology.

Just so we are on the same page:

Tables have FIELDS. There cannot be an "unbound field" in a table. A field in part of the table (an object in the table).
Forms have CONTROLS. A form can be bound (to a table/query) or unbound. If a form is bound, then the controls ca be bound or unbound.

It sounds like you have an unbound form with 100 unbound controls on it.
How are you updating the 5 table? 5 queries? VBA code?


If you set an unbound control on the form to Empty/NULL, should the field in the table then be set to Empty/NULL?

[tagteam]

Yeah, ok. they are unbound controls on a form. I am using a query. Yes, if the unbound control on the form is set to null then i want it to update the value to empty, whereas now, just leaving the unbound control blank will not change the value.
I will test what June7 says today and try to use a button with vba behind it to set a particular date unbound control to null and see if that works.
Thanks,
Tate