HELP Please - Viewing data from one form to another MS Access

[wsh]

I need some help please. I need to show data as disabled but visible
on 1 of my forms that is linked up to another table. So I'm trying to
pull data into the form that lives in a table not linked to the form
for the form is already linked up to a different table.

For example: I have 2 forms and 2 tables. Form A is linked to table
A. Form B is linked to Table B. The cost field is from the
componentcost table and linked to on Form b, but I want it also to show

up on Form A but form A is linked to the inventory table. I only need
it to show up on Form A for viewing purposes only, but I don't know how

to get that information to show up on Form A. I've tried a subform that

doesn't work. I've tried code. This code sorta works but only takes
the first number value in the table and its the same value on each
record. Its not changing when I scroll from one record to the next in
sync with the records on Form B. I hope I"m making sense - any help
would be so very much appreciated! Wendy


Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Components.Cost FROM Inventory Right
JOIN Components ON Inventory.Component = Components.ID;")
txtComponentCost = rs!Cost
txtComponentCost.Requery

 

[Ken Sheridan]

Add a text box to Form A with a ControlSource property of:

=DLookup("Cost", "Components", "ID = " & Nz([Component]),0)

This assumes ID and Component are a number data type, not text. If its the
latter use:

=DLookup("Cost", "Components", "ID = """ & [Component] & """")

Ken Sheridan
Stafford, England

 

[wsh]

Hi Ken,

THank you for helping me. I tried both methods. The first method
brings back #Name? and the second method does nothing? Any clue what
I'm doing wrong? Thank you :-(

 

[Ken Sheridan]

From the SQL you posted you have tables Components and Inventory where the
Component column in Inventory references the ID column in Inventory and the
Cost column is in the Components table. You said that Form A is based on the
Inventory table, so the Component column should be in its underlying
recordset. This being the case I don't see why one or other of the
expressions should not work, depending on the data type of the key columns,
number or text.

What it should do is look up the value of the Cost column in the row of
Components whose ID column's value matches the value of the Component column
in the form's current record, which I assume to be what's wanted.

Another way you could do it is to base the form on a query which joins the
Inventory and Components tables and return the Cost column in the query.
Bind a text box control on the form to this column and set the control's
Enabled property to False and its Locked property to True. This prevents the
user doing anything with the control, even moving focus to it.

Ken Sheridan
Stafford, England

 

[wsh]

Ah ok this may be the problem then. Inventory is a stand alone table,
but does have component and product id numbers in them as foreign keys,
however the main tables are: comoponents which has a primary key,
linked to productcomponents as the linking/intersection table with only
2 number fields with component id and product id and then the products
table with the main prodid. Ok so now this may be why Im having
trouble. I like your idea of basing the form on a query. I never ever
gave that a consideration. If I did this will the query automatically
update records to the table or will it need to be an append query which
points the the components table????

 

[Ken Sheridan]

The query should be updatable as far as the columns from the Inventory table
are concerned. I still can't figure why the DLookup function calls don't
work, though??.

Signing off for today, so if you get back to me again I'll pick it up
tomorrow.

Ken Sheridan
Stafford, England

 

[wsh]

if you send me your email I"m happy to zip you up the db so you can
see......my email is

 

[Ken Sheridan]

Send it to:

ken<dot>sheridan<at>dsl<dot>pipex<dot>com

I can't promise to spend much time on it, but hopefully I'll be able to sort
the form out for you.

Ken Sheridan
Stafford, England