Take This Value and Shove It

[Guest]

I have a form named "enterLoanDocs". Its record source is the table named
"loanOpsTracking". On this form, I have a field named "accountNo" whose
control source is the field "accountNo" in the aforementioned table. I also
have a field named "borrower" whose control source is:

=DLookUp("[name]","openLoans"," [loanNumber] = " &
Forms!enterLoanDocs!accountNo)

When I enter a value in the accountNo field, the form looks up the
borrower's name in the table "openLoans." However, when I finish filling out
the form, the values in all the fields except "borrower" are entered in the
loanOpsTracking table.

How do I tell the form to take the value in the borrower field and shove it
into the borrower field of the loanOpsTracking table?

Thanks,
GwenH

 

[Douglas J. Steele]

Since the borrower name is completely derivable from accountNo, you
shouldn't be storing it the second table (assuming that accountNo is stored
there)

 

[Guest]

What Douglas recommends is the correct thing to do, but I thought I'd follow
up with a bit of the why and how.

The why is that the Control Source property of a control on a form can be
used as you are doing it. It is considered an Ubound control because it is
not bound to a field in the form's record source. You can't, however, do it
both ways. There are some tricks to making it appear you are having it both
ways, but that is another subject.

The how is that if you do as Douglas recommends, you are following good
database normalization rules against store redundant data. So, the trick is
any time you want to display the borrower's name, use the same technique you
are using to display the name on the form.

 

[Guest]

Here's why it has to be done the way I'm doing it. The account number is a
loan account number. The data that gets stored in the form's record source
consists of a loan account number, a note number, the borrower's name, and
date received. The form's underlying table is simply a log of documents
received. So yes, an account number may be stored in the underlying table
multiple times, but each time it will have a unique note number. The bank I
work for has thousands of open loan account numbers, but only about 300 of
them will be stored in the table in question at any given time, and multiple
occurrences of the same account number will have different note numbers.
Periodically the older records will be deleted from the table, because we
don't need to keep them indefinitely.

In other words, following a quick read of my original post the data appears
to be redundant and in violation of database normalization rules, but it's
not. Furthermore, the entire database is going to be rather small and used by
people for whom it must be kept simple.

So please tell me, how can I have it both ways?

Thanks!
GwenH

What Douglas recommends is the correct thing to do, but I thought I'd follow
up with a bit of the why and how.

The why is that the Control Source property of a control on a form can be
used as you are doing it. It is considered an Ubound control because it is
not bound to a field in the form's record source. You can't, however, do it
both ways. There are some tricks to making it appear you are having it both
ways, but that is another subject.

The how is that if you do as Douglas recommends, you are following good
database normalization rules against store redundant data. So, the trick is
any time you want to display the borrower's name, use the same technique you
are using to display the name on the form.

I have a form named "enterLoanDocs". Its record source is the table named
"loanOpsTracking". On this form, I have a field named "accountNo" whose
control source is the field "accountNo" in the aforementioned table. I also
have a field named "borrower" whose control source is:

=DLookUp("[name]","openLoans"," [loanNumber] = " &
Forms!enterLoanDocs!accountNo)

When I enter a value in the accountNo field, the form looks up the
borrower's name in the table "openLoans." However, when I finish filling out
the form, the values in all the fields except "borrower" are entered in the
loanOpsTracking table.

How do I tell the form to take the value in the borrower field and shove it
into the borrower field of the loanOpsTracking table?

Thanks,
GwenH

 

[Guest]

This post only indicates your database design is more incorrect then we
originally thought. You really only need to do it that way if you want to
maintain an incorrectly structured database.

The main problem is you are confusing how data is stored with how it is
presented to the user. These are two separate issues with multipe books
written on both.

Since you have a source for the borrow's name, you don't need to add it to
another table. You can get it from where you are getting it now. I will
explain later how to structure a query for your form that will take care of
the problem.

What your database structure should consist of is two tables. One for the
account number demographics and a child table for the notes.

tblAccount
ACCOUNT_NUMBER - Primary key
DATE_RECEIVED - Date/Time

tblNotes
ACCOUNT_NUMBER - Foreign key to tblAccount
NOTE_DATE - Date/Time - Date of the Note
NOTE_TEXT - Memo

