Lookup

  • Thread starter Thread starter San
  • Start date Start date
S

San

I have a table customer with two fields customerid and
customer name.
There is one more table sales with customerid and customer
name in it.
The relationship is one to many ( Customer to sales )

Now this is what I want to be working in table.
As soon as I enter a new record with the customer id in
sales table it should get the customer name from customer
table and fill up the customer name field of sales table.

I know this is not a correct design but is it possible?


Any suggestions will be highly appreciated.
Thanks,
San
..
 
Not to hard.

CustomerID field AfterUpdate Event

txtCustomerName = DLookup("CustomerName", "CustomerTableName", "CustomerID =
'" & Me.CustomerID & " ' ")

Of course you would have to substitute the real field names for what I
wrote, and depending on your field characteristics for CustomerID you may
not need the ' ' - for instance, there is not need to string the field if it
is an integer value everytime.
 
Randy ,
I am looking for a way to do that in table not the form.
Any suggestions?
Thanks,

-----Original Message-----
Not to hard.

CustomerID field AfterUpdate Event

txtCustomerName = DLookup
Click to expand...
("CustomerName", "CustomerTableName", "CustomerID =
 
Hi San!

I´m just qurious to know! Why in earth do you want to do that?
You know that it´s a strange tabledesign, you wrote that, so why do you do
it?

The way I see it you should not need to have customer name in tblSales since
you already have CustomerID identifying the customer from tblCustomer,
right!?

But to answer your Q. I´m not the right guy to tell you the exact syntax for
doing this but it should be able to do (sorry for that).

In a standard code module you should be able to write code to check if
tblSales are/or have been apdated. If so sort the table finding the new
record geting customer name from tblCustomer where tblCustomer.CustomerID =
tblSales.CustomerID.

It have to be possible but I don´t know the exakt syntax, sorry!

// Niklas
 
From within an Access table - I don't know.

If you were using SQL Server you could easily do this with a Trigger ... but
in Access ... code is the easiest way.
 
I am looking for a way to do that in table not the form.
Click to expand...

Table datasheets have VERY limited capabilities, and should not
generally be used for anything except debugging and development. You
can make a continuous Form look very much like a datasheet, if that's
an appearance that you like; but on a Form you get all sorts of
programmable events, complete control over the appearance, and many
other advantages.

Not using Forms with a database is a bit like not using a transmission
with an automobile. Sure, you can drive in first gear all the time if
you want... but... <g>
 
It's a no can do really
And replicating the customer name is not a good idea in a relational DB (as
names seldom change - use join queries for reporting instead)
putting the current price of an item in a sales line will make sense
though...
you can only do that in an access "table" using a datasheet form, or else
you need to use a Back-end database which facilitate table-level triggers
(Oracle, DB2 or SQL Server for instance)

--
Pieter Wijnen

When all else fail try:
http://www.mvps.org/access
http://www.granite.ab.ca
 
Back
Top