How to suggest entering a Monday Date

[doyle60]

I have a field in a subform that should be a certain day of the week
based on a field in the header.

Some factories always ship on a Monday and some on a Thursday, for
example. Each factory has a field called BoatETDDay and a number 1
through 7 is entered in that field.

So when the user types a date that does not match I want a box to come
up and suggest the next date that would match the BoatETDDay. But the
message should allow the date entered to stand.

What would be the code for such a thing? The first line would be
something like this:

If Format(Delivery,"w") <> BoatETDDay then
'Message
End if

Thanks,

Matt

 

[OssieMac]

Lookup Weekday function in help.

Example: Say BoatETDDay 1 through 7 represents Monday to Sunday.

If Weekday(YrDate,2) <> BoatETDDay then

Code would go in After_Update event of field.

Get back if you still can't follow. However, let me know what day of the
week does 1 represent in "BoatETDDay and a number 1 through 7 is entered in
that field"

 

[Graham Mandeno]

Hi Matt

The Weekday function returns the same result as Format("w") and is rather
more intuitive.

To generate a suggested date, subtract the Weekday of the entered date and
add BoatETDDay, then add another 7 if BoatETDDay is less than Weekday. This
"air code" should give you a start:

Private Function ShipDate_AfterUpdate()
Dim SuggestedDate as Date, EnteredDay as Integer, msg as String
EnteredDay = Weekday(ShipDate)
If EnteredDay <> BoatETDDay then
SuggestedDate = ShipDate - EnteredDay + BoatETDDay _
+ IIf(BoatETDDay < EnteredDay, 7, 0)
msg = ShipDate & " is not a " & Format(SuggestedDate, "dddd") _
& vbCrLf & vbCrLf & "Do you wish to change the ship date to " _
& SuggestedDate & "?"
If MsgBox(msg, vbQuestion Or VbYesNo) = vbYes Then
ShipDate = SuggestedDate
End If
End If
End Sub

 

[Klatuu]

After Update would be too late. You need to be able to change it before it
is updated. Use the control's Before Update event:

Private Sub txtDelivery_BeforeUpdate(Cancel As Integer)
dtmSuggestedShipDate

With Me
If Weekday(.txtDelivery) <> .BoatETDDay Then
dtmSuggestedShipDate = DateAdd("d", .BoatETDDay, .txtDelivery)
If MsgBox("Suggested Ship Date Is " & dtmSuggestedShipDate & _
vbNewLine & "Use Suggested Date", vbQuestion + vbYesNo, _
"Delivery Date on Wrong Week Day") = vbYes Then
.txtDelivery = dtmSuggestedShipDate
End If
End If
End With

 

[doyle60]

Thanks Klatuu, but Graham's code does work in the after update event.
Thanks Graham, I didn't really have to change much, just the actual
field name for the date being entered and I reworded some of your
message.

How would you suggest warning a user who does the reverse? That is,
they enter the dates in the sub and then they change the header's
Factory afterwards.

I have an idea but would like to know if something is a bit easier
than my method.

Matt

 

[Graham Mandeno]

Hi Dave

Last time I looked, it was illegal to change the value of a control in its
own BeforeUpdate event. Has this changed?

 

[Graham Mandeno]

Hi Matt

