Update Text Field Based on Value in First Text Field

G

Guest

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
S

Steve Schapel

Gwen,

This is not a recommended approach. If the Borrower is already
associated with a particular Loan Number in your database, I would
advise against redundantly storing that information again in your Loan
Tracking table. However, I can understand wanting the borrower's name
to be displayed on your form. There are a number of approaches that can
be taken to this. Some of these are discussed in this article
http://accesstips.datamanagementsolutions.biz/lookup.htm
 
G

Guest

You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry
 
C

cornboy

I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry said:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

Gwen H said:
I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
D

Douglas J. Steele

You're correct. Quotes are needed if it's a text field.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry said:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " &
Me.Account)

Barry

Gwen H said:
I have a form that will be used to add records to a table
"loanTracking". I
want the first field on this form to be a text field called "account".
Users
will enter a loan number in account, and when they tab away from that
field I
want it to look up the borrower's name in the query "openLoans". Then I
want
to store both the user-entered loan number and the borrower's name in
the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so
I can
get an idea for the correct syntax?

Thanks!
GwenH
 
G

Guest

Here's the deal: We have thousands of loan account numbers, but only a few of
these will be stored in the Loan Tracking table. So, I have two tables: One
that I update from Business Objects that lists all open account numbers and
the borrower(s), and the other is the Loan Tracking table. The purpose of the
Loan Tracking table is to track loans on whom we've received collateral
insurance.

Since the Loan Tracking table will be so much smaller in comparison, I don't
see a problem with storing redundant data. We will be periodically deleting
data from this table anyway - it will only hold info on insurance documents
received in the last 6 months.
 
G

Guest

Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry said:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

Gwen H said:
I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
R

Roger Carlson

You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry said:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

:

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
G

Guest

Still not working. The error message says: "Microsoft Access can't find the
field "|" referred to in your expression." Here's my code. I removed the
quotes from around [openLoansQuery].[loanNumber] because before I did that, I
was getting an Data Type mismatch error. The field Loan Number is formatted
to be a number.

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
Me.borrower = DLookup("borrower", "openLoansQuery",
[openLoansQuery].[loanNumber] = " & Me.accountNo & ")
End If
End Sub

Thanks!
GwenH

Roger Carlson said:
You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

:

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
G

Guest

Okay, I corrected my code as I've posted below. Now I'm getting a different
type of error. The field "accountNo" is the first field on the form. When I
enter a loan number in the field and tab to the next field, which is noteNo,
I get an error message telling me that the field "borrower" (the third field
on the form) cannot contain a null value. I haven't even tabbed to that field
yet, and apparently the form thinks I'm trying to save the record! Borrower
is a required field in the underlying table, but why is the form checking its
contents when I haven't even tabbed to that field yet? Aaaarrrrrggggghhhhh!

Thanks,
GwenH

Roger Carlson said:
You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

:

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
G

Guest

Never mind ... I solved that problem by moving the Borrower field to the
bottom of the form's tab order. However, while I'm not getting any error
messages now, the Borrower field is not getting updated. After I enter a
valid account number in the "accountNo" field and tab off it, the Borrower
field is still blank.

Ideas?

Thanks,
GwenH

Roger Carlson said:
You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

cornboy said:
I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

:

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's name in the
table "loanTracking." I do not want to use a combo box or list box for
"account".

What is the best way to do this? And can you please post pseudocode so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
R

Roger Carlson

Can you post your corrected code? Also, have you put a breakpoint in the
code to make sure it's being executed?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Never mind ... I solved that problem by moving the Borrower field to the
bottom of the form's tab order. However, while I'm not getting any error
messages now, the Borrower field is not getting updated. After I enter a
valid account number in the "accountNo" field and tab off it, the Borrower
field is still blank.

Ideas?

Thanks,
GwenH

Roger Carlson said:
You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Thanks for your help ... I'm getting a compile error on your code: "Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

:

I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " & Me.Account)

Barry

:

