Updating Record Through a Form

[adidab14]

Hello there,
I am trying to update a record through a form based on an unbound text box and a null value but I'm having a little trouble getting the code to work. I'm hoping that someone here can help me.
Basically I'm looking to add a timestamp to a specific field (TimeOut) if the record contains the value in the text box AND a null value in the TimeOut field.

So my variables here are:
Table where all of this is found = Test
Field that needs to match the text box = MoldNumber
Text Box = txtMold
Field that I need updated if it has a null value = TimeOut.

Here's the code I'm trying but failing with:

CurrentDb.Execute "UPDATE Test SET TimeOut = #" & Now() & "# WHERE MoldNumber= '" & Me!TxtMold & "'", IsNull(TimeOut), dbFailOnError

I'm not terribly well versed in VBA but I just can't seem to figure this out. Any help would be greatly appreciated!

 

[theDBguy]

Hi. You were almost there. I would try it this way:

Dim strSQL As String


strSQL = "UPDATE Test SET TimeOut=Now() WHERE TimeOut Is Null AND MoldNumber='" & Me.txtMold & "'"


Debug.Print strSQL


CurrentDb.Execute strSQL, dbFailOnError

Hope it helps...

 

[adidab14]

That worked like a charm. You are amazing!

 

[theDBguy]

That worked like a charm. You are amazing!

Hi. Glad to hear it worked for you. Good luck with your project.

 

[IbrBadri]

That worked like a charm. You are amazing!

I think you missed to tagg "SOLVED"

 

[Pat Hartman]

I'm not terribly well versed in VBA but I just can't seem to figure this out. Any help would be greatly appreciated!

If you don't know VBA, then you should probably not be working with unbound forms since you must replicate everything that Access is doing behind the scenes for you for free in order to make an unbound form actually work. Why are you using an unbound form rather than a bound form? Bound forms are the best feature of Access. Do not discard them lightly.