2 subforms in one form, how to make them talk?

[Luna Saisho]

Hiya,

I had posted this about a week ago and got an answer that unfortunately
didn't help out. Since then, I had another project to work on, and have gone
back to this to find I'm still stumped.

Basically, it goes like this:
I have "Form-Main" with two subforms: "Form-Main_Locations" and
"Form-Main_Shipment".

In the shipment area is a field called RecvDate. What this field should be
filled with, is the date we receive the product. When you click into this
field (I have it set to OnEnter so they can enter needed info and when they
get to here, it's set) it comes up with an error.

What I want it to do in "Form-Main_Shipment" is to have it look at
[ShipType] to find out if it's OTR or Rail (OTR means over the road, or by
truck) most likely with an iif statement. I want it to pull the value from
the Locations subform, which contains the days it takes to ship from that
location in the fields [OTR] and [Rail]. Back in the shipment subform,
[RecvDate] should have the result of [ShipDate] plus either [OTR] or [Rail]
as appropriate.

If this doesn't make sense, please let me know.

Thank you in advance for any assistance!
Stephanie

 

[Anne]

Regarding the date field:
To enter a data into the RecvDate, you should not have an onEnter event. If
it is a bound field the data will stay put. Format with an input mask to make
entry easier.

 

[Ron2006]

1) IMO I avoid hard formating - particularly in a date field. If you
simply say that the format of the field is "shortdate" then the user
can simply enter m/d " 1/4" and Access will fill in the rest making it
01/04/2006 just as in Excel.

2) You do not want the action in the onEnter event of the field to be
calculated. You want it to be in the afterupdate event of each of the
fields that are used to calculate the date.

Example-
FieldA and FieldB and FieldC are all on the form and required for the
calculations.

in the afterupdate of each.

if not isnull(me.fieldA) and not isnull(me.fieldB) and not
isnull(me.fieldC) then
..... calculation steps
me.datefieldname = results of calculation
endif

If you put this code in a subroutine

sub CalcNewDate()
if not isnull(me.fieldA) and not isnull(me.fieldB) and not
isnull(me.fieldC) then
..... calculation steps
me.datefieldname = results of calculation
endif
end sub


then in the afterupdate of FieldA and FieldB and FieldC put

CalcNewDate


===============

Hope this gave you some ideas.

Ron

 

[Luna Saisho]

Hi Ron,

On your points...

1) I already have it set up for shortdate. I do all I can to make it
easier for the user, since that's who it's for.

2) afterupdate huh? Definately worth a try! I could have sworn I tried
that, but since it's not yet working right, I probably haven't.

I will also try your code and see how it works out. Thank you very much for
your reply!
Stephanie

 

[Luna Saisho]

Oh, I forgot to mention in my last reply...

Should I use an unbound field? I'm hoping that this date can be put into
[RecvDate] so it can be modified by the user, if needed (products aren't
received on weekends or holidays, for instance.)

Thank you

 

[Ron2006]

Definitely have it bound to the table field.

There are ways that you can get it to guess the date better, but first
let's get it working.

Ron

 

[Luna Saisho]

Hiya Ron,

