How do I blank a date field ina bound form?

[Pampas58]

The Question:

How do I blank a date field in a bound form? I’m not good enough yet to create and work with unbound forms and get anything done.

The Why of the Question:

I’ve created an order, repair, and ship database where the repairs are matched to the initial repair order. The part that’s a bit different about this project is that, the repaired parts go into a pool of repaired units and may go back to any one of 4,000 different clients.

The Code I wrote:

Input of “Defective Received Date” seems like a very simple thing to do. First I gave it a DEFAULT value, but it sometimes did not work. I decided it was just as easy to go into the EVENT handler for the FORM itself and put in the following code:

If IsNull([Defective Received Date]) then [Defective Received Date] = Date()

This works great, but not really. The escape function will get rid of the date, if I escape out of the form; that’s the great part. However, I found some users who will go in, put a date in, not put in the proper unit serial number and now when the script runs to check the validity of the serial number (Do I have that Serial Number in the system or not), it leaves the date in there [I want to delete the date from the field since it should not be saved without the serial number being in].

I’ve been trying to find anything that will tell me how to do this, and I cannot find it.

Thanks in advance for your help.

 

[CaptainD]

If the table field that holds the date will allow Nulls then update it to Null

UPDATE YourTable SET YourDateField = NULL WHERE iID = TheIDNumberOfTheRecord

 

[Pampas58]

CaptainD,

Thanks, but I get the error "Compile Error, Expect end of statement"

When I put in the following code:


Dim vResponse As Variant
Dim iID As Integer

iID = [ID] ' this is the current record number

'Warn the user that there is a Date stored without a serial number
vResponse = MsgBox("Date without Serial Number - Do you want to Exit?", vbYesNo, "WARNING")
If vRepsonse = vbYes Then

UPDATE tblOrderStatus SET [defective received date] = NULL WHERE "[ID] =" iID
End If

DoCmd.Close

 

[Pampers]

Maybe (typed not tested):

dim strSql as string

sql = "tblOrderStatus  SET  =  [defective received date] = null  " & _
"WHERE ID = " & Me.ID


DoCmd.RunSQL (strSQL)

Pampers
Keeping it simple can be complicated

 

[ck1999]

Have you tried something like

If Me.Dirty = True Then
'Undo changes:
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
end if

or if the record is already saved run
a delete query

DELETE *
FROM Table1
where [serial] = serialtest;

serialtest is the serial number variable for when you tested and it did not comply

how one of these helps

 

[PHV]

DoCmd.RunSQL "UPDATE tblOrderStatus SET [defective received date] = NULL WHERE [ID]=" & iID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pampers]

Oeps,

sql = "UPDATE tblOrderStatus SET = [defective received date] = null " & _
"WHERE ID = " & Me.ID


DoCmd.RunSQL (strSQL)

Pampers
Keeping it simple can be complicated

 

[PHV]

Still SQL syntax error for Pampers ;-)

 

[Pampas58]

Pampers,

Thanks but after trying various different approaches with the statment, it appears that I cannot set the field back to Null that way... but I'm going to keep trying.

PHV,
Thanks too, but it too did not work.

ck1999,
Thanks for the offer, I did find something that sort of worked with the UnDo command from the DoCmd function, but still not quite right.

What I've ended up doing is to have a set of warnings and control the exit from the form so that if they made a mistake, they are forced to manually erase the mistake and then the form will allow them to exit.

I also added a "SysFormName" field where I capture the name of the form that last edited the record, plus a "SysMainDate" to the record so that at least I can see what happened to that one record.

Not an elegant solution, but at least it is one that will work till I come up with something better.

Thanks everyone for trying to help me cure some of my own ignorance...

Pamapas58

 

[Pampers]

Oeps2 (better to test it then to type I guess). Tnx PHV

sql = "UPDATE tblOrderStatus  SET  [defective received date] = null  " & _
"WHERE ID = " & Me.ID

Pampers
Keeping it simple can be complicated