473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Automatically fill fields

48
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
Aug 2 '07 #1
18 2114
damonreid
114 Expert 100+
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.
Aug 2 '07 #2
alive84
48
thanks for the answer.

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

I have tried

Expand|Select|Wrap|Line Numbers
  1. SELECT CostCenter.CostCenter, CostCenter.OrgUnit
  2. FROM CostCenter
  3. WHERE (((CostCenter.OrgUnit)=[Where CostCenter]));
but it never worked:

Best regards and thanks,

Alive
Aug 2 '07 #3
damonreid
114 Expert 100+
Expand|Select|Wrap|Line Numbers
  1. SELECT TableOne.[Key], TableOne.[OtherFields], [TableTwo].KeyName, [TableTwo].[FieldName], [TableTwo].[FieldName] FROM [TableTwo] RIGHT JOIN TableOne ON [TableTwo].ID = KeyOne.KeyTwo;
Aug 2 '07 #4
alive84
48
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.
Aug 2 '07 #5
damonreid
114 Expert 100+
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]
Aug 2 '07 #6
alive84
48
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 2 '07 #7
damonreid
114 Expert 100+
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?
Aug 2 '07 #8
alive84
48
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?
Aug 2 '07 #9
damonreid
114 Expert 100+
PM them through and I will have a look
Aug 2 '07 #10
NeoPa
32,556 Expert Mod 16PB
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 ;)
Aug 2 '07 #11
alive84
48
@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:

Expand|Select|Wrap|Line Numbers
  1. SELECT Work.CostCenter, Work.OrgUnit, Work.Region, CostCenter.CostCenter, CostCenter.OrgUnit, CostCenter.Region
  2. 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
Aug 2 '07 #12
NeoPa
32,556 Expert Mod 16PB
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.
Aug 2 '07 #13
alive84
48
Hi NeoPa,

Ok, I will try to visualize it.

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

Expand|Select|Wrap|Line Numbers
  1. SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
  2. 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
Aug 3 '07 #14
NeoPa
32,556 Expert Mod 16PB
That's because the ON part of the FROM clause should be saying :
Expand|Select|Wrap|Line Numbers
  1. [CostCenter].CostCenterID=[Work].CCFieldName
(where CCFieldnName is the name of the CostCentre field within your [Work] table) instead of
Expand|Select|Wrap|Line Numbers
  1. [CostCenter].CostCenterID =CostCenter.Work
Aug 3 '07 #15
NeoPa
32,556 Expert Mod 16PB
So, assuming that your [Work] table has the field called [CostCenterID], your SQL would be :
Expand|Select|Wrap|Line Numbers
  1. SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit, Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
  2. FROM CostCenter INNER JOIN [Work] ON CostCenter.CostCenterID=Work.CostCenterID
unless of course, CostCenter.[OrgUnit, Region] is a typo. In which case try :
Expand|Select|Wrap|Line Numbers
  1. SELECT CostCenter.[CostCenterID], CostCenter.[OrgUnit], CostCenter.[Region], [Work].WorkID, [Work].[OrgUnit], [Work].[Region]
  2. FROM CostCenter INNER JOIN [Work] ON CostCenter.CostCenterID=Work.CostCenterID
Aug 3 '07 #16
alive84
48
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.
Aug 6 '07 #17
NeoPa
32,556 Expert Mod 16PB
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.
Aug 6 '07 #18
alive84
48
ok....I have a form and a table Work.

sorry for the confusion.
Aug 7 '07 #19

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: paul b | last post by:
Hello, I have a small problem in PHP an perhaps someone can help me: I have a simple HTML login page with a username and a password field, as well as a submit button, which I have to use for...
8
by: netsurfer | last post by:
Hi: Have a question on making the date automatically filled in by what the user enters in by the date at the top. The date entered at the top would most likely be on a Wednesday then I need...
1
by: Charles Robinson III | last post by:
When selecting a option from a drop down menu in the form view, which is linked to a table with three columns, how do I get Access to automatically fill in two other fields automatically once the...
0
by: Aravind | last post by:
Hi folks. I have the following tables (PK = primary key, FK = foreign key): Member (MemNo , MemName, MemType, Course/Faculty) History (..., MemNo , ...) Member and History are linked in a 1...
7
by: jballard | last post by:
Hello, I have a database set-up with a form and two subforms in it. I have one of the subforms (replacement parts) set-up where you can pick part numbers from a drop down box and also pick a...
2
by: DP | last post by:
hi, i have created a video rental database system. i have created a customer form, and a film form. i related all the tables, with; tblCustomer has membershipID, which is primary and...
1
by: moorebj | last post by:
I am having trouble writing a SELECT query. I have a "Customer" table with "Customer Name", "Street Address" and "Suburb, State, Post Code" fields. On a form I would like to be able to enter in...
4
by: JA | last post by:
Not exactly sure how to explain this. I have 4 fields (in access 2000). Category, Catid, Subcat, subcatid. There are 40 categories, and about 1500 subcats. I've added the categories to the...
1
by: cclayton000 | last post by:
Does anyone have a basic example of a script that can automatically fill fields in a subform? Here is the scenario: I have a main form for a Sample Lot and I have added some side-fields (not...
1
by: pctec | last post by:
Hello, I have a combo box that is looking into a field from a table named Machines, this machine table has two columns; MachineName and MachineRate but the Combo Box it only showing the MachineName...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.