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
"Rex" wrote:
> 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
>
> On Jan 23, 4:51 pm, Rod Plastow <RodPlas...@discussions.microsoft.com>
> wrote:
> > 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" wrote:
> > > 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- Hide quoted text -- Show quoted text -
>
>
|
Similar Threads
