Default field on a form

[NoviceIan]

Hi,

We're building a new database and have a problem with two tables.

Referral

Referral Number
Referral Code
Referral Type (Combo "Referral Or Benckmark")

Make Referral

Make Referral Number
Customer Number
Referral Number
Status ("Referred" Or "Benchmark" Or "Actioned")

Basically we have different types of referrals some require following up to
ensure they get actioned others do not. When we make a referral we lookup
the values in the Referral Code field.

At the moment when we make a referral we have to select the status as well.
We would like this field to be defaulted depending on the value of the
referral type field in the referral table.

However this field needs be be altered later down the line when Referrals
are actioned. How would be go about this?

 

[Devlin]

Hello Ian,

It sounds as though you could use a little more knowlege in working with
databases before devling into this. However, if I understand your question,
you will want something like this:

Table Referrals
ReferralID As Long
ReferralRelatedCustomerID As Long
ReferralStatus As Integer (i.e. 1 = "Referred", 2 = "Benchmark", 3 =
"Actioned")
ReferralInfo...

Table Customers
CustomerID As Long
CustomerInfo...

You would then set up two forms for viewing your Referrals:

In one form (MyReferralListFrm) you would possibly set up a data list of
your referrals using either a datasheet or multi-forms.

Then you would have another form (MyReferralListDlg) that would be the
interface to the list. In this form you would include a sub form for
"MyReferralListFrm". You might include on this form some text or combo boxes
for desired criteria to search for in your list. Then you can simply add a
button that when executed would apply your criteria to an SQL where
expression and then set that expression to the form's filter, and then set
the form's filteron property to True.

Because you are going to be searching for specific referrals by their status
the first criteria you would want would be "ReferralStatus = " & StatusNumber
(i.e. 1 = "Referred", 2 = "Benchmark", 3 = "Actioned")

I hope this helps you out and best of luck.

 

[NoviceIan]

Hi,

Is there no way I can do this on the actual form where a referral is made?
Could I build an event or something?? All I basically want to do is have the
status field decide for itself (based on another field in the referral table)
wether a referral's status is referred or a benchmark.

We wont need to view the referrals using forms and referrals are never added
following the initial data entry. We will print a report of the refferals
that require following up.

However saying that we would also need to ensure that referrals that are
benchmarks can never have any other status and that referrals that require
following up should never be marked as benchmarks.

At the momoent we are only building a prototype so we could get away without
this automation however I would like to get it sorted.

Ian