I need a script to lookup information table. In a Data Access Page

[Gregh-apg]

Need help.

I need a script to pull information out of a table. I will have the
record number I need to select the record. I need to pull the
information out and assign it to a text box. I will access it with
a command1 Button onclick event.

Example:

Table: Invoice
InvoiceID
CustomerID
CustomerName
Other....

Table: Customer
CustomerID
CustomerName
Contact
Phone
Address
other....


The data access page will have the InvoiceID,CustomerID,CustomerName
I want to pull the CustomerName from the customer table and put it
CustomerName
in the form.

Note: I don't want to use a dropdown List box

Please can someone help

 

[Guest]

hi,
if you want to pull info out of a table and pop it in a
text box on a form then you don't need a button.
see my answer to Rufino Palacol in access new users posted
at 11:59pm on jan20. his question was How to lookup?

 

[Tim Ferguson]

I will have the
record number I need to select the record.

Remember that record numbers do not mean much in a relational context:
record order moves around quite at random. This is what PKs are for, to
identify individual rows.

I need to pull the
information out and assign it to a text box.

Easiest way is usually with a DLookup() function.

Table: Invoice
InvoiceID
CustomerID
CustomerName

Hold on there: what is CustomerName doing as an attribute of an Invoice?
It should be in the Customers table, not here.

Table: Customer
CustomerID
CustomerName
Contact
Phone
Address
other....

Yes, exactly. Don't bother trying to duplicate them because they _will_
get out of synch.

The data access page will have the InvoiceID,CustomerID,CustomerName

The other way to get the CustomerName is to join the tables on the
CustomerID fields, and then just plonk Customer.CustomerName in a control
on your form^W page. Probably even faster than the DLookup.

If you want to be able to change the CustomerID from the dap, then you'll
need to include the foreign key Invoices.CustomerID in the query (and
some reasonably user-friendly method of choosing another one).

If you want to be able to edit the customer records from the dap, then
you'll need to include the primary key Customer.CustomerID in the query.

There you are: no programming or scripts required!

Hope it helps


Tim F