change values in a form based on other values

[Rex]

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.


to elaborate


I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:


Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------

000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |


in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to

change in the Family table


Any help would be greatly appreciated..


Rex

 

[Rod Plastow]

Hi Rex,

You certainly can do this. There are two situations that I can think of:

- Where both the date and the family status are on the same bound form (or
Main/Sub form set)

- All other situations

In the first situation you can simply poke the new value of the status into
the control on the form and Access will take care of the table updating. The
status control can even be locked and/or disabled to prevent accidental
updating. I stress that for this to work the form and the status control
have to be bound to the table or a query based on that table.

In the second situation you will have to revert to some programming.
Although Access provides some natty little interrogation functions such as
DCount and DAvg there is no update equivalent; so you have to update the
table from first principles but it's quite straightforward. There are two
object models for the data base interface: ADO (ActiveX Data Objects) and DAO
(Data Access Objects), each with their strengths and weaknesses. Personally
I use ADO and could give you some sample code if you require it.

O.K. whichever situation you have I recommend the trigger for updating the
status to be the BeforeUpdate event of the date control. This is better than
the AfterUpdate event in that you can cancel the update from the
BeforeUpdate. In the event procedure you could check that the date is in a
sensible range and equal or later than the date sent issuing a message and
canceling the event if this is not true. Then depending upon whichever
situation you have, update the status remembering of course that it could be
possible for the user to delete the received date in which case you need to
reverse the status.

I realise I've given you no actual code. If you want this let me know which
situation and which object model you prefer.

Regards,

Rod

 

[Rex]

Hi Rod,

Thanks for replying.. I would be greatful to you if you can please send
me the code for both the situation you have mentioned as I think it
will help me learn about Access a bit more since I am beginner..

cheers
Rex

 

[Rod Plastow]

Rex,

Very busy today. Get back to you asap.

Rod

 

[Rod Plastow]

Hi Rex,
I’ve found a moment to answer you in part, the remainder will follow.
I assume you know how to invoke the VBA editor for any event. If not shout
and I’ll give you instructions.
Situation 1a – Assumes both date fields and the status field are on the SAME
form.

Private Sub dateReceived_BeforeUpdate(Cancel As Integer)
If IsNull(Me.dateReceived) Then 'User has deleted the date
Me.FamilyStatus = "a" 'Substitute your own value
Exit Sub
End If
If Me.dateReceived < Me.dateSent Then 'Illogical unless time flows backwards
MsgBox "Please enter a received date equal to or later than the date sent."
Me.dateReceived.SetFocus
Cancel = True
Exit Sub
End If
Me.FamilyStatus = "b" 'Substitute your own value for received
End Sub

You need to substitute your own status values in the above, omit the quotes
if the status is numeric. I have assumed you call your controls dateSent,
dateReceived and FamilyStatus; if not, substitute your own names. I also
assume you have set the format of the date controls to be one of the Access
date formats; Access then makes sure the user enters a date and not some
spurious value. The second If/End If construct tests for a logic error and
shows the use of Cancel to cancel the update.

To follow:
Situation 1b – Main/Sub form situation
Situation 2 – Direct DB update

Regards,

Rod