You should be able to use exactly the same code from the AfterUpdate event
of the factory control. (Presumably this is a combo bow which automatically
causes BoatETDDay to change.

Instead of duplicating the code, change the name of the function:
Private Sub ShipDate_AfterUpdate()
to
Private Function AdjustShipDate()

Then you can call the same function from the property sheet of both
controls:
After Update: =AdjustShipDate()

 

[doyle60]

I can't use the same code exactly. Let me explain first where
important fields are:

FactoryID: On the main form and main query of that form:
OrderHeaderqry.
BoatETDDay: This field is not on the form but in the FactoryID table.
It holds a number 1 through 7 for the day of the week the factory
ships.
Delivery: This is a field in the subform of the form and it the field
we wish to change. It has a one to many relationship with the main
form.

Your code above was easy to install because adding the BoatETDDay to
the subform's query with the Delivery did not stop it from being
updateable.

But doing the reverse (having code run after changing the FactoryID)
is more troublesome. The Delivery dates are one to many and the
BoatETDDay is not, and never can be, part of the main query of the
form, OrderHeaderqry.

So things have to be done quite differently.

I'm thinking I have the skills and experience to pull this off. But I
am wondering how you would attack it before I begin.

Thanks,

Matt

 

[Graham Mandeno]

Hi Matt

When you change the FactoryID in the main form, do you want to change (or
offer to change) ALL the Delivery dates in the subform? If so, do you want
to confirm each one singly?

 

[doyle60]

I want the code to take the lowest date of the many and offer to
change all to the week day higher that matches.

Almost all the records in the main form have the same Delivery dates
in the subform anyway. (This Delivery is a sub form item but very
well could have been a Header item.)

Thanks for asking,

Matt

 

[Graham Mandeno]

Hi Matt

I suggest you create the following function in a standard module (not your
form's class module):

Public Function NextWeekday( _
InDate As Date, _
DayOfWeek As VbDayOfWeek _
) As Date
Dim InDay As VbDayOfWeek
InDay = Weekday(InDate)
If InDay = DayOfWeek Then
NextWeekday = InDate
Else
NextWeekday = InDate - InDay + DayOfWeek _
+ IIf(DayOfWeek < InDay, 7, 0)
End If
End Function

Now you can either execute a query to update the selected delivery dates:

Dim sql as string
sql = "Update YourTable set Delivery = NextWeekday(Delivery, " _
& BoatETDDay & ") where <insert selection criteria here>"
CurrentDb.Execute sql

Or you can loop through the records in your subform's recordsource:

With Me![SubformControlName].Form.RecordsetClone
.MoveFirst
Do Until .EOF
.Edit
!Delivery = NextWeekday( !Delivery, BoatETDDay )
.Update
.MoveNext
Loop
End With

 

[doyle60]

Thanks. I get a compile error on the below code saying "Automation
type not supported in visual basic" and it masks out everything from
"Public..." to "...) As Date".

Public Function NextWeekday( _
� � InDate As Date, _
� � DayOfWeek As VbDayOfWeek _
� ) As Date
� Dim InDay As VbDayOfWeek
� InDay = Weekday(InDate)
� If InDay = DayOfWeek Then
� � NextWeekday = InDate
� Else
� � NextWeekday = InDate - InDay + DayOfWeek _
� � � � + IIf(DayOfWeek < InDay, 7, 0)
� End If
End Function

The field that links the subform to the main form is called PO. The
name of the form is ENTRYFORM2. So would I write "where PO = Forms!
ENTRYFORM2!PO" or "ENTRYFORM2.Form!PO" in the code below where you
have to insert selection criteria?

Dim sql as string
sql = "Update YourTable set Delivery = NextWeekday(Delivery, " _
� � & BoatETDDay & ") where <insert selection criteria here>"
CurrentDb.Execute sql

But the code changes the dates after the user changes the factory
without asking the user first. Can we first test to see if the dates
are bad and then if bad, have a box that comes up that asks if the
dates should be changed? The box does not have to have the actual
date in it like before, just a message like "Do you want to change the
Delivery dates to the actual week day this factory ships on?"

Thanks. I feel as if I'm taking the mile here.

Matt

 

[doyle60]

Graham,
I believe I solved the last issue above. I created an invisible
subform and some invisible controls and a query that analyzes the
dates and if there are dates that do not match after you change the
factory, the code will give a message stating so. It returns a yes no
msg box as your other message but right now if you hit yes, it does
some silly thing just for testing. I have to stick your code in
there.

So all I need is a reply to the compile error and the correct way to
filter the action to change the dates.

Thanks,

Matt

 

[Graham Mandeno]

Hi Matt

The nice thing about a mile is that it's a finite distance

I'm puzzled about that error, and I wonder if it has something to do with
the boxes in the code below.

Public Function NextWeekday( _
? ? InDate As Date, _
? ? DayOfWeek As VbDayOfWeek _
? ) As Date

Do those appear in your module window? There may be something peculiar
about cutting and pasting from your newsgroup reader into the VBA window.
Try deleting any strange characters or white space at the start of each line
and reinserting space with the Tab key.

You are almost correct with the selection criteria for the SQL statement.
However, instead of referring to Forms!ENTRYFORM2!PO, you can refer to the
value directly, as it is in the scope of the code you are executing:

sql = "Update YourTable set Delivery = NextWeekday(Delivery, " _
& BoatETDDay & ") where PO=" & Me!PO

If PO is a text field then add embedded quotes.

Of course you can check for bad dates and ask for confirmation before
executing your update statement:

If DCount( "*", "YourTable", "PO=" & Me!PO _
& " and Weekday(Delivery)<>" & Me!BoatETDDay) > 0 then
If MsgBox( "Your question", vbYesNo Or vbQuestion) = vbYes Then
... code to update dates
End If
End If
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks. I get a compile error on the below code saying "Automation
type not supported in visual basic" and it masks out everything from
"Public..." to "...) As Date".

Public Function NextWeekday( _
? ? InDate As Date, _
? ? DayOfWeek As VbDayOfWeek _
? ) As Date
? Dim InDay As VbDayOfWeek
? InDay = Weekday(InDate)
? If InDay = DayOfWeek Then
? ? NextWeekday = InDate
? Else
? ? NextWeekday = InDate - InDay + DayOfWeek _
? ? ? ? + IIf(DayOfWeek < InDay, 7, 0)
? End If
End Function

The field that links the subform to the main form is called PO. The
name of the form is ENTRYFORM2. So would I write "where PO = Forms!
ENTRYFORM2!PO" or "ENTRYFORM2.Form!PO" in the code below where you
have to insert selection criteria?

Dim sql as string
sql = "Update YourTable set Delivery = NextWeekday(Delivery, " _
? ? & BoatETDDay & ") where <insert selection criteria here>"
CurrentDb.Execute sql

But the code changes the dates after the user changes the factory
without asking the user first. Can we first test to see if the dates
are bad and then if bad, have a box that comes up that asks if the
dates should be changed? The box does not have to have the actual
date in it like before, just a message like "Do you want to change the
Delivery dates to the actual week day this factory ships on?"

Thanks. I feel as if I'm taking the mile here.

Matt

 

[doyle60]

What do you mean by "boxes"? I did not copy any line-leading ">" or
"?". Before posting the problem I did take out the line continuation
marks too. Still got the error.

At your suggestion, I typed the whole thing over again, to make sure
it was all clean. I used no spaces and no indentation. Still get the
error.

I have Access 97, if that matters.

Did you try the code in an Access module yourself?

Could I possibly be getting the error because I need to do something
else somewhere first? I have not used the other code yet anywhere in
the database.

Thanks again. And thanks for the new check code.

Matt

 

[Graham Mandeno]

Hi Matt

In the post where you included the code (beginning "Thanks. I get a compile
error..."), each indented line begins with one or more small rectangular
characters after the ">" and before the code. These were replaced by "?" in
my reply.

For example (I've replaced each box with []):

Public Function NextWeekday( _
[] [] InDate As Date, _

I thought some strange characters in there might be causing the problem.

Yes, I wrote the code in an Access module and tested it before copying and
pasting it into my reply to you. In fact I just copied it from my post back
into another Access module and it still works.

What version of Access are you using? The only thing I can think would be
causing such an error is VbDayOfWeek, although that is an enumeration that
is declared in every version of the VBA Library. Try clicking your cursor
on it and pressing Ctrl-F2. The Object Browser window should open showing
the definitions for vbFriday, vbMonday, etc.

You could try replacing "As VbDayOfWeek" with "As Integer".
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[doyle60]

I am using Access 97.

Nothing comes up when I Ctrl+F2 "VBDayOfWeek".

When I change it to "Interger," it works if I also change the Dim line
to integer. The whole of the code is now this (no line continuers
here):

Public Function NextWeekDay(InDate As Date, DayOfWeek As Integer) As
Date
Dim InDay As Integer
InDay = WeekDay(InDate)
If InDay = DayOfWeek Then
NextWeekDay = InDate
Else
NextWeekDay = InDate - InDay + DayOfWeek + IIf(DayOfWeek < InDay, 7,
0)
End If
End Function

So I can use that?

Thanks,

Matt

 

[Douglas J. Steele]

Yes, that should be the equivalent for Access 97.

vbDayOfWeek is a type that limits the possible values to only those integers
between 0 and 6. Access 97 didn't support types like that.

 

[Graham Mandeno]

Hi Matt

Ah yes - Access 97 - that explains it. Enumerations declared in external
libraries were not available for local variables in Access 97.

Your updated version using "As Integer" should work just fine.

 

[doyle60]

Thanks.

Remember this code you gave me a while ago to test first whether there
are bad dates?:

If DCount( "*", "YourTable", "PO=" & Me!PO _
& " and Weekday(Delivery)<>" & Me!BoatETDDay) > 0 then
If MsgBox( "Your question", vbYesNo Or vbQuestion) = vbYes Then
... code to update dates
End If
End If

Well, the problem is I get a data mismatch error. I got this a lot
when I tried to do this myself. You see, the "Weekday(Delivery)" is a
date and the "Me!BoatETDDay" is a number.

Can this be rectified easily?

Thanks,

Matt