Automatically fill fields

[alive84]

hi there,

I am trying to find a way to link three fields in a form together. For example, if the User chooses CostCenter, then automatically the fields OrgUnit and Region receive a value (CostCenter=00011, Region=US, OrgUnit=Architectur)

I have tried to solve the problem with: [CostCenter] in the fields Region and OrgUnit. Because I have added columns to the CostCenter Table. However, what I am getting is just the CostCenterID number, so doesn't work.

Is the way I am approaching the only possible one? or is there a SQL-WHERE possibility, or even a macro. Does anybody have an idea?

Best Regards,

Alive

[damonreid]

Why not make a new table with the cost centres in it and have the other two fields associated in that table.

Then when you need all 3 fields simply have a query with both tables in it and it will automatically connect the associated fields with the selection.

[New Table]
CostCenter - Region - OrgUnit
00011 - US - Architecture
00012 - IR - Architecture

Once they pick 00011 if you run a query with both tables you will get all the information in one place.

[alive84]

thanks for the answer.

I have already a table CostCenter with columns CostCenterID, CostCenter, OrgUnit, Region.

I have tried

SELECT CostCenter.CostCenter, CostCenter.OrgUnit
FROM CostCenter
WHERE (((CostCenter.OrgUnit)=[Where CostCenter]));

but it never worked:

Best regards and thanks,

Alive

[damonreid]

SELECT TableOne.[Key], TableOne.[OtherFields], [TableTwo].KeyName, [TableTwo].[FieldName], [TableTwo].[FieldName] FROM [TableTwo] RIGHT JOIN TableOne ON [TableTwo].ID = KeyOne.KeyTwo;

[alive84]

thanks for that clear explanation. But what do you mean with tabletwo?

I have everything in one table.

Tablename: CostCenter
PrimaryKey: CostCenterID
Column2: OrgUnit
Column3:Region

thanks for the update.

[damonreid]

You should have two tables for this to work, one with the 3 fields above and another table that "looks up" the key to pull all the information into.

Does that make sense?

So you have one table that you enter the Key into and another table that links that key to the information you want. Your query pulls both tables together to give you the information you want.
[img=http://img247.imageshack.us/img247/6166/queryhw4.th.png]

[alive84]

Unfortunately, I can't view the image (it's a blocked site)

ok, so you basically mean that TableTwo is the Form where I Typ the CostCenter. TableOne is where the CostCenter has it's values, such as OrgUnit and Region.

Now I have tried to write the SELECT as such:

SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region] FROM [Work] RIGHT JOIN CostCenter ON [CostCenter].CostCenterID =CostCenter.Work;

However, Access gives me the error message: Join expression not supported

I am sorry that I am asking so many questions.

Thanks

[damonreid]

That is no problem, if you don't ask you don't get answers (no matter how mangled...)
The basic idea is you have one table that stores your 3 fields. Another table then uses a lookup wizard on one field to link to your table that stores the 3 fields.
Now you can make a query with both tables. If you set it up so that all the values from the second table are taken and only the values in the first table where there are corresponding values in the second table (ie a right join) then you should be able to bring all the fields you want together on a form or report by looking at this one query in the place of two tables...

Does that help?

[alive84]

thanks for your help and patience.

I see, but as soon as I am trying something like that and create the query and put it into the ControlSource, I receive an error message from access ?Name. Although, I haven't misspelled anything.

Can I send you some screenshot?

[damonreid]

PM them through and I will have a look

[NeoPa]

When discussing queries it's best (certainly easiest) to post the underlying SQL. I doubt you'll manage to send screenshots via PM anyway.
BTW Nice work Damon, your new status is in hand ;)

[alive84]

@NeoPa:

Your are certainly right with both matters! :-)

@Damon

I have tried the one you gave me, didn't work. So I have tried to build another one, but this one is kind of messy, but my logic (which must be wrong) tells me this:

SELECT Work.CostCenter, Work.OrgUnit, Work.Region, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region
FROM CostCenter INNER JOIN [Work] ON (CostCenter.Region = Work.Region) AND (CostCenter.OrgUnit = Work.OrgUnit);

But what Access gives me in the Form is ?Name, great error message.

Thanks to you both

[NeoPa]

Alive,
Can you do either :

1. Provide a picture of the error.
2. Describe it with everything that appears - the title; the msg; etc

I think there may be something missing from the message. It doesn't seem to fit the SQL posted.

[alive84]

Hi NeoPa,

Ok, I will try to visualize it.

I create the following query in the SQL-Builder of Access:

SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
FROM [Work] RIGHT JOIN CostCenter ON [CostCenter].CostCenterID =CostCenter.Work;

While I try to save it, I receive the error message: Join Expression Not Supported

So I am changing the RIGHT JOIN, with an INNER JOIN.

Same Error, but Access highlights [CostCenter].CostCenterID =CostCenter.Work .

So, something must be wrong here, but I ve tried to do follow Damon's advices.

So, I am lost...

Thanks

[NeoPa]

That's because the ON part of the FROM clause should be saying :

[CostCenter].CostCenterID=[Work].CCFieldName

(where CCFieldnName is the name of the CostCentre field within your [Work] table) instead of

[CostCenter].CostCenterID =CostCenter.Work

[NeoPa]

So, assuming that your [Work] table has the field called [CostCenterID], your SQL would be :

SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
FROM CostCenter INNER JOIN [Work] ON CostCenter.CostCenterID=Work.CostCenterID

unless of course, CostCenter.[OrgUnit, Region] is a typo. In which case try :

SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit], CostCenter.[Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
FROM CostCenter INNER JOIN [Work] ON CostCenter.CostCenterID=Work.CostCenterID

[alive84]

Thanks for the answer.

I do not know what is wrong with me and my AccessDatabase. When I try it, I still get that stupid and frustrating error message in my form: #?Name.

I finally understand the SQL queries you guys were giving me, but somehow it still wont work at all.

When I am linking the query to the form field, is the following approach right?

1. I type the SQL-Query in the Access SQL-View. Save it
2. Go to my form Work and add the Query to the OrgUnit field --> ... --> Queries --> choose OrgUnit --> hit value --> Ok.
3. Save the Form and switch the view.

That's the right approach, no?

Thanks for the patience and of course the help you all are providing.

[NeoPa]

You've just described your [Work] object as a form rather than as a table. If that is true you should clarify that. We've been working under the delusion that it's a table.

[alive84]

ok....I have a form and a table Work.

sorry for the confusion.