I have a form that will be used to add records to a table "loanTracking". I
want the first field on this form to be a text field called "account". Users
will enter a loan number in account, and when they tab away from that field I
want it to look up the borrower's name in the query "openLoans". Then I want
to store both the user-entered loan number and the borrower's
name
in the
table "loanTracking." I do not want to use a combo box or list
box
for
"account".

What is the best way to do this? And can you please post
pseudocode
so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
G

Guest

Here is my corrected code again:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
Me.borrower = DLookup("borrower", "openLoansQuery",
"[openLoansQuery].[loanNumber] = " & Me.accountNo)
End If
End Sub

Thanks!



Roger Carlson said:
Can you post your corrected code? Also, have you put a breakpoint in the
code to make sure it's being executed?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Never mind ... I solved that problem by moving the Borrower field to the
bottom of the form's tab order. However, while I'm not getting any error
messages now, the Borrower field is not getting updated. After I enter a
valid account number in the "accountNo" field and tab off it, the Borrower
field is still blank.

Ideas?

Thanks,
GwenH

Roger Carlson said:
You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]= '" &
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Thanks for your help ... I'm getting a compile error on your code:
"Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then

DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

:

I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little uncertain of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " &
Me.Account)

Barry

:

I have a form that will be used to add records to a table
"loanTracking". I
want the first field on this form to be a text field called
"account". Users
will enter a loan number in account, and when they tab away from
that field I
want it to look up the borrower's name in the query "openLoans".
Then I want
to store both the user-entered loan number and the borrower's name
in the
table "loanTracking." I do not want to use a combo box or list box
for
"account".

What is the best way to do this? And can you please post pseudocode
so I can
get an idea for the correct syntax?

Thanks!
GwenH
 
R

Roger Carlson

Well, the obvious question here is: Is there actually an account number? If
not, it won't even enter the If. Put a break point on your dlookup line to
see if its even entering the conditional.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Gwen H said:
Here is my corrected code again:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then
Me.borrower = DLookup("borrower", "openLoansQuery",
"[openLoansQuery].[loanNumber] = " & Me.accountNo)
End If
End Sub

Thanks!



Roger Carlson said:
Can you post your corrected code? Also, have you put a breakpoint in the
code to make sure it's being executed?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Gwen H said:
Never mind ... I solved that problem by moving the Borrower field to the
bottom of the form's tab order. However, while I'm not getting any error
messages now, the Borrower field is not getting updated. After I enter a
valid account number in the "accountNo" field and tab off it, the Borrower
field is still blank.

Ideas?

Thanks,
GwenH

:

You have to assign the value produced in the DLookup to some variable or
object:

Me.Borrower =
DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'"
&
Me.AccountNo & "'")

In this case Borrower would be a textbox on the form.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Thanks for your help ... I'm getting a compile error on your code:
"Expected
=". Here's my code:

Sub accountNo_AfterUpdate()
If Len(accountNo) > 0 Then

DLookup("borrower","openLoansQuery","[openLoansQuery].[loanNumber]=
'" & Me.AccountNo & "'")
End If
End Sub

Thanks again!
GwenH

:

I think, if 'account' is a text field as indicated in your initial
post Gwen, you'll want to put quotes around the Me.Account thing:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = '" &
Me.Account & "'")

I this right Barry? I'm kind of a noob and I'm a little
uncertain
of
this.

Barry Gilbert wrote:
You could use DLookup:

DLookup("BorrowsName","openLoans","[openLoans].[loanNumber] = " &
Me.Account)

Barry

:

I have a form that will be used to add records to a table
"loanTracking". I
want the first field on this form to be a text field called
"account". Users
will enter a loan number in account, and when they tab away from
that field I
want it to look up the borrower's name in the query "openLoans".
Then I want
to store both the user-entered loan number and the
borrower's
name
in the
table "loanTracking." I do not want to use a combo box or
list
box
for
"account".

What is the best way to do this? And can you please post pseudocode
so I can
get an idea for the correct syntax?

Thanks!
GwenH
 

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

Top