Dlookup - autofill control based on combo box choice

G

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.
 
T

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
 
G

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?




tina said:
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


Laura_Christian said:
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.
Click to expand...
Click to expand...
 
T

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


Laura_Christian said:
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?




tina said:
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


Laura_Christian said:
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
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
G

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

tina said:
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


Laura_Christian said:
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?




tina said:
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.
Click to expand...
Click to expand...
Click to expand...
 
T

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


Laura_Christian said:
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

tina said:
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


Laura_Christian said:
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
Click to expand...
have
in
the calculation field in my query is:

IIf([RenewalID]=1,IIf([Expired/Terminated]=0,IIf(Not
Click to expand...
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
Click to expand...
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
Click to expand...
DLookup()
?
you could calculate the expiration date on the fly, and set the
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup - multiple criteria 1
Invalid Use of Null - After Update 2
Using A Dlookup Function On A Form When A Combo Box is Updated 2
Running A Macro Based on Selection From Combo Box 1
Populating text box based on selection in two combo boxes 3
display a field in a form which isnt in the record source of the f 1
Populate combo box based on a control in the form 2
return value based on combo box selection 3

Top