Dlookup - autofill control based on combo box choice

[Guest]

I have a form with the following controls:
[EffectiveDate] - Agreement effective date
[CurrentTermExp] - Expiration date of current term
[cboRenewal] - Conditions under which agreement renews; one of the choices
is "Autorenews-1year" (bound to [RenewalID]=1)

I also have a query [qryAgmtsThatAutoExpire] with a calculated field [Expr1]
that essentially turns out the current term expiration date based on
[EffectiveDate], and on the condition that [RenewalID]=1.

I would like to write an event in my form so that when the user chooses
"Autorenews" ([RenewalID]=1) from the combo box, and provided that
[EffectiveDate] is not null, the control [CurrentTermExp] does a DLookup and
instantly displays the value calculated in [Expr1] in my query. If
[RenewalID] does NOT =1 I'd like to still be able to enter [CurrentTermExp]
manually.

 

[tina]

sounds like you already have the data you need in the current record in the
form: EffectiveDate and RenewalID. if that's all you're using to calculate
the expiration date of the current term, why use a query and a DLookup() ?
you could calculate the expiration date on the fly, and set the value of the
CurrentTermExp field, using the AfterUpdate event procedure of control
cboRenewal, as

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DateAdd("yyyy", 1, Me!EffectiveDate)
End If

End Sub

hth

 

[Guest]

The reason I need a DLookup is that it's not so simple to calculate the
current term's expiration date; I can't just add a year. The reason I can't
just add a year is that if today's date precedes the current term's
expiration _this_ year, then the year is still 2005. The expression I have in
the calculation field in my query is:

IIf([RenewalID]=1,IIf([Expired/Terminated]=0,IIf(Not
IsNull([EffectiveDate]),IIf(DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate]))<=Now(),DateSerial(Year(Now())+1,Month([EffectiveDate]),Day([EffectiveDate])),DateSerial(Year(Now()),Month([EffectiveDate]),Day([EffectiveDate]))))))

(There are some additional conditions in there but they're not really
pertinent to my original question.)

Any clues as to how to write the conditional DLookup?

sounds like you already have the data you need in the current record in the
form: EffectiveDate and RenewalID. if that's all you're using to calculate
the expiration date of the current term, why use a query and a DLookup() ?
you could calculate the expiration date on the fly, and set the value of the
CurrentTermExp field, using the AfterUpdate event procedure of control
cboRenewal, as

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DateAdd("yyyy", 1, Me!EffectiveDate)
End If

End Sub

hth

I have a form with the following controls:
[EffectiveDate] - Agreement effective date
[CurrentTermExp] - Expiration date of current term
[cboRenewal] - Conditions under which agreement renews; one of the choices
is "Autorenews-1year" (bound to [RenewalID]=1)

I also have a query [qryAgmtsThatAutoExpire] with a calculated field [Expr1]
that essentially turns out the current term expiration date based on
[EffectiveDate], and on the condition that [RenewalID]=1.

I would like to write an event in my form so that when the user chooses
"Autorenews" ([RenewalID]=1) from the combo box, and provided that
[EffectiveDate] is not null, the control [CurrentTermExp] does a DLookup and
instantly displays the value calculated in [Expr1] in my query. If
[RenewalID] does NOT =1 I'd like to still be able to enter [CurrentTermExp]
manually.

 

[tina]

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DLookUp("Expr1", "MyQuery")
End If

End Sub

if the query is pulling more than one record, either set criteria in the
query to pull the relevant record, or set criteria in the DLookup()
function.

hth

The reason I need a DLookup is that it's not so simple to calculate the
current term's expiration date; I can't just add a year. The reason I can't
just add a year is that if today's date precedes the current term's
expiration _this_ year, then the year is still 2005. The expression I have in
the calculation field in my query is:

IIf([RenewalID]=1,IIf([Expired/Terminated]=0,IIf(Not
IsNull([EffectiveDate]),IIf(DateSerial(Year(Now()),Month([EffectiveDate]),Da
y([EffectiveDate]))<=Now(),DateSerial(Year(Now())+1,Month([EffectiveDate]),D
ay([EffectiveDate])),DateSerial(Year(Now()),Month([EffectiveDate]),Day([Effe
ctiveDate]))))))

(There are some additional conditions in there but they're not really
pertinent to my original question.)

Any clues as to how to write the conditional DLookup?

sounds like you already have the data you need in the current record in the
form: EffectiveDate and RenewalID. if that's all you're using to calculate
the expiration date of the current term, why use a query and a DLookup() ?
you could calculate the expiration date on the fly, and set the value of the
CurrentTermExp field, using the AfterUpdate event procedure of control
cboRenewal, as

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DateAdd("yyyy", 1, Me!EffectiveDate)
End If

End Sub

hth

I have a form with the following controls:
[EffectiveDate] - Agreement effective date
[CurrentTermExp] - Expiration date of current term
[cboRenewal] - Conditions under which agreement renews; one of the choices
is "Autorenews-1year" (bound to [RenewalID]=1)

I also have a query [qryAgmtsThatAutoExpire] with a calculated field [Expr1]
that essentially turns out the current term expiration date based on
[EffectiveDate], and on the condition that [RenewalID]=1.

I would like to write an event in my form so that when the user chooses
"Autorenews" ([RenewalID]=1) from the combo box, and provided that
[EffectiveDate] is not null, the control [CurrentTermExp] does a

DLookup
and

instantly displays the value calculated in [Expr1] in my query. If
[RenewalID] does NOT =1 I'd like to still be able to enter [CurrentTermExp]
manually.

 

[Guest]

Tina,
Thanks for your help. There _is_ more than one record in my query, and
that's one place I get stuck. I'm not clear as to how to set up link
criteria, either in the query or in the DLookup, so that the right record
gets pulled.

