Validate Data

[mattc66 via AccessMonster.com]

I am trying to create some code that will confirm that the user entered a
valid date. The date must be today or in the future. Below is the code I
thought would work. It doesn't... Can anyone give me some help with the
proper syntax.

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate

Else: MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"

End If

Thanks
Matt

 

[Beetle]

Not sure what you mean by "it doesn't work" but:

You appear to mixing Single Line If and Block If syntax. Plus, you don't say
what event this code is in, but it should be in the Before Update event of the
control and/or the form so you can use the Cancel argument, i.e.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"
Cancel = true
Me.txtNewEstShipDate.SetFocus
End If

End Sub

 

[mattc66 via AccessMonster.com]

Its on a click event. If the user puts in todays date it thinks it's invalid.
Also would there be a way to validate that its a work day that is entered?

Not sure what you mean by "it doesn't work" but:

You appear to mixing Single Line If and Block If syntax. Plus, you don't say
what event this code is in, but it should be in the Before Update event of the
control and/or the form so you can use the Cancel argument, i.e.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"
Cancel = true
Me.txtNewEstShipDate.SetFocus
End If

End Sub

I am trying to create some code that will confirm that the user entered a
valid date. The date must be today or in the future. Below is the code I

[quoted text clipped - 10 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

More important issue:
I am updating the estimated ship date on each line item on the subform detail.
I am doing this from the unbound text box called "txtNewEstShipDate" using a
click event on a button on the main form. The below code is working now
thanks to Beetle. However if there is more then one record in the subform I
need to have it update each of the records "EST_SHIP" with the same data from
the txtNewEstShipDate". Could someone help with this issue? - Thanks

Private Sub cmdUpdEstShipDate_Click()

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid date."), vbCritical, "Invalid Date"
Cancel = True
Me.txtNewEstShipDate.SetFocus
End If

End Sub

Not sure what you mean by "it doesn't work" but:

You appear to mixing Single Line If and Block If syntax. Plus, you don't say
what event this code is in, but it should be in the Before Update event of the
control and/or the form so you can use the Cancel argument, i.e.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"
Cancel = true
Me.txtNewEstShipDate.SetFocus
End If

End Sub

I am trying to create some code that will confirm that the user entered a
valid date. The date must be today or in the future. Below is the code I

[quoted text clipped - 10 lines]

Thanks
Matt

 

[Beetle]

A click event of what? A command button? Is the user entering the date
in a text box and then clicking a command button?

Anyway, as to the rest of your question, the reason it thinks todays date is
invalid is because you are using Now() in your statement. Use Date() instead.
As far as workdays, you could use something like;

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
Else If Me.txtNewEstShipDate < Date() Then
MsgBox "Dates in the past are not allowed", vbInformation, "Invalid Date"
Me.txtNewEstShipDate.Undo
Else
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
End If

However, the above will not account for Holidays. If you need to do that it's
a little more complicated because you have to create a table to store which
dates are Holidays, then verify the date against that table, etc., etc.

--
_________

Sean Bailey

Its on a click event. If the user puts in todays date it thinks it's invalid.
Also would there be a way to validate that its a work day that is entered?

Not sure what you mean by "it doesn't work" but:

You appear to mixing Single Line If and Block If syntax. Plus, you don't say
what event this code is in, but it should be in the Before Update event of the
control and/or the form so you can use the Cancel argument, i.e.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"
Cancel = true
Me.txtNewEstShipDate.SetFocus
End If

End Sub

I am trying to create some code that will confirm that the user entered a
valid date. The date must be today or in the future. Below is the code I

[quoted text clipped - 10 lines]

Thanks
Matt

 

[Beetle]

OK, that's a bit different so nevermind my response to your last question
(although some of it will still apply).

First, just as a point of information, only certain events have a built in
Cancel
argument. The Click event is not one of them, so having Cancel = True
in this event does nothing. The example I posted in my first response was
based
on the Before Update event, which does have a built in Cancel argument.

Moving on, what you'll need to do is use an Update query. You'll need to use
criteria to determine which records should be updated. Let's assume that the
link between your main form and subform is ShipID, and that the table the
subform is based on is called tblShipRecords, then the code might look like;

Dim strSQL As String

strSQL = "Update tblShipRecords Set tblShipRecords.ShipDate = #" _
& Me.txtNewEstShipDate & "# Where tblShipRecords.ShipID=" _
& Me.ShipID

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
Else If 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

--
_________

Sean Bailey

More important issue:
I am updating the estimated ship date on each line item on the subform detail.
I am doing this from the unbound text box called "txtNewEstShipDate" using a
click event on a button on the main form. The below code is working now
thanks to Beetle. However if there is more then one record in the subform I
need to have it update each of the records "EST_SHIP" with the same data from
the txtNewEstShipDate". Could someone help with this issue? - Thanks

Private Sub cmdUpdEstShipDate_Click()

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid date."), vbCritical, "Invalid Date"
Cancel = True
Me.txtNewEstShipDate.SetFocus
End If

End Sub

Not sure what you mean by "it doesn't work" but:

You appear to mixing Single Line If and Block If syntax. Plus, you don't say
what event this code is in, but it should be in the Before Update event of the
control and/or the form so you can use the Cancel argument, i.e.;

Private Sub Form_BeforeUpdate(Cancel As Integer)

If txtNewEstShipDate >= Now() Then
Me.[sfrmPKOrderDetail].Form![EST_SHIP] = Me.txtNewEstShipDate
Else
MsgBox ("You entered an invalid Date"), vbCritical, "Invalid Date"
Cancel = true
Me.txtNewEstShipDate.SetFocus
End If

End Sub

I am trying to create some code that will confirm that the user entered a
valid date. The date must be today or in the future. Below is the code I

[quoted text clipped - 10 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Okay - thanks for the response. When I run this I get an error - Method or
data member not found. on the >> Me.txtNewEstShipDate. The Me.
txtNewEstShipDate is a unbound text box I have on the main form. Its an entry
box for the user to enter the new estimated shipdate for the order.

OK, that's a bit different so nevermind my response to your last question
(although some of it will still apply).

First, just as a point of information, only certain events have a built in
Cancel
argument. The Click event is not one of them, so having Cancel = True
in this event does nothing. The example I posted in my first response was
based
on the Before Update event, which does have a built in Cancel argument.

Moving on, what you'll need to do is use an Update query. You'll need to use
criteria to determine which records should be updated. Let's assume that the
link between your main form and subform is ShipID, and that the table the
subform is based on is called tblShipRecords, then the code might look like;

Dim strSQL As String

strSQL = "Update tblShipRecords Set tblShipRecords.ShipDate = #" _
& Me.txtNewEstShipDate & "# Where tblShipRecords.ShipID=" _
& Me.ShipID

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
Else If 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

More important issue:
I am updating the estimated ship date on each line item on the subform detail.

[quoted text clipped - 39 lines]

 

[mattc66 via AccessMonster.com]

I think I found my error. Thanks

Okay - thanks for the response. When I run this I get an error - Method or
data member not found. on the >> Me.txtNewEstShipDate. The Me.
txtNewEstShipDate is a unbound text box I have on the main form. Its an entry
box for the user to enter the new estimated shipdate for the order.

OK, that's a bit different so nevermind my response to your last question
(although some of it will still apply).

[quoted text clipped - 34 lines]

 

[mattc66 via AccessMonster.com]

Ok - I think its very close. I am getting an error.
Data Type mismatch > CurrentDb.Execute strSQL, dbFailOnError

EST_SHIP was a Date/Time so I changed to to a Text type and that didn't seem
to resolve. Any ideas of what else I can check?

I think I found my error. Thanks

Okay - thanks for the response. When I run this I get an error - Method or
data member not found. on the >> Me.txtNewEstShipDate. The Me.

[quoted text clipped - 6 lines]

 

[Beetle]

It's been a couple of days, so you may have solved your issue by now
but if not post your current code and we can try to work out the error.
--
_________

Sean Bailey

Ok - I think its very close. I am getting an error.
Data Type mismatch > CurrentDb.Execute strSQL, dbFailOnError

EST_SHIP was a Date/Time so I changed to to a Text type and that didn't seem
to resolve. Any ideas of what else I can check?

I think I found my error. Thanks

Okay - thanks for the response. When I run this I get an error - Method or
data member not found. on the >> Me.txtNewEstShipDate. The Me.

[quoted text clipped - 6 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

It turned out that I needed single quotes because my Order_Num is a text fld.
Problem resolved..

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

Thanks

It's been a couple of days, so you may have solved your issue by now
but if not post your current code and we can try to work out the error.

Ok - I think its very close. I am getting an error.
Data Type mismatch > CurrentDb.Execute strSQL, dbFailOnError

[quoted text clipped - 9 lines]