Part lookup / Part add record form

[andyaerial]

Hi guys,
I'm very new to programming with MS Access and have a ajor issue I can't resolve.

I have a form (form a) and in this form I have a box for entering a product ID number from another table 'not the one this form is used for' what I want to be able to do is....

When the ID number is entered into the box, have another box display information from the next column in another box on form a, from table b.

ie

Box 1 on form a=34576
Box 2 on Form a=Wooden coffe table (beech)

table b
(col 1)ID= 34576 (col 2)Description=Wooden coffe table (beech)

however, I also need form a to be able to use the relevant information in Box 1&2 and place it into table c aswell as some other information on form a.

I need a step by step instruction if anyone can help me as as I said first I am brand new at this.

thanks in advance.

 

[CEH]

Sounds like you first need to look at "normalization" It looks like either have or you are going to have, the same data in multiple tables... A No-No.......

 

[andyaerial]

I don't think I explained myself very well....

Table A
ID:25464 Product:Table

Form A
[Box1](Entered by user)ID:25464 -macro or inner relationship or something-
[Box2](Auto filled by macro or equivilient)Product:Table

Then a few more boxes on form a like:-

Date, Sales Rep.

....save.....

Then Table C.
ID:25464 Product:Table Date:12/12/2009 Sales Rep:XXXXXX

Help with creating/linking box 1 and 2 is what I'm after.

 

[CEH]

OK, I would suggest a combobox in place of your "Text1"
But, first, you do not want to, or need to, and you should not store your "description" of item in both tables.
Use a combo built on table A with field "ItemID" and "Description" Use the wizard... Do not pick the "hide" button.

In the combobox select those 2 values, but hide the Description... Then use the "on change" event to copy the values to the text box...
Me.YourText2box = Me.YourCombo.columns(1)
Column 0 is actually the first column, "ItemID"

 

[andyaerial]

Thanks Curtis that has really helped.

Also it gave me something to think about in "normalising" my database, after some *light* reading I see what you mean now. Thanks.

Andy