Names not Numbers

[leeli67]

I have been getting some good help on this forum , thanks for that , next problem

I have made an Order Form Using an Order Table and Order Detail table, what I need to know is where should I start looking for Info on how I can get NAMES and not NUMBERS on the form, surely I don't have to remember the ID's for all my customers and products.



Attachment 6452

[June7]

Use multi-column combobox. Check out tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in Access Forms: Control Basics section.

[leeli67]

Can u make an existing input box on a form into a combo box ?

[June7]

Select the textbox and right click.

[orange]

Instead of using a table as recordsource of your form, create a query that joins the associated tables and include the name field(s) from the "other " table(s) and make the query the recordsource of the form.

[leeli67]

So would that allow me to do this :

Attachment 6462

[June7]

Use multi-column combobox for the ProductsID/Name (you appear to have that)

Then options:

1. RecordSource for the subform would be a join of order details and product info tables. This will make all related fields available and can bind textboxes to them. The jointype would be 'show all records from order details and only those from products that are equal'. Set the Price textbox as Locked Yes and TabStop No.

2. Include the price as a hidden column in the combobox RowSource. Price textbox ControlSource refers to the price column of the combobox.

[leeli67]

Thanks June for your reply , Could you explain this bit in a different way, I cant get my head round it :

"RecordSource for the subform would be a join of order details and product info tables. This will make all related fields available and can bind textboxes to them. The jointype would be 'show all records from order details and only those from products that are equal'. Set the Price textbox as Locked Yes and TabStop No."



1. is the following done in Query design --- " 'show all records from order details and only those from products that are equal'"
2. Are you saying to do a query of order details table and products then use that as a subform (datasheet style) , with Order Table as form header/top?
3. Does it matter if Format of Price box is number or currency.

Many thanks for your Help

[June7]

1. Yes. This is the jointype. Double click the line linking the pk/fk fields in the query design to open dialog.
2. I use only forms or reports as objects in subform containers. You can try having the query be the object or use the query as RecordSource for the form in the subform container or build the SQL statement right in the form's RecordSource property.
3. I never set format and lookup properties in tables. I do this in the properties of textbox/combobox/listbox on form or report. However, don't think it will matter. Try it.

[leeli67]

ok cheers that sort of worked and the price now populates the price field, but alas the details are not being saved back into the order details table as shown in my last pic . any ideas anyone

Attachment 6477Attachment 6475Attachment 6476

i think the problem maybe my query setup

[June7]

Only values in bound controls are saved to table. Any control with an expression in ControlSource is not bound. Results of expressions are not saved to table. If you want to save the price to the order details table, will need VBA code in some event behind the subform. A principle of relational database is to not duplicate data. So the concept is to save the product ID and then retrieve the price by query joins. However, if there is a chance the price could change for product, need to either save the price or create new records in the product table for each price for each product. This would necessitate a field in product table to indicate active/inactive and filter combobox RowSource to offer only the active product records.

Code to save price to table can be simple as: Me!Price = Me.Price. Trick is figuring out which event to use. Perhaps the AfterUpdate event of the combobox.

[leeli67]

Ok , you explain it very well , so everything will be tied by Primary and foreign fields and i can do querys to retrieve my info , all good

1 last question

the customers in the database receive discounts on certain items where would be the best place to implement this ?

i.e customer A gets a 20% discount on cakes but not BREAD,ROLLS

[June7]

Possibly another field in Products for discount rate. Put a 0 value in records for those that don't receive discount. Then again, just like the price, the discount rate field is available by joining tables in query.