Valadation Rule in Table 1

[assets]

I first ask this in a different area and it was the wrong place I think. ones that affect what I require are is addresses and divison ( I also have a query based on divison to give me DivisonID, Divison and Costcentre)

There is a field in Table Addresses called divisionID that has a lookup SELECT Division.DivisionID, Division.Division, Division.Active, Division.CostCentre FROM Division WHERE (((Division.Active)=-1)); This displays the divison correctly.

What I like to do is use the Costcentre field infomation from Division (or the query) to polulate a field in Addresses with the costcente info that is in division where the DivisonID in both table are the same.



Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

Assets,
I would first get rid of the evil lookup fields.

Then, why would you want to store the costcentre field in Addresses since it is dependent on the Division and can always be looked up in the Division table? You could display the value in forms or reports using any one of several different methods.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

SELECT Division.DivisionID, Division.Division, FROM Division

Displays the division. But I need to costcentre that corrsponds to that division to display in a seperate field in assets. To display on the main form show user information.

The concept is to have the information is to later when I work out how is to populate a form that can be sent for computer access and building pass access that is automatically generated one you add a new user.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

I asked about storing the costcentre. Can you answer why you need to store the costcentre when it is dependent on the Division? You should always be able to display the costcentre in any query that contains the DivisionID.


Duane
Hook'D on Access
MS Access MVP

 

[assets]

Duane thanks. What I trying to achieve is to show the correct costcentre in a seperate text box on the form. Later on I trying also to generate a form that contains all information that will be emailed to the correct area requesting computer access and building access. But I need the correct cost centre to show on the formto start with.

Thank

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

You can "show" the costcentre value in a form by either including the Division table in your form's record source or by using a combo box in the form with the Division table as the Row Source. The Row Source would need to include the CostCentre column so you could display it in the form.

Again, as long as you have the divisionID in a record source, you can display/show the costcentre. That's how relational databases work.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Thanks I used a Query and Show to do a work around to show costcentre.

A question (well you opinion) am I better off using Word or Excel if I want access to produce forms that have tables.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

tables" are in Access so I'm not sure what requirements would suggest using Word or Excel.

Duane
Hook'D on Access
MS Access MVP