Since you will want the borrower's name on the form for the user's
convenience, you retrieve it using the loan number.
Your form should be a standard Form/Subform model using a query like this as
the record source:
SELECT tblAccount.ACCOUNT_NUMBER, tblAccount.DATE_RECEIVED, openloans.[name]
FROM tblAccount LEFT JOIN "openloans" ON tblAccount.ACCOUNT_NUMBER =
openloans.loanNumber

Your subform record source should be tblNotes or a query based on it.

This is the correct way to do what you are trying to accomplish. However,
if you wish to continue using improper database design techniques, what you
need to do is add a field for the borrower's name to your table, change the
Dlookup you have as the control source and make it the default value, and
bind the control to the borrower's name field.

Please understand, Gwen, this is only an effort to help. The only reason
you have to do what you are doing is because you have not yet learned the
correct way. Database design and Application design are very complex. It
just takes a lot of practice and experience.

Good Luck.

Here's why it has to be done the way I'm doing it. The account number is a
loan account number. The data that gets stored in the form's record source
consists of a loan account number, a note number, the borrower's name, and
date received. The form's underlying table is simply a log of documents
received. So yes, an account number may be stored in the underlying table
multiple times, but each time it will have a unique note number. The bank I
work for has thousands of open loan account numbers, but only about 300 of
them will be stored in the table in question at any given time, and multiple
occurrences of the same account number will have different note numbers.
Periodically the older records will be deleted from the table, because we
don't need to keep them indefinitely.

In other words, following a quick read of my original post the data appears
to be redundant and in violation of database normalization rules, but it's
not. Furthermore, the entire database is going to be rather small and used by
people for whom it must be kept simple.

So please tell me, how can I have it both ways?

Thanks!
GwenH

What Douglas recommends is the correct thing to do, but I thought I'd follow
up with a bit of the why and how.

The why is that the Control Source property of a control on a form can be
used as you are doing it. It is considered an Ubound control because it is
not bound to a field in the form's record source. You can't, however, do it
both ways. There are some tricks to making it appear you are having it both
ways, but that is another subject.

The how is that if you do as Douglas recommends, you are following good
database normalization rules against store redundant data. So, the trick is
any time you want to display the borrower's name, use the same technique you
are using to display the name on the form.

I have a form named "enterLoanDocs". Its record source is the table named
"loanOpsTracking". On this form, I have a field named "accountNo" whose
control source is the field "accountNo" in the aforementioned table. I also
have a field named "borrower" whose control source is:

=DLookUp("[name]","openLoans"," [loanNumber] = " &
Forms!enterLoanDocs!accountNo)

When I enter a value in the accountNo field, the form looks up the
borrower's name in the table "openLoans." However, when I finish filling out
the form, the values in all the fields except "borrower" are entered in the
loanOpsTracking table.

How do I tell the form to take the value in the borrower field and shove it
into the borrower field of the loanOpsTracking table?

Thanks,
GwenH

 

[Guest]

You are misunderstanding what fields I have in the underlying table. Here's a
complete list:

accountNo (the loan account number)
noteNo (the number that will make each record in the table a unique record)
borrower
dateReceived (the date the insurance document for the loan was received -
not the date of the note or the date the loan documents were received).

To keep it simple for the end user, all four fields must be in the same
table, because they will sometimes need to go into the table to lookup a
specific record. Furthermore, I am prevented from "doing it the correct way"
because of the way I must acquire the loan account numbers and borrower
names. To get that data into the Access database, and keep it updated, I have
to import it periodically from a Business Objects report that lists the loan
account number and the borrower's name (no note numbers, dates, or anything
else are listed on the report). I will have to set this process up so it is
extremely simple for the end user. The end users I am working with on this
particular database require absolute simplicity.

Please understand that not every "incorrect" database design you see posts
about on this board are not always "incorrect" because the creator is
ignorant. Sometimes it is due to the limitations imposed by the nature and
sources of the information we have to work with, as well as the computer
literacy of our end users.

GwenH

This post only indicates your database design is more incorrect then we
originally thought. You really only need to do it that way if you want to
maintain an incorrectly structured database.

The main problem is you are confusing how data is stored with how it is
presented to the user. These are two separate issues with multipe books
written on both.

