stupid question

[krillin]

I have two tables

table1:
ProjectCode
ProjectName

table2:
ProjectName

i have created a form for table 2 and i want to show depending on the selected ProjectName a tekst wich shows the ProjectCode from table 1, how to do this.

Tnx in advance

 

[Ally]

I'm a little confused as to exactly what you want but I'll give it a bash.

If I understand you correctly, for a start you would need an ID field in the second table that links the project name to the project code. Is the Project Code a primary key?

What you could then do is make a combo box on a form that looks up the fields in your second table which would automatically show the ID field.

Sorry if I've misunderstood you completely.

 

[krillin]

Not completely

I have a combo box on my form in wich you can select the ProjectName derived from table 1, this value will be stored in ProjectName of table 2, this means that both tables has the same ProjectNames. In table 1 the ProjectCode is the primaire key, however i want to show a textbox wich shows the ProjectCode from table 1 depending from the selection of the combobox

i hope that this makes some things a little clearer

 

[Ally]

Okay - talked with Col my colleague and we think this might be what you need.

On After_Update of your combo box enter the VBA code:
Me.TxtBoxName.Value = Me.ProjectName.Column(0)

 

[directormac]

But why?

My question is WHY you want to store the same piece of data twice?

--Puzzled Mac

 

[krillin]

tnx

First of all i want to thank everybody for their fast reply's.

The reason that i'm using the same names is that on table2 the name can appear many times while on table 1 the name only can appear just once.

The solution to my problem isn't solved yet, sorry about that but it returns an error

I have included the file for you to take a look at and maybe things would be more clearer now.

Table1:
ProjectCode <-- key
ProjectName

Table2
ID
ProjectName

form from Table2:
Combo box wich returns the ProjectNames from Table 1 and writes it into ProjectName Table2
Text box wich should return the Projectcode from Table1 determined by the selection of the combobox

 

[ColinEssex]

Firstly you have referred to the Project Name twice in your combobox. Your fields should be ProjectCode and ProjectName

Secondly your ComboBox is called Combo4 NOT Project Name (in the VB code)

Change those details and it does work


Col

 

[krillin]

almost

almost there,

i have used your suquestion and it works partially. It writes down the ProjectCode inside the box, however it doesn't write down the selected combobox value (ProjectName) into Table2

 

[ColinEssex]

change the bound column in the comboBox to 2

 

[krillin]

TNX

thanks, it works.

great support on this site, just one question left

i understand what i did wrong but i don't understand the function of the bound parameter.

anyway thanks alot

 

[ColinEssex]

the BoundColumn property is stored in the field named in the ControlSource property.

 

[krillin]

Colin

Tnx colin,

again, i hope i'm not boring you. But wat if i have another row in my Table1 called ProjectLeader and i want to show it on the form aswell, with only the selection of the combo box

 

[ColinEssex]

When you say another row - I presume you mean another column.

Add the ProjectLeader column to your table1 and then add that to the ComboBox underlying query. Add the extra bit to your afterUpdate code of the combo to post the new data to the new field.

Bear in mind that when you refer to it - it will be column 2 (the columns in a combo start at 0)

Col

 

[krillin]

tnx colin, it works. I know enough to proceed with my project, i will run into some other problems but i know were to place my questions

greetz
a newbie

 

[directormac]

tnx colin, it works. I know enough to proceed with my project, i will run into some other problems but i know were to place my questions

Krillin:

You can save yourself some of those future problems by NOT putting the CompanyName directly into Table2. The reasons for this are many, including the fact that if you ever need to correct or change the name of a company, you're going to have to go through every record on Table2 and change the value in that field.

What you want to do is put the value of the PRIMARY KEY from your Companies table (Table1) into a field on Table2, which I presume is some other kind of info (such as orders). That field in Table2 is called a FOREIGN KEY. This sort of association (called a "Relationship") is what separates a Relational Database (like Access) from a "flat-file" database.

TABLE: Companies
FIELD: (Primary Key) CompanyID (TYPE: AutoNumber)
FIELD: CompanyName (TYPE: Text)

TABLE: Orders
FIELD: (Primary Key) OrderID (TYPE: AutoNumber)
FIELD: OrderedFrom (TYPE: Long Int)

Then set up a "one to many" relationship (under the Tools menu) from Companies.CompanyID to Orders.OrderedFrom.

You should really examine the sample "Northwind Traders" database, snoop through the manuals, and read some beginner-level on-line database tutorials. I don't think anyone here begrudges helping newbies (heck, they're still willing to help me...) but folks will eventually stop responding if they feel like you're just asking them to do it for you... No offense intended.

--Perpetual-Newbie Mac

 

[krillin]

Thx for your comment ( i think). I know what you mean but i have several reasons to duplicate the ProjectName field in table2. And this field is a unique field In table 1.

However i do understand your suggestion and will try to create a better way for this.

Reading through manuals is a great thing but it will take ages to understand the small details that are hidden inside all the options (including VBA). I'm still learning but meanwhile i want to gather all information on problems that will occur when i'm going to start on the project.

This is my way to learn things and by using this way i have learned: Pascal, PHP, HTML, Delphi, Java, MySQL.

 

[ColinEssex]

I suggest you get started on the project, you'll never anticipate all the problems that will happen.

Col

 

[krillin]

tnx colin

 

[directormac]

Absolutely, Col! K, it's precisely BECAUSE the program is so complex and has so many facets that you need to plow in and muck with it. In fact, I have found that a lot of the best material (including some amazing solutions offered on this forum) didn't make any sense to me UNTIL I had run into that particular problem myself and spent some time getting dirty with the factors involved.

Just a thought...

--Wise-Old Mac

 

[krillin]

Here we go again

I have tried several hours to find and read the answers to my problem but i didn't find it. There are some problems left. But i don't really understand wat to do . So here we go

People who have read this topic know how my table's look like

question1:

The combo box i have created has a button next to it wich pressed has to use the combo box value to use it as a filter for all records. And when pressed again will remove the filter

question 2:

I would like to stop the next button if its pressed while the next field is empty (or give a message)

question 3:

The fields ProjectLeader and ProjectCode are updated only when the combo box value has changed, i would like it to be updated when the form is opened

pfhew a big list to work through, but to make life easier here's the code

http://www.bodybagarena.com/RTBase.zip