SQL UPDATE in Subform responding with Enter Parameter Value 1

[Rooski]

I am using Microsoft Access 2000.

The code below resides in a subform. Using the DMax command, strRevisionDate is loaded with the largest TDateRevised value that can be found in tblTDateRevision for a given Project Number. The SQL statement then updates the field SupervisorTDateRevised (found in tblProjectMain) using the value in strRevisionDate. The project number comes from the mainform [Forms]![frmAddProjTDateRevMain]![ProjectNumber].

The problem with the code occurs after the user hits a command button to save data entered on the screen. An “Enter Parameter Value” dialog box pops up asking for a value for strRevisionDate associated with the SET part of the SQL statement. If the user supplies the date, the code executes and makes the proper update.

I have displayed the component fields involved in the code to make sure that each had its proper value and found everything to be OK. I have also defined strRevisedDate as a Date field but encountered the same results.

The only time I don't get the Enter Parameter Value box is when I remove strRevisedDate from the SET and hard-code a date (like '05/20/2006') in its place. Then everything works fine.

Why is the dialog box appearing? And how can I make it not appear but still have the code execute successfully?

Private Sub Form_AfterUpdate()

Dim strRevisionDate As String
Dim strSQL As String

strRevisionDate = ""
strSQL = ""

strRevisionDate = DMax("[TDateRevised]", "tblTDateRevision", "[ProjectNumber] =" & "[Forms]![frmAddProjTDateRevMain]![ProjectNumber]")

strSQL = "UPDATE tblProjectMain " & "SET [SupervisorTDateRevised] = strRevisionDate " & "WHERE [ProjectNumber] = [Forms]![frmAddProjTDateRevMain]![ProjectNumber];"

DoCmd.RunSQL strSQL

End Sub

Much thanks in advance,

Rooski

 

[HandsOnAccess]

I assume Field data type of TDateRevised is Date
While using the code below, care should be taken to check if updating is in proper order, i.e., the format of strRevisionDate may get updated as mm/dd/yyyy or dd/mm/yyyy.
It is important to check how it is updating by trying for dates like 01/15/2006 and 07/01/2006.
You can use dateformat function to set the updation according to your requirement.

----------------
Private Sub Form_AfterUpdate()

Dim strRevisionDate As Date
Dim strSQL As String

strSQL = ""

If Not IsNull(DMax("[TDateRevised]", "tblTDateRevision", "[ProjectNumber] = [Forms]![frmAddProjTDateRevMain]![ProjectNumber]")) = True Then
strRevisionDate = DMax("[TDateRevised]", "tblTDateRevision", "[ProjectNumber] = [Forms]![frmAddProjTDateRevMain]![ProjectNumber]")

strSQL = "UPDATE tblProjectMain " & _
"SET [SupervisorTDateRevised] = #" & strRevisionDate & "# " & _
"WHERE [ProjectNumber] = [Forms]![frmAddProjTDateRevMain]![ProjectNumber];"

DoCmd.RunSQL strSQL
Else
msgbox "Max Date Not Found.",vbcritical,"User Alert"
Exit Sub
Endif
End Sub
-------------

 

[Rooski]

HOA,

Thanks for responding to my request for help. I just returned to the office this morning after the long holiday weekend.

Your assumption about the data type of TDateRevised is correct. (Actually, the data type is date/time.)

I used you coding suggestions and that made all the difference! The only thing I don't understand is the use of the # symbols. What is their function?

Much thanks,

Rooski

 

[HandsOnAccess]

Just like how we use a quote ("'") While using a string, We use "#" Symbol to check date value.

Remember
It is important to check how it is updating by trying for dates like 01/15/2006 and 07/01/2006.

Regards,