vba click button to switch field value and set field value?

[siamandm]

Hi,

if i have a table with two date fields inside, assessed date and received date also a field for status ( Assessed and Received), in my continuous form the assessed field is set to the current date as default value, also the status field default value = assessed
i want to have a button when i press it fills the received date which comes from a text box field and switches the status field from assessed to received !

how to achieve this or if there is a better way please suggest

many thanks in advance.

Regards

[HansV]

The code shouldn't be difficult. What is the problem you have to write it?

[siamandm]

what i need to do is to change the status and input date at the same time for those field in the range of the date assessed
in the attached example
when put the data range and press change status button i want to changes affects only those fields in the range i specified

Database44.zip

regards

[HansV]

You want to set the ReceivedDate and Status for all records whose AssessedDate is within the specified range. But what should ReceivedDate be set to? To the current date (today)? Or to the value of AssessedDate? Or ...?

[siamandm]

we can have a text box with a default date today(), that will give us the option to change the date if we have to .

Regards

[HansV]

Let's say that you name the text box txtNewDate. The code for the command button could be

Code: Select all

Private Sub cmdChaneStatus_Click()
    Dim strSQL As String
    strSQL = "UPDATE tblAssess SET ReceivedDate=#" & Format(Me.txtNewDate, "yyyy/mm/dd") & "#, Status=" & Me.cboStatus & _
        " WHERE StudentID=" & Me.StudentID & " AND AssessedDate Between #" & _
        Format(Me.txtDateFrom, "yyyy/mm/dd") & "# And #" & Format(Me.Text11, "yyyy/mm/dd") & "#"
    CurrentDb.Execute strSQL, dbFailOnError
    Me.qryAssess.Requery
End Sub