Button to reset field value to ""

[JimO]

Looking to have a button on a continuous form that will reset the value of a field [Progress] to a blank value.

Private Sub Command20_Click()
    Me.Progress.Value = ""
End Sub

I tried this code but I get nothing.

Jim O

[vicsaccess]

I think more information is needed. Is the button part of the continuous record, how ever many records are displayed there is a button for each? If so then the answer would be identical to my reply to your other post. Update SQL with reference to the line ID setting progress="".

[JimO]

Thanks for the response,

It is a continuous form and I want to reset all records in the field [Progress] to "". There is only a total of 30 rows and I use this field to keep track of my progress. It is a temporary repeatable process, so when a cycle is complete I can reset the form as a whole rather than delete all 30 individually.

Jim O

[vicsaccess]

ok, if I remember correctly leave out the where condition and just try an update SQL. Something like

currentdb.execute("update tablename SET progress=null")

[JimO]

Here is my latest try. When I click the button nothing happens.

Private Sub Reset_Click()
    CurrentDb.Execute ("update SportsMLBFrm SET progress=null")
End Sub

Jim O

[davegri]

Is SportsMLBFrm a form? The update query can only updata a table.

CurrentDB.Execute "Update MyTable Set Progress = " & null

If you want a zero length string, use vbNullString instead of null in the query.

[JimO]

It is a form. I am tracking baseball results and have a form of teams with wins and losses. As a game is complete I enter the results and mark the game as complete (the [Progress] field). The next day I use the same form (table) to record the days results, I am just looking for a way to clear the [Progress] field with a button to clear all records in the progress column rather than go through and delete each one separately.
Here is what I have so far that does not work.

Private Sub Reset_Click()
    CurrentDb.Execute "Update SportsMLBFrm Set Progress = " & Null
End Sub

Jim O

[vicsaccess]

What's the name of the table where progress is located? Put that table in the update not the form. Then after the update add a requery, me.sportsmlbfrm.requery, so that it updates your form.

[JimO]

Entered this code to the field [WinLoss] on the form I am working with and not getting any results.

Private Sub Win__AfterUpdate()
    CurrentDb.Execute ("update MLBTbl set Date" & Date & "where MLBTblID=" & Me.ID.Value)
    Me.sportsmlbfrm.Requery
End Sub

Jim O

[davegri]

 CurrentDb.Execute ("update MLBTbl set Date" & Date & "where MLBTblID=" & Me.ID.Value)

There's a couple of problems here. The field Date in your MLBtbl is using an Access reserved word, Date. In your table, change the fieldname to something else, like WLDate.
Second, your sql produces incorrect syntax. It's missing an equal sign (=) and it needs a couple of spaces to avoid jamming things together in a way that the SQL interpreter cannot process.

CurrentDb.Execute ("update MLBTbl set Date = " & Date & " where MLBTblID= " & Me.ID.Value)