How to suggest entering a Monday Date

[Graham Mandeno]

Hi Matt

[Delivery] might be a date, but Weekday([Delivery]) is a number, same as
[BoatETDDay].

I suspect the problem is with [PO]. Is it numeric or text? If it is text
then you need to enclose the value from your form in quotes:

If DCount( "*", "YourTable", "PO='" & Me!PO _
& "' and Weekday(Delivery)<>" & Me!BoatETDDay) > 0 then

Note the extra single quotes after "PO=" and before " and Weekday..."

 

[doyle60]

Okay, so now I'm moving on to completing the code in the after update
of the factory controls. The code that I put in is below and it only
sort of works.

I tried to keep from you a little complication that I didn't think
mattered to you. You see there are actually two factories for each
record. If the second factory (Subfactory) is not filled in, then the
first factory's (FactoryID) BoatETDDay is used and if the second one
is filled in, then that second BoatETDDay is used.

So the query the subform is based on brings in the correct BoatETDDay
by bringing in the Factory table twice and properly linking them in
the query grid. The field line reads as follows:

BoatETDDay1: IIf([Factorytbl_1].[FactoryID] Is Null,[Factorytbl].
[BoatETDDay],[Factorytbl_1].[BoatETDDay])

So the field is actually "BoatETDDay1" and not "BoatETDDay".
The query is called: OrderDetailsqry

Other names:
The subform is called: OrderDetailssub
It has a control called: BoatETDDay
With the control source: BoatETDDay1

The code below works only for the factory and completely ignores what
is in the subfactory (which, if filled in, is the one that matters).
I can't make heads or tails out of why this is happening.

I get an error if I try to change the BoatETDDay to the name in the
query: BoatETDDay1. Why? I don't understand.

Thanks.

The code:
__________________________

