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=000 11, Region=US, OrgUnit=Archite ctur)

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

[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.Wor k .

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.