Since you have a source for the borrow's name, you don't need to add it to
another table. You can get it from where you are getting it now. I will
explain later how to structure a query for your form that will take care of
the problem.

What your database structure should consist of is two tables. One for the
account number demographics and a child table for the notes.

tblAccount
ACCOUNT_NUMBER - Primary key
DATE_RECEIVED - Date/Time

tblNotes
ACCOUNT_NUMBER - Foreign key to tblAccount
NOTE_DATE - Date/Time - Date of the Note
NOTE_TEXT - Memo

Since you will want the borrower's name on the form for the user's
convenience, you retrieve it using the loan number.
Your form should be a standard Form/Subform model using a query like this as
the record source:
SELECT tblAccount.ACCOUNT_NUMBER, tblAccount.DATE_RECEIVED, openloans.[name]
FROM tblAccount LEFT JOIN "openloans" ON tblAccount.ACCOUNT_NUMBER =
openloans.loanNumber

Your subform record source should be tblNotes or a query based on it.

This is the correct way to do what you are trying to accomplish. However,
if you wish to continue using improper database design techniques, what you
need to do is add a field for the borrower's name to your table, change the
Dlookup you have as the control source and make it the default value, and
bind the control to the borrower's name field.

Please understand, Gwen, this is only an effort to help. The only reason
you have to do what you are doing is because you have not yet learned the
correct way. Database design and Application design are very complex. It
just takes a lot of practice and experience.

Good Luck.

Here's why it has to be done the way I'm doing it. The account number is a
loan account number. The data that gets stored in the form's record source
consists of a loan account number, a note number, the borrower's name, and
date received. The form's underlying table is simply a log of documents
received. So yes, an account number may be stored in the underlying table
multiple times, but each time it will have a unique note number. The bank I
work for has thousands of open loan account numbers, but only about 300 of
them will be stored in the table in question at any given time, and multiple
occurrences of the same account number will have different note numbers.
Periodically the older records will be deleted from the table, because we
don't need to keep them indefinitely.

In other words, following a quick read of my original post the data appears
to be redundant and in violation of database normalization rules, but it's
not. Furthermore, the entire database is going to be rather small and used by
people for whom it must be kept simple.

So please tell me, how can I have it both ways?

Thanks!
GwenH

What Douglas recommends is the correct thing to do, but I thought I'd follow
up with a bit of the why and how.

The why is that the Control Source property of a control on a form can be
used as you are doing it. It is considered an Ubound control because it is
not bound to a field in the form's record source. You can't, however, do it
both ways. There are some tricks to making it appear you are having it both
ways, but that is another subject.

The how is that if you do as Douglas recommends, you are following good
database normalization rules against store redundant data. So, the trick is
any time you want to display the borrower's name, use the same technique you
are using to display the name on the form.

:

I have a form named "enterLoanDocs". Its record source is the table named
"loanOpsTracking". On this form, I have a field named "accountNo" whose
control source is the field "accountNo" in the aforementioned table. I also
have a field named "borrower" whose control source is:

=DLookUp("[name]","openLoans"," [loanNumber] = " &
Forms!enterLoanDocs!accountNo)

When I enter a value in the accountNo field, the form looks up the
borrower's name in the table "openLoans." However, when I finish filling out
the form, the values in all the fields except "borrower" are entered in the
loanOpsTracking table.

How do I tell the form to take the value in the borrower field and shove it
into the borrower field of the loanOpsTracking table?

Thanks,
GwenH

 

[Guest]

I suggest you do what you want to do. In my post I gave you the answer to
how to accomplish it.
I never said you are ignorant, I said inexperienced. There is a world of
difference.

You are misunderstanding what fields I have in the underlying table. Here's a
complete list:

accountNo (the loan account number)
noteNo (the number that will make each record in the table a unique record)
borrower
dateReceived (the date the insurance document for the loan was received -
not the date of the note or the date the loan documents were received).

To keep it simple for the end user, all four fields must be in the same
table, because they will sometimes need to go into the table to lookup a
specific record. Furthermore, I am prevented from "doing it the correct way"
because of the way I must acquire the loan account numbers and borrower
names. To get that data into the Access database, and keep it updated, I have
to import it periodically from a Business Objects report that lists the loan
account number and the borrower's name (no note numbers, dates, or anything
else are listed on the report). I will have to set this process up so it is
extremely simple for the end user. The end users I am working with on this
particular database require absolute simplicity.

