Fill fields automatically on Form

  • Thread starter Thread starter George van Niekerk
  • Start date Start date
G

George van Niekerk

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?
 
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
 
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] & """")
 
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 said:
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
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?
Click to expand...
.
Click to expand...
 
What is VarCompanyDescription? Is this a memory variable or a field or a text
box?
--
Duane Hookom
Microsoft Access MVP


George van Niekerk said:
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 said:
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
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?
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top