If it helps to know, I have two fields that [RecvDate] works with...
[ShipDate] and [ShipType]. ShipDate, as you might guess, is the date it
ships. (Isn't it novel how I came up with that name?) ShipType is a
drop-down (combo) box that allows the user to select one of two ship types.

With that, I created a new query, selected the correct tables, and added
your code as follows, at the end:

SELECT [ShipDate] AS Entity
FROM [Tbl-Tracking]
WHERE [ShipDate] IS NOT NULL
UNION
SELECT [ShipType] AS Entity
FROM [Tbl-Tracking]
WHERE [ShipType] IS NOT NULL
ORDER BY Entity;

And Access changed it to be...
SELECT [ShipDate] AS Entity
FROM [Tbl-Tracking]
WHERE [ShipDate] IS NOT NULL
UNION SELECT [ShipType] AS Entity
FROM [Tbl-Tracking]
WHERE [ShipType] IS NOT NULL
ORDER BY Entity;

Not much different, but it could be worth showing. When I try to run the
report that calls this query, I get the following error:
"The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect."

Did I miss something?

Thanks!
Steph

 

[Anne]

Ron,
Thought I had replied before, but it never reached the newsgroup.
I want to thank you for your response No. 1.
I had no idea, that I could create an input mask of mm/dd and access would
add the year. I tried it now and I am so pleased that access does
automatically convert it to mm/dd/yy or whichever way the form field is
formatted. I love it, what a time saver. I am so used to just entering the
month and day and having, for example Quickbooks, filling in the complete
date, that I for my own entries in access, I keep entering the date without
the year, only to wind up with an error message.
It is greatly appreciated. As a matter of fact, some time ago, I put this
issue out to the newsgroup and I was told it could not be done.
Thanks,
Anne

 

[Luna Saisho]

Ok, uhm, I'm feeling quite the fool here, as I replied to the wrong message.
I have two help requests posted right now, and I'm sorry for wasting your
time with an answer that doesn't well, apply... I'm sorry.

 

[Ron2006]

relative to the sql.

Should BOTH selects be selecting something "as Entity". It would seem
one of them should be labeled something else. It is such a simple query
I can't see where anything else could be wrong.

Ron

 

[Luna Saisho]

Hi Again,

Ignoring my latest reply that meant to go to a different thread (Could you
feel the heat from my blushing where you were?), I have some questions on
what you wrote. Please bear with me as I have yet to get into any more then
the most basic coding within Access.

I've come to find that this will give the right date when the form displays:

=IIf([ShipType]='OTR',[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![OTR],[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![Rail])

That goes into the field that shows the receive date, and is bound to
[RecvDate]. As I said above, it will display correctly when the form first
displays, but I cannot get it to update in any way. So I am going to try
your code, but in anticipation of my possible problems, I wanted to verify
some changes to make it work for me.

I only have two fields to check, [ShipType] (combo box containing two
entries, OTR and Rail) and [ShipDate]. Three, to be picky, but if you don't
select the first one, you're not selecting anything else yet. (That
would be the ship location that gives you the # of days by rail or OTR).

if not isnull(me.fieldA) and not isnull(me.fieldB) then {removed fieldC}
..... calculation steps
me.datefieldname = results of calculation
endif

For the calculation steps, would I put [RecvDate]=Iif ... (calculation
above)? And for the fields listed in your isnull statements, I want to make
sure I do it correctly, so would it look like "isnull(me.ShipType)" or
"isnull(ShipType)"?

Thank you!
Stephanie

 

[Ron2006]

From the bottom up.

You want to use me.fieldname whenever you are writing the code on the
form. If you address a field on a form from code anywhere else in the
application such as a module or another form you would use the format
forms![formname]![fieldname] (with some exceptions if the field
is on a subform.)

If you have a field that basically is being calculated using values
from other fields, then you will almost always want to force feed the
calculation in the afterupdate event of those fields or of another
field if they themselves are the result of some calculation.
If you don't do that then you will see the result in the on open (and
maybe if you force a refresh of the screen) but NOT when the other
fields change.

If that calculated field is being saved in a field, then the
recordsource MUST be the field in the table and NOT a calculation. That
is another reason for the forcefeeding of the calculation.

You will obviously have to switch the if format around to the normal
code format when you move it to the afterupdate event (and it should
also be in the oncurrent event of the form. This is if the field is a
bound field. If it is unbound then you can leave it as it is.
Luna Saisho wrote:

The logic for the calculation needs to be in the afterupdate event of
any and all fields that would affect its calculation. The reason for
that is that if it involves three fields but you have the update in
only the last two but you change the value of the first and it should
affect the calculation, it will not do so when you make the change but
will affect when the screen in refreshed or closed and re-opened.

I think I got all your questions.

Ron

 

[Luna Saisho]

I think I'm almost done bothering you, Ron. ^_^

So, I know where to put it, and most of how to enter it, but VB coding is
still my weak spot (Yes, I'm getting a good Access book as soon as I can,
within a week), but I do have one more question...

Can you help translate this from a form-type expression to vb code?

=IIf([ShipType]='OTR',[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![OTR],[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![Rail])

Thank you again. After you answer, you can have a handful of home made
chocolate chip cookies, k? ^_^

Stephanie

From the bottom up.

You want to use me.fieldname whenever you are writing the code on the
form. If you address a field on a form from code anywhere else in the
application such as a module or another form you would use the format
forms![formname]![fieldname] (with some exceptions if the field
is on a subform.)

If you have a field that basically is being calculated using values
from other fields, then you will almost always want to force feed the
calculation in the afterupdate event of those fields or of another
field if they themselves are the result of some calculation.
If you don't do that then you will see the result in the on open (and
maybe if you force a refresh of the screen) but NOT when the other
fields change.

If that calculated field is being saved in a field, then the
recordsource MUST be the field in the table and NOT a calculation. That
is another reason for the forcefeeding of the calculation.

You will obviously have to switch the if format around to the normal
code format when you move it to the afterupdate event (and it should
also be in the oncurrent event of the form. This is if the field is a
bound field. If it is unbound then you can leave it as it is.
Luna Saisho wrote:

The logic for the calculation needs to be in the afterupdate event of
any and all fields that would affect its calculation. The reason for
that is that if it involves three fields but you have the update in
only the last two but you change the value of the first and it should
affect the calculation, it will not do so when you make the change but
will affect when the screen in refreshed or closed and re-opened.

I think I got all your questions.

Ron

 

[Ron2006]

=IIf([ShipType]='OTR',[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-Locatio­nInfo]![OTR],[ShipDate]+[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![R­ail])


If me.ShipType = 'OTR' then
me.recvdate = me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-Locatio­nInfo]![OTR]
else
me.recvdate = me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![R­ail]
end if


This assums that shiptype and recvdate are on the same subform. There
may be a need for another form! somewhere in the addressing of the
other subform field. I believe it would go between the [Form-Main]!
and the [Form-Main_Sub-locattionInfo] You may have to play around with
that one, although since you got a value in the other location as
source there may be nothing else needed.

Ron

 

[Luna Saisho]

Hi Ron,

I put the following code in the Afterupdate area for each of the three
fields needed, the two to check, [ShipDate] and [ShipType], as well as
[RecvDate] where the new date should show:

Private Sub RecvDate_AfterUpdate()

If Not IsNull(Me.ShipDate) And Not IsNull(Me.ShipType) Then
If Me.ShipType = "OTR" Then
Me.RecvDate = Me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![OTR]
Else
Me.RecvDate = Me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![Rail]

End If
End If
End Sub


And viola, we have a winner!! Thank you so much! *Big hugs* I so
appreciate your help!

Stephanie

 

[Ron2006]

I am glad it seems to work.

With putting the code in the afterupdate event for the RecvDate I
believe you are making it that they cannot change the date to anything
else IF there is a value in the other two fields. I am not sure I would
wont it there. Now if you have RecvDate locked, then it does not
matter, since they would not be able to change it anyway. That would
simply make it un-usable and never executed code.

You can have Recvdate locked, and the code for the other two fields
will work.

Have a great day.

Ron

 

[Ron2006]

Although I said: I am not sure I would wont it there.

I really meant: I am not sure I would want it there.

Ron

 

[Ron2006]

Luna,

You can add a little more code to your routine to cover the weekends so
that only holidays would require modification.....

Here is your code as you stated with the addition:

If Not IsNull(Me.ShipDate) And Not IsNull(Me.ShipType) Then
If Me.ShipType = "OTR" Then
Me.RecvDate = Me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![OTR]
Else
Me.RecvDate = Me.ShipDate +
[Forms]![Form-Main]![Form-Main_Sub-LocationInfo]![Rail]


End If

' add following
' code to handle Saturday and Sunday

if datepart("w",me.Recvdate) = 7 then ' Saturday
me.Recvdate = me.Recvdate + 2
elseif datepart("w",me.Recvdate) = 1 then ' Sunday
me.Recvdate = me.Recvdate + 1
endif

' End of Saturday / sunday code.

End If


===================================

In fact you could add just that new code to the afterupdate to really
surprise the user.... or do the two tests and give the user a msgbox
asking if they really want to put in a recvdate of
Saturday/Sunday......

Have a great day.


Ron

 

[Luna Saisho]

Wow, fantastic! That will make life simpler for Mike, and Mike happier with
me. ^_^

Thank you again, you've taught me quite a bit and it is very appreciated!
Steph