Please understand that not every "incorrect" database design you see posts
about on this board are not always "incorrect" because the creator is
ignorant. Sometimes it is due to the limitations imposed by the nature and
sources of the information we have to work with, as well as the computer
literacy of our end users.

GwenH

This post only indicates your database design is more incorrect then we
originally thought. You really only need to do it that way if you want to
maintain an incorrectly structured database.

The main problem is you are confusing how data is stored with how it is
presented to the user. These are two separate issues with multipe books
written on both.

Since you have a source for the borrow's name, you don't need to add it to
another table. You can get it from where you are getting it now. I will
explain later how to structure a query for your form that will take care of
the problem.

What your database structure should consist of is two tables. One for the
account number demographics and a child table for the notes.

tblAccount
ACCOUNT_NUMBER - Primary key
DATE_RECEIVED - Date/Time

tblNotes
ACCOUNT_NUMBER - Foreign key to tblAccount
NOTE_DATE - Date/Time - Date of the Note
NOTE_TEXT - Memo

Since you will want the borrower's name on the form for the user's
convenience, you retrieve it using the loan number.
Your form should be a standard Form/Subform model using a query like this as
the record source:
SELECT tblAccount.ACCOUNT_NUMBER, tblAccount.DATE_RECEIVED, openloans.[name]
FROM tblAccount LEFT JOIN "openloans" ON tblAccount.ACCOUNT_NUMBER =
openloans.loanNumber

Your subform record source should be tblNotes or a query based on it.

This is the correct way to do what you are trying to accomplish. However,
if you wish to continue using improper database design techniques, what you
need to do is add a field for the borrower's name to your table, change the
Dlookup you have as the control source and make it the default value, and
bind the control to the borrower's name field.

Please understand, Gwen, this is only an effort to help. The only reason
you have to do what you are doing is because you have not yet learned the
correct way. Database design and Application design are very complex. It
just takes a lot of practice and experience.

Good Luck.

Here's why it has to be done the way I'm doing it. The account number is a
loan account number. The data that gets stored in the form's record source
consists of a loan account number, a note number, the borrower's name, and
date received. The form's underlying table is simply a log of documents
received. So yes, an account number may be stored in the underlying table
multiple times, but each time it will have a unique note number. The bank I
work for has thousands of open loan account numbers, but only about 300 of
them will be stored in the table in question at any given time, and multiple
occurrences of the same account number will have different note numbers.
Periodically the older records will be deleted from the table, because we
don't need to keep them indefinitely.

In other words, following a quick read of my original post the data appears
to be redundant and in violation of database normalization rules, but it's
not. Furthermore, the entire database is going to be rather small and used by
people for whom it must be kept simple.

So please tell me, how can I have it both ways?

Thanks!
GwenH

:

What Douglas recommends is the correct thing to do, but I thought I'd follow
up with a bit of the why and how.

The why is that the Control Source property of a control on a form can be
used as you are doing it. It is considered an Ubound control because it is
not bound to a field in the form's record source. You can't, however, do it
both ways. There are some tricks to making it appear you are having it both
ways, but that is another subject.

The how is that if you do as Douglas recommends, you are following good
database normalization rules against store redundant data. So, the trick is
any time you want to display the borrower's name, use the same technique you
are using to display the name on the form.

:

I have a form named "enterLoanDocs". Its record source is the table named
"loanOpsTracking". On this form, I have a field named "accountNo" whose
control source is the field "accountNo" in the aforementioned table. I also
have a field named "borrower" whose control source is:

=DLookUp("[name]","openLoans"," [loanNumber] = " &
Forms!enterLoanDocs!accountNo)

When I enter a value in the accountNo field, the form looks up the
borrower's name in the table "openLoans." However, when I finish filling out
the form, the values in all the fields except "borrower" are entered in the
loanOpsTracking table.

How do I tell the form to take the value in the borrower field and shove it
into the borrower field of the loanOpsTracking table?

Thanks,
GwenH