If Not IsNull(BoatETDDay) Then
If DCount("*", "OrderDetailsQry", "PO='" & Me!PO & "' and
Weekday(Delivery)<>" & Me!BoatETDDay) > 0 Then
If MsgBox("Do you want to change the Delivery dates to the actual
week day this factory ships on?", vbYesNo Or vbQuestion) = vbYes Then

'New Code:
With Me![OrderDetailssub].Form.RecordsetClone
.MoveFirst
Do Until .EOF
.Edit
!Delivery = NextWeekDay(!Delivery, BoatETDDay)
.Update
.MoveNext
Loop
End With

'End of New Code

End If
End If
End If
________________________________

 

[Graham Mandeno]

OK, so now let's see if I can get this straight

Your main form is based on your PurchaseOrders table.
That is the table with the two factory fields (Factory and Subfactory).
Your subform is based on your OrderDetails table.
OrderDetails is related to PurchaseOrders many-to-one.

All correct so far?

Now, the bits I don't understand...

Your Delivery date field is in OrderDetails, right? Is this really where
you want it? Do you have different items in an order with different target
delivery dates?

Supposing you do need the Delivery date field in OrderDetails, all the order
detail records have the same Factory (and Subfactory), right? (because these
fields are in the PO table) Why do you then need to include BoatETDDay1 in
you subform's query?

It seems to me that your code should be doing the following:

Factory_AfterUpdate:
If Subfactory.ETDDay is not null then do nothing
(because Subfactory.ETDDay is used and Factory.ETDDay is irrelevant)
Otherwise, Call CheckDateChange(Factory.ETDDay)

Subfactory_AfterUpdate:
Call CheckDateChange(Subfactory.ETDDay)

Private Sub CheckDateChange(NewETDDay As Integer)
If DCount("*", "OrderDetails", "PO='" & Me!PO _
& "' and Weekday(Delivery)<>" & NewETDDay) > 0 Then
If MsgBox("Do you want to change the Delivery dates " _
& "to the actual week day this factory ships on?", _
vbYesNo Or vbQuestion) = vbYes Then
CurrentDb.Execute "Update OrderDetails " _
& "set Delivery = NextWeekDay(Delivery, " _
& BoatETDDay & ") where PO='" & Me!PO & "'", _
dbFailOnError
Me![OrderDetailssub].Form.Requery
End If
End If
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Okay, so now I'm moving on to completing the code in the after update
of the factory controls. The code that I put in is below and it only
sort of works.

I tried to keep from you a little complication that I didn't think
mattered to you. You see there are actually two factories for each
record. If the second factory (Subfactory) is not filled in, then the
first factory's (FactoryID) BoatETDDay is used and if the second one
is filled in, then that second BoatETDDay is used.

So the query the subform is based on brings in the correct BoatETDDay
by bringing in the Factory table twice and properly linking them in
the query grid. The field line reads as follows:

BoatETDDay1: IIf([Factorytbl_1].[FactoryID] Is Null,[Factorytbl].
[BoatETDDay],[Factorytbl_1].[BoatETDDay])

So the field is actually "BoatETDDay1" and not "BoatETDDay".
The query is called: OrderDetailsqry

Other names:
The subform is called: OrderDetailssub
It has a control called: BoatETDDay
With the control source: BoatETDDay1

The code below works only for the factory and completely ignores what
is in the subfactory (which, if filled in, is the one that matters).
I can't make heads or tails out of why this is happening.

I get an error if I try to change the BoatETDDay to the name in the
query: BoatETDDay1. Why? I don't understand.

Thanks.

The code:
__________________________

If Not IsNull(BoatETDDay) Then
If DCount("*", "OrderDetailsQry", "PO='" & Me!PO & "' and
Weekday(Delivery)<>" & Me!BoatETDDay) > 0 Then
If MsgBox("Do you want to change the Delivery dates to the actual
week day this factory ships on?", vbYesNo Or vbQuestion) = vbYes Then

'New Code:
With Me![OrderDetailssub].Form.RecordsetClone
.MoveFirst
Do Until .EOF
.Edit
!Delivery = NextWeekDay(!Delivery, BoatETDDay)
.Update
.MoveNext
Loop
End With

'End of New Code

End If
End If
End If
________________________________

 

[doyle60]

Oh, boy. I put the BoatETDDay in the query of the subform so we could
do the first part of my question. Remember that when the date is
changed itself, we have code that runs to check it. So there is a
line in that code that reads:

If EnteredDay <> BoatETDDay Then

Including the BoatETDDay in the query, which was easy to do, made
writing this code much easier.

This database has been used for 10 years now and the Delivery is in
the subform as it should be. It has turned out that 99% of POs use
the same dang date for all records in the sub and there is a general
rule that they should have the same date, but I'm convinced I did the
right thing at the time. It can't be changed now, of course. The
things is intensely complex, believe me.

If we may take one step back, what I'm having problems understanding
is where the code is finding the "BoatETDDay" in the expression:

If DCount("*", "OrderDetailsQry020", "PO='" & Me!PO & "' and
Weekday(Delivery)<>" & Me!BoatETDDay) > 0 Then

Is that a field on the mainform? There isn't one.
Is that a field in the subform's query? There isn't one. It's
BoatETDDay1.
Is that a name of a control in the subform? There is one. But the
code seems to ignore what it is actually returning there.

Can you tell me where it is looking and finding it? Because it is
finding it. There is a factorytbl that has that in it but that table
is not called up anywhere. It is in two queries back from the query
that the sub is based on. The query takes the two factories and
figures out the correct BoatETDDay, calling it BoatETDDay1.

What I simply want this one line of code to do is to have BoatETDDay
refer to the BoatETDDay1 in the query or the name of the field in the
subform (which is BoatETDDay).

I tried to do this myself but have failed completely. I tried taking
out the "Me!" before it. I've tried adding OrderDetailsQry020 before
it. But no luck.

Ahh! I just thought of something. Could the problem be that I have
not explained to you that both the subfactory and the Factory are
based on the same table?

Anyway, if I could simply get that one line of code to refer to what
is in the query OrderDetailsQry020, I believe my problems would all be
solved.

Thanks for your patience.

If it doesn't work after this, I'll just write a little reminder to
the user to hand check those dates.

Matt

 

[Graham Mandeno]

Hi Matt

Thanks for your patience.

If it doesn't work after this, I'll just write a little reminder to
the user to hand check those dates.

Hey, I'm happy to be patient. Don't give up - we're very close to nailing
this

I definitely think that you should leave BoatETDDay and any joins to
Factorytbl out of your subform. I'll explain later how to handle the
problem of
If EnteredDay <> BoatETDDay Then ...

In fact, I would not include Factorytbl in the RecordSource query of the
main form either.

I assume that you select Factory and Subfactory with combo boxes, right?

Create a query, qrySelectFactory, as follows:

Select FactoryID, FactoryName, BoatETDDay
from Factorytbl order by FactoryName;

Set the properties for both your Factory and Subfactory combo boxes to:
RowSource: qrySelectFactory
ColumnCount: 3
BoundColumn: 1
ColumnWidths: 0;;0
AfterUpdate: =CheckDateChange()

Create a textbox on your main form:
Name: txtBoatETDDay
ControlSource: =Val( Nz( IIf( Subfactory.Column(2) > 0,
Subfactory.Column(2), Factory.Column(2) ), 0 ) )
Visible: No

The complicated ControlSource expression should cover all possibilities of
factory fields being null or BoaETDDay values being missing in a factory
record.

Now you can use txtBoatETDDay, not BoatETDDay in your calculations:

Private Function CheckDateChange()
If txtBoatETDDay > 0 Then
If DCount("*", "OrderDetails", "PO='" & Me!PO _
& "' and Weekday(Delivery)<>" & txtBoatETDDay) > 0 Then
If MsgBox("Do you want to change the Delivery dates " _
& "to the actual week day this factory ships on?", _
vbYesNo Or vbQuestion) = vbYes Then
CurrentDb.Execute "Update OrderDetails " _
& "set Delivery = NextWeekDay(Delivery, " _
& txtBoatETDDay & ") where PO='" & Me!PO & "'", _
dbFailOnError
Me![OrderDetailssub].Form.Requery
End If
End If
End If
End Function

Finally, modify the code in your subform to refer to the textbox on the
parent form:

Private Function Delivery_AfterUpdate()
Dim SuggestedDate as Date, EnteredDay as Integer
Dim BoatETDDay as Integer, msg as String
EnteredDay = Weekday(Delivery)
BoatETDDay = Me.Parent!txtBoatETDDay
If EnteredDay <> BoatETDDay And BoatETDDay > 0 then
SuggestedDate = Delivery - EnteredDay + BoatETDDay _
+ IIf(BoatETDDay < EnteredDay, 7, 0)
msg = Delivery & " is not a " & Format(SuggestedDate, "dddd") _
& vbCrLf & vbCrLf & "Do you wish to change the delivery date to " _
& SuggestedDate & "?"
If MsgBox(msg, vbQuestion Or VbYesNo) = vbYes Then
Delivery = SuggestedDate
End If
End If
End Sub
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand