Data Type Mismatch

[mattc66 via AccessMonster.com]

In the below code I am getting an error message Error #3464 Data Type
Mismatch. It stops on the last line of code "CurrentDb.Execute strSQL,
dbFailOnError".

The txtNewEstShipDate field is am unbound text box on my main form. The
est_ship field is a Data/Time type. I changed it to a text type to see if
that would resolve it. It made no difference and I still got the error
message. Can anyone give me any assistance with this issue?

Thanks
Matt

Private Sub cmdUpdEstShipDate_Click()
Dim strSQL As String

strSQL = "Update tblIR_ChinaSales Set tblIR_ChinaSales.EST_SHIP = #" _
& Me.txtNewEstShipDate & "# Where tblIR_ChinaSales.Order_Num=" _
& Me.ORDER_NUM

If Weekday(Me.txtNewEstShipDate) = 1 Or Weekday(Me.txtNewEstShipDate) = 7
Then
MsgBox "Weekend dates are not allowed", vbInformation, "Invalid Date"
Me.txtNewEstShipDate.Undo
Exit Sub
ElseIf Me.txtNewEstShipDate < Date Then
MsgBox "Dates in the past are not allowed", vbInformation, "Invalid
Date"
Me.txtNewEstShipDate.Undo
Exit Sub
Else
CurrentDb.Execute strSQL, dbFailOnError

End If

End Sub

 

[ruralguy via AccessMonster.com]

Is the tblIR_ChinaSales.Order_Num field a numeric field? If not then you
would need single quotes.

Where tblIR_ChinaSales.Order_Num= ' " & Me.ORDER_NUM & " ' "

(spaces added for visibility)

 

[mattc66 via AccessMonster.com]

Thank you - that was it.. One more question - what would I need to do to the
code to requery the subform query?

Is the tblIR_ChinaSales.Order_Num field a numeric field? If not then you
would need single quotes.

Where tblIR_ChinaSales.Order_Num= ' " & Me.ORDER_NUM & " ' "

(spaces added for visibility)

In the below code I am getting an error message Error #3464 Data Type
Mismatch. It stops on the last line of code "CurrentDb.Execute strSQL,

[quoted text clipped - 31 lines]

End Sub

 

[mattc66 via AccessMonster.com]

Found it: Forms![MainFormName]![fsubProjectList].Form.Requery

Thank you - that was it.. One more question - what would I need to do to the
code to requery the subform query?

Is the tblIR_ChinaSales.Order_Num field a numeric field? If not then you
would need single quotes.

[quoted text clipped - 8 lines]

 

[ruralguy via AccessMonster.com]

Assuming you are on the MainForm then you can also use:

Me.fsubProjectList.Form.Requery

Found it: Forms![MainFormName]![fsubProjectList].Form.Requery

Thank you - that was it.. One more question - what would I need to do to the
code to requery the subform query?

[quoted text clipped - 4 lines]