Can you explain to me the different parts of the DLookup criteria, what they
refer to?

Best,
Laura

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DLookUp("Expr1", "MyQuery")
End If

End Sub

if the query is pulling more than one record, either set criteria in the
query to pull the relevant record, or set criteria in the DLookup()
function.

hth

The reason I need a DLookup is that it's not so simple to calculate the
current term's expiration date; I can't just add a year. The reason I can't
just add a year is that if today's date precedes the current term's
expiration _this_ year, then the year is still 2005. The expression I have in
the calculation field in my query is:

IIf([RenewalID]=1,IIf([Expired/Terminated]=0,IIf(Not
IsNull([EffectiveDate]),IIf(DateSerial(Year(Now()),Month([EffectiveDate]),Da
y([EffectiveDate]))<=Now(),DateSerial(Year(Now())+1,Month([EffectiveDate]),D
ay([EffectiveDate])),DateSerial(Year(Now()),Month([EffectiveDate]),Day([Effe
ctiveDate]))))))

(There are some additional conditions in there but they're not really
pertinent to my original question.)

Any clues as to how to write the conditional DLookup?

sounds like you already have the data you need in the current record in the
form: EffectiveDate and RenewalID. if that's all you're using to calculate
the expiration date of the current term, why use a query and a DLookup() ?
you could calculate the expiration date on the fly, and set the value of the
CurrentTermExp field, using the AfterUpdate event procedure of control
cboRenewal, as

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DateAdd("yyyy", 1, Me!EffectiveDate)
End If

End Sub

hth


message I have a form with the following controls:
[EffectiveDate] - Agreement effective date
[CurrentTermExp] - Expiration date of current term
[cboRenewal] - Conditions under which agreement renews; one of the choices
is "Autorenews-1year" (bound to [RenewalID]=1)

I also have a query [qryAgmtsThatAutoExpire] with a calculated field
[Expr1]
that essentially turns out the current term expiration date based on
[EffectiveDate], and on the condition that [RenewalID]=1.

I would like to write an event in my form so that when the user chooses
"Autorenews" ([RenewalID]=1) from the combo box, and provided that
[EffectiveDate] is not null, the control [CurrentTermExp] does a DLookup
and
instantly displays the value calculated in [Expr1] in my query. If
[RenewalID] does NOT =1 I'd like to still be able to enter
[CurrentTermExp]
manually.

 

[tina]

Access Help explains the DLookup() function pretty clearly, Laura. read that
first, and post back if you need clarification on a specific issue.

since you're already using a query, personally i would probably apply
criteria at the query level, rather than in the DLookup. if you need to
specify multiple criteria, especially, using the query design grid is easier
than writing a criteria string for the DLookup() function. you have to
decide what criteria on what query field will return the correct record; for
instance, is it the primary key value of the current record in the open
form? if you determine that the criteria IS a value from a field in the open
form, set the criteria in the query to refer to that field in that form, as

Forms!MyFormName!MyFieldName

hth

Tina,
Thanks for your help. There _is_ more than one record in my query, and
that's one place I get stuck. I'm not clear as to how to set up link
criteria, either in the query or in the DLookup, so that the right record
gets pulled.

Can you explain to me the different parts of the DLookup criteria, what they
refer to?

Best,
Laura

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DLookUp("Expr1", "MyQuery")
End If

End Sub

if the query is pulling more than one record, either set criteria in the
query to pull the relevant record, or set criteria in the DLookup()
function.

hth

The reason I need a DLookup is that it's not so simple to calculate the
current term's expiration date; I can't just add a year. The reason I can't
just add a year is that if today's date precedes the current term's
expiration _this_ year, then the year is still 2005. The expression I

have
in

the calculation field in my query is:

IIf([RenewalID]=1,IIf([Expired/Terminated]=0,IIf(Not

IsNull([EffectiveDate]),IIf(DateSerial(Year(Now()),Month([EffectiveDate]),Da
y([EffectiveDate]))<=Now(),DateSerial(Year(Now())+1,Month([EffectiveDate]),D
ay([EffectiveDate])),DateSerial(Year(Now()),Month([EffectiveDate]),Day([Effe
ctiveDate]))))))

(There are some additional conditions in there but they're not really
pertinent to my original question.)

Any clues as to how to write the conditional DLookup?




:

sounds like you already have the data you need in the current record

in
the

form: EffectiveDate and RenewalID. if that's all you're using to calculate
the expiration date of the current term, why use a query and a

DLookup()
?

you could calculate the expiration date on the fly, and set the

value of
the

CurrentTermExp field, using the AfterUpdate event procedure of control
cboRenewal, as

Private Sub cboRenewal_AfterUpdate()

If Not IsNull(Me!EffectiveDate) And Me!cboRenewal = 1 Then
Me!CurrentTermExp = DateAdd("yyyy", 1, Me!EffectiveDate)
End If

End Sub

hth


message I have a form with the following controls:
[EffectiveDate] - Agreement effective date
[CurrentTermExp] - Expiration date of current term
[cboRenewal] - Conditions under which agreement renews; one of the choices
is "Autorenews-1year" (bound to [RenewalID]=1)

I also have a query [qryAgmtsThatAutoExpire] with a calculated field
[Expr1]
that essentially turns out the current term expiration date based on
[EffectiveDate], and on the condition that [RenewalID]=1.

I would like to write an event in my form so that when the user chooses
"Autorenews" ([RenewalID]=1) from the combo box, and provided that
[EffectiveDate] is not null, the control [CurrentTermExp] does a DLookup
and
instantly displays the value calculated in [Expr1] in my query. If
[RenewalID] does NOT =1 I'd like to still be able to enter
[CurrentTermExp]
manually.