Fill fields automatically on Form

George van Niekerk · External Since: Oct 19, 2009 Posts: 3

I have a table with fields - Financialcompany and companydescription, which i
use in a form to update another table with the same field names. I have used
the following code on the exit event for the Financialcompany filed in the
form.

Private Sub Company_Exit(Cancel As Integer)
Dim varCompanyName As Variant
varCompanyName = DLookup("CompanyDescription", "LTCompanies",
"FinancialCompany =[FinancialCompany] ")
If (Not IsNull(varCompanyName)) Then Me![CompanyName] = varCompanyName
End Sub

The problem is that the CompanyDescription field is updated with the same
description i.e. first record of the Company table, irrespective of the
selection of the financialcompany field in the form. Hope this makes sense.
WHat am i doing wrong?
--
Kindest regards

John Spencer · External Since: Jul 15, 2009 Posts: 47

The problem is that you are finding the first match where the field
FinancialCompany matches the field FinancialCompany in the same record. That
is every record in LTCompanies unless you have a null.

What you want is to match the current value of the Company control to a record
in LTCompanies. So try:

varCompanyName = DLookup("CompanyDescription", "LTCompanies",
"FinancialCompany =""" & Me.[Company] & """")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

George van Niekerk wrote:
> I have a table with fields - Financialcompany and companydescription, which i
> use in a form to update another table with the same field names. I have used
> the following code on the exit event for the Financialcompany filed in the
> form.
>
> Private Sub Company_Exit(Cancel As Integer)
> Dim varCompanyName As Variant
> varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> "FinancialCompany =[FinancialCompany] ")
> If (Not IsNull(varCompanyName)) Then Me![CompanyName] = varCompanyName
> End Sub
>
> The problem is that the CompanyDescription field is updated with the same
> description i.e. first record of the Company table, irrespective of the
> selection of the financialcompany field in the form. Hope this makes sense.
> WHat am i doing wrong?

Duane Hookom · External Since: Feb 07, 2005 Posts: 1359

I would first question why you think you need to store the comapny name in
more than one place in your database. This is generally considered bad
practice.

If you really want to store it, try this assuming FinancialCompany is a text
field and your current form has a field in its record source FinancialCompany.

varCompanyName = DLookup("CompanyDescription", "LTCompanies",
"FinancialCompany =""" & [FinancialCompany] & """")

--
Duane Hookom
Microsoft Access MVP

"George van Niekerk" wrote:

> I have a table with fields - Financialcompany and companydescription, which i
> use in a form to update another table with the same field names. I have used
> the following code on the exit event for the Financialcompany filed in the
> form.
>
> Private Sub Company_Exit(Cancel As Integer)
> Dim varCompanyName As Variant
> varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> "FinancialCompany =[FinancialCompany] ")
> If (Not IsNull(varCompanyName)) Then Me![CompanyName] = varCompanyName
> End Sub
>
> The problem is that the CompanyDescription field is updated with the same
> description i.e. first record of the Company table, irrespective of the
> selection of the financialcompany field in the form. Hope this makes sense.
> WHat am i doing wrong?
> --
> Kindest regards

George van Niekerk · External Since: Oct 19, 2009 Posts: 3

Thanks for the response John.

I have tried your proposal using the after update event for the financial
company combo field as the control, however no success. The following string
was used

VarCompanyDescription = DLookup("CompanyDescription", "LTCompanies",
"FinancialCompany =""" & Me.[FinancialCompany] & """")

CompanyDescription being the field to auto fill once the FinancialCompany
combo field is selected, which exist in both my form and the lookup table

LTCompanies being the lookup table and
FinancialCompany being a combo field in the form and exist in the
LTCompanies table.

Please excuse my ignorance - I know i am missing something very simple.

Kindest regards

"John Spencer" wrote:

> The problem is that you are finding the first match where the field
> FinancialCompany matches the field FinancialCompany in the same record. That
> is every record in LTCompanies unless you have a null.
>
> What you want is to match the current value of the Company control to a record
> in LTCompanies. So try:
>
> varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> "FinancialCompany =""" & Me.[Company] & """")
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> George van Niekerk wrote:
> > I have a table with fields - Financialcompany and companydescription, which i
> > use in a form to update another table with the same field names. I have used
> > the following code on the exit event for the Financialcompany filed in the
> > form.
> >
> > Private Sub Company_Exit(Cancel As Integer)
> > Dim varCompanyName As Variant
> > varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> > "FinancialCompany =[FinancialCompany] ")
> > If (Not IsNull(varCompanyName)) Then Me![CompanyName] = varCompanyName
> > End Sub
> >
> > The problem is that the CompanyDescription field is updated with the same
> > description i.e. first record of the Company table, irrespective of the
> > selection of the financialcompany field in the form. Hope this makes sense.
> > WHat am i doing wrong?
> .
>

Duane Hookom · External Since: Feb 07, 2005 Posts: 1359

What is VarCompanyDescription? Is this a memory variable or a field or a text
box?
--
Duane Hookom
Microsoft Access MVP

"George van Niekerk" wrote:

> Thanks for the response John.
>
> I have tried your proposal using the after update event for the financial
> company combo field as the control, however no success. The following string
> was used
>
> VarCompanyDescription = DLookup("CompanyDescription", "LTCompanies",
> "FinancialCompany =""" & Me.[FinancialCompany] & """")
>
> CompanyDescription being the field to auto fill once the FinancialCompany
> combo field is selected, which exist in both my form and the lookup table
>
> LTCompanies being the lookup table and
> FinancialCompany being a combo field in the form and exist in the
> LTCompanies table.
>
> Please excuse my ignorance - I know i am missing something very simple.
>
> Kindest regards
>
>
> "John Spencer" wrote:
>
> > The problem is that you are finding the first match where the field
> > FinancialCompany matches the field FinancialCompany in the same record. That
> > is every record in LTCompanies unless you have a null.
> >
> > What you want is to match the current value of the Company control to a record
> > in LTCompanies. So try:
> >
> > varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> > "FinancialCompany =""" & Me.[Company] & """")
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2009
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> > George van Niekerk wrote:
> > > I have a table with fields - Financialcompany and companydescription, which i
> > > use in a form to update another table with the same field names. I have used
> > > the following code on the exit event for the Financialcompany filed in the
> > > form.
> > >
> > > Private Sub Company_Exit(Cancel As Integer)
> > > Dim varCompanyName As Variant
> > > varCompanyName = DLookup("CompanyDescription", "LTCompanies",
> > > "FinancialCompany =[FinancialCompany] ")
> > > If (Not IsNull(varCompanyName)) Then Me![CompanyName] = varCompanyName
> > > End Sub
> > >
> > > The problem is that the CompanyDescription field is updated with the same
> > > description i.e. first record of the Company table, irrespective of the
> > > selection of the financialcompany field in the form. Hope this makes sense.
> > > WHat am i doing wrong?
> > .
> >