form dropdown box with costs (1 Viewer)

[rainbows]

hi

I have 2 tables

I is the main information I will save my data the other is where I keep my products and cost

the idea was to open up the form select a part from the drop down box which would then bring down the costs. decsription, proiduct code ect automatically

i woild then enter any other data i need like qty etc


i cannot get any of it to work no materr how i try . could you plesse look at the attached and adive /help me to get it working

thanks

steve

 

[June7]

Your tables are not linking on key fields. Descriptive fields should not be duplicated in multiple tables. Should save ProductID, not ProductName nor ProductCode nor Description. Only save UnitPrice if there is possibility it will change in the future.

Would be nice if you clarified which table is which.

Not seeing Orders table yet you have an OrderID in ProductUnits table as well as [main product details]. Why?

How does Products table relate to the other tables?

Bind the form to only the table you want to enter/edit records for - which seems to be [product units].

Combobox settings:
RowSource: SELECT ID, [Product Name], [Product code], Description, [Unit price] FROM [main product details];
ControlSource: [product id]
BoundColumn: 1
ColumnCount: 5
ColumnWidths: 0;1;0;0;0

UNBOUND textboxes can reference combobox columns by index to display related info - index begins with 0:
=[Product Name].Column(2)

Advise not to use spaces nor punctuation/special characters in naming convention. Be consistent with capitalization. Better would be ProductUnits, OrderID, etc.

 

[rainbows]

thanks for the advise

this form that I am try to get to work will be a sub form. from a master sales order form

the "order id" will link it

the products table is where all the small parts are to make up the main part in the table " main product details and cost et


the idea is that on the orders from I will just select one assembled unit
and on another subform I will select all the parts for that assembled unit to purchase

the sales invoice will be for the main part
the purchase order will be form the parts I select from the products table

thanks

steve

 

[June7]

Doesn't alter my comments.

 

[arnelgp]

here check the datasheet form.
also I check the relation ship and the [main table details] structure.

 

[rainbows]

hi

thank you

I have mislead you

the table products will not be used or this form

just the 2 tables


I want to select one productname and it will automacitically fetch the product code , description and cost

the only thing I will fill in is the qty required


so I should have a from after completing it
with
product name , product code, description cost and qty
this data will then form my reports

hope this makes sense

thanks for your help

steve

 

[arnelgp]

you misunderstand the concept.
you don't need to save all the fields, ie. productname, description, etc.. on the
table. you it is already in the product table.
what you need is only the ID of that product, qty.
this ID when linked, to product table will give you all info about the product.
you do that my making a query.
you use this query as a source for your report also.

 

[June7]

Manufacturing/assembly type database is common topic on numerous forums. Do a search and you should find examples.

 

[rainbows]

hi

thanks

I tried to have just the id and the qty but when I kinked them it would not let me enter data into the qty field

this has been the problem all the time and why I think I am struggling

I think there will be about 10 product to choose from in my drop down box but the qty will vary

thank for helping me

 

[June7]

Did you do modifications suggested in post 2?

 

[Dreamweaver]

Just had a look Noticed a few things


1 Having Field And Table Names With spaces is not a good Idear as it can have lead to problems later.
2 Have you got the product Description and product code data the wrong way around???
3 this may be just me but I never ever use comboboxes in tables.
4 your using one field to store all suppliers for a product that will in itself be a nightmare later.


Haven't checked any more not a lot of time.


This is a pic of a basic products screen I built for a project I'm playing with at the moment hope it will help

 

[rainbows]

hi

I think I have done what was suggested in post 2 but I cannot get that working

I have tried to change it but it still don't work , now I cannot even get the dropbox showing the data

thanks for you help

 

[arnelgp]

you don't create a relationship on "product name", you use its autonumber or primary key as linking. you can/may delete the content of product name and your subdata will be orphaned. you cannot delete the autonumber. you have to delete the whole record or reseed it to remove.

 

[June7]

No, you have not done all the changes I suggested.

Again, relationship should involve key fields. ID is primary key so it should be saved as FK into related table. Don't link on name and don't save name. Fix relationship in Relationships window.

Bind form to [product units] table.

Bind combobox to [productid] from [product units].

Use expressions in textboxes to display related info.

Advise to give each ID field a unique name.