Auto populate a control

G

GailK

Acess 2000
In my form, I have an AgentName combo box from which the RowSource =
tblAgentDetails. This tbl contains: AgentName, AgentAlias and LeadAgentName.
Once the AgentName is selected I would like the next control, called
AgentAlias to populate based on the AgentName. I have the Control Source for
the AgentAlias set to AgentAlias in the form.

Having gone through the threads I have tried the AfterUpdate event and
DLookup but I have been unsuccessful. I would appreciate any assistance.

thx

Here are the Event Procedures tried:

Private Sub AgentName_AfterUpdate()
Me![AgentAlias] = Me.AgentName.Column(1)
End Sub

Private Sub AgentName_AfterUpdate()
Me.AgentAlias = DLookup("[AgentName]", "AgentDetails",
"[AgentAliasName]" = Me.AgentName)

End Sub
 
O

Ofer Cohen

For both cases

Check the row source of the combo, does it includes the AgentAlias field,
and does it located on the second column (you specified (1), which indicate
second column, because the count start with 0

Private Sub AgentName_AfterUpdate()
Me![AgentAlias] = Me.AgentName.Column(1)
End Sub

If you just want to display the value, you can set the control source of the
AgentAlias text field in the form to
=[AgentName].Column(1)

===================================
When the Dlookup use a string, if that the AgentAliasName is a text field,
you need to add a single column before and after the filter, and the equal
sign need to be inside the quote

Private Sub AgentName_AfterUpdate()
Me.AgentAlias = DLookup("[AgentName]", "AgentDetails",
"[AgentAliasName] = '" & Me.AgentName.Column(1) & "'")

Again, if you just want to display the value you can set the control source
of the AgentAlias text field in the form to
DLookup("[AgentName]", "AgentDetails", "[AgentAliasName] = '" &
[AgentName].Column(1) & "'")
 
G

GailK

Thanks so much for the quick response.. and such an easy solution!!! I
hadn't brought down the AgentAlias field in the row source. As for the
DLookup, I will keep a copy for future reference.

You're a gem!


Ofer Cohen said:
For both cases

Check the row source of the combo, does it includes the AgentAlias field,
and does it located on the second column (you specified (1), which indicate
second column, because the count start with 0

Private Sub AgentName_AfterUpdate()
Me![AgentAlias] = Me.AgentName.Column(1)
End Sub

If you just want to display the value, you can set the control source of the
AgentAlias text field in the form to
=[AgentName].Column(1)

===================================
When the Dlookup use a string, if that the AgentAliasName is a text field,
you need to add a single column before and after the filter, and the equal
sign need to be inside the quote

Private Sub AgentName_AfterUpdate()
Me.AgentAlias = DLookup("[AgentName]", "AgentDetails",
"[AgentAliasName] = '" & Me.AgentName.Column(1) & "'")

Again, if you just want to display the value you can set the control source
of the AgentAlias text field in the form to
DLookup("[AgentName]", "AgentDetails", "[AgentAliasName] = '" &
[AgentName].Column(1) & "'")


--
Good Luck
BS"D


GailK said:
Acess 2000
In my form, I have an AgentName combo box from which the RowSource =
tblAgentDetails. This tbl contains: AgentName, AgentAlias and LeadAgentName.
Once the AgentName is selected I would like the next control, called
AgentAlias to populate based on the AgentName. I have the Control Source for
the AgentAlias set to AgentAlias in the form.

Having gone through the threads I have tried the AfterUpdate event and
DLookup but I have been unsuccessful. I would appreciate any assistance.

thx

Here are the Event Procedures tried:

Private Sub AgentName_AfterUpdate()
Me![AgentAlias] = Me.AgentName.Column(1)
End Sub

Private Sub AgentName_AfterUpdate()
Me.AgentAlias = DLookup("[AgentName]", "AgentDetails",
"[AgentAliasName]" = Me.AgentName)

End Sub
Click to expand...
Click to expand...
 
Top