A simple table qquestion

[Akshay]

I have databse that has two tables

Table 1: "Funding"

[ID]-Autonumber-
[Funding Type]-Number- Combo Box(Look up in table "Funding Type" by
ID)
[Amount]- Number- Manual feed
[Alias]- a five digit number
[Account]- a six digit number

Table 2: "Funding Type"

[ID]-Autonumber
[Funding Type]-Text- i has about 87 entries and thats al of them
[Alias]- a five digit number- manually fed in this table
[Account]- a six digit number- manually fed in this table

NOw each "Funding Type" in tabel 2 (Funding Type) has a alias and
account assgined to it.. and I want that whenever i am entering data
in table 1 and choose a funding type from the drop down..the
coresponding alias and account number should show up in the respective
columns automatically in table 1. These account and alias numbers have
already been entered in table 2 (Funding Type)

Do i have to write an expresion... please advise.. new to access

 

[Jeff Boyce]

From your description, it sounds like you are working directly in the
tables... and have used the "lookup" datatype for Funding Type in your
Funding table.

Tables work great for storing data, but forms are design specifically to
enter and edit data. And they have a rich "event" set you can use to add
validation and procedures.

The lookup datatype causes considerable confusion, as it stores the key
value, but displays the looked-up value. The tablesdbdesign newsgroup
covers this issue frequently -- and the consensus is to convert that field
to a long int type (if your lookup table uses an Autonumber key field), and
use a form and combo box in the form for doing the lookup.

 

[Akshay]

Jeff.. thnaks .. I do use a form to enter data into the tabel 1 "
Funding"... how do i set the properties in the form for the " Alias"
and " Account" control so when i choose the "funding type" in the
drop down in the form.. the corresponding account and alias are
entered into the table "Funding" automatically i dont have to key
them in every time a new funding takes place..... just to remind
you.. the account and alias are matched to each funding type and are
already entered in the "Funding Type" table (table 2) with the
corresponding funding types

 

[Jeff Boyce]

Akshay

If I understand your question, you don't need to enter the duplicate
information (your "account" and "alias"). You only need to enter the key of
the lookup table(s). Use a query to join the two tables, and return the
values in the lookup table.

If you are saying you'd like to see the additional information from the
lookup table on the form you are using for your main table, you can display
that without storing it. Look into the .Column() property of the combobox.

The general approach would be to add unbound text controls on your form, and
use the AfterUpdate event of your combobox to put values in those unbound
controls, using something like:

Me!txtFirstUnboundControl = Me!cboYourComboBox.Column(n)

where the "n" is one less than the column count in which the field/value is
returned by your combobox's query. (you really do need to look into the
syntax on this one!).

 

[Akshay]

Jeff, I dont think i completely understand wht im supposed to do
here....I tried doing what you told me but its not working.... I wish
i could show you the databse somehow...I don't even know what kind of
information you need or whether I have explained the problem
properly....to put it simply...I have two tables with three common
fields...data in all these fields is entered in Table 2 and have one
to many relationship to table 1(The table in which I enter day-to-day
data) where I have look ups for all three to table 2 by ID (and thus
the dropdowns)... (By the way I use a form to enter data into table 1
that have field objects with the same expressions for lookups and thus
the same dropdowns boxes) . I want that when i choose from drop down
of field number one in my form the other two fields should fill
themselves automatically without having me to fill them manually.....
now you can ask me anything about the table I can tell you that but ..
i dont know what kind of info you would want to solve this
problem...hope this explains something... i'm doubtful though..


akshay

 

[nassim hamana]

speak you french?