Not In List

  • Thread starter Thread starter ned
  • Start date Start date
N

ned

There are two pertinent fields to my question: Revenue
Code (REVCODE) and a related description (REVDESC). The
REVDESC may be customized so it needs to be stored and
displayed separately than the REVCODE on a record-by-
record basis. However, I want to store the list of
revenue codes and the default description in a separate
table: tblRevCode. As a user is data entering REVCODE for
a record, once they enter the REVCODE, I want the default
REVDESC to automatically populate the REVDESC field for
that record. In addition, if the REVCODE the user enters
is not currently in tblRevCode, then I want them to be
able to insert it anyway, with a description and have that
automatically append to tblRevCode. Any direction on
either of these two matters would be greatly appreciated.
TIA,
Ned
 
Hello.

You will need to base the form on a query similar to:

SELECT tblRevDesc.*, tblOtherData.* FROM tblOtherData LEFT
JOIN tblRevDesc ON tblOtherData.REVCODE=tblRevDesc.REVCODE

(In query builder: Add the two tables and drag the field
REVCODE from one table to the other. Doubleclick
the "Link" and change type to include all from
tblOtherData and matching records from tblRevDesc. Then
doubleclick the asterix (*) in both tables).

On your form, you can now add the two fields
[tblOtherData.REVCODE] and [REVDESC].

When you enter a REVCODE, then Access will try and follow
the JOIN between the tables, looking up REVCODE and
REVDESC in tblRevDesc.

When entering an unknown REVCODE, Access stores the code
in tblOtherData. There is no matching record in
tblRevDesc, leaving the REVDESC field blank. When entering
data into this field, Access will create a new record in
tblRevDesc.
 
I created the query:

SELECT tblOtherData.*, tblRev.*
FROM tblOtherData LEFT JOIN tblRev ON tblOtherData.RevCode
= tblRev.RevCode;

(obviously having the correct necessary tables and fields
in place). I then based the form on this query.

However, the query is Read-Only, and therefore, I could
not data enter a RevCode in the form.

I was thinking this would be done using VB. (Also, FYI, I
am using Access 2000). I would have thought I would be
using the NotInList event, but I am not familiar with this
command and there is very little help on this in Access.
Either that or an AfterUpdate event that searches the
record set for the matching RevCode, pulls the
corresponding RevDesc and throws it in my
tblOther.RevDesc. I just don't know the code to do
something like this.

Any ideas?

Thank you for your response!!



-----Original Message-----
Hello.

You will need to base the form on a query similar to:

SELECT tblRevDesc.*, tblOtherData.* FROM tblOtherData LEFT
JOIN tblRevDesc ON tblOtherData.REVCODE=tblRevDesc.REVCODE

(In query builder: Add the two tables and drag the field
REVCODE from one table to the other. Doubleclick
the "Link" and change type to include all from
tblOtherData and matching records from tblRevDesc. Then
doubleclick the asterix (*) in both tables).

On your form, you can now add the two fields
[tblOtherData.REVCODE] and [REVDESC].

When you enter a REVCODE, then Access will try and follow
the JOIN between the tables, looking up REVCODE and
REVDESC in tblRevDesc.

When entering an unknown REVCODE, Access stores the code
in tblOtherData. There is no matching record in
tblRevDesc, leaving the REVDESC field blank. When entering
data into this field, Access will create a new record in
tblRevDesc.



-----Original Message-----
There are two pertinent fields to my question: Revenue
Code (REVCODE) and a related description (REVDESC). The
REVDESC may be customized so it needs to be stored and
displayed separately than the REVCODE on a record-by-
record basis. However, I want to store the list of
revenue codes and the default description in a separate
table: tblRevCode. As a user is data entering REVCODE for
a record, once they enter the REVCODE, I want the default
REVDESC to automatically populate the REVDESC field for
that record. In addition, if the REVCODE the user enters
is not currently in tblRevCode, then I want them to be
able to insert it anyway, with a description and have that
automatically append to tblRevCode. Any direction on
either of these two matters would be greatly appreciated.
TIA,
Ned
.
Click to expand...
.
Click to expand...
 
Back
Top