Populate a textbox if a value exists in a related field

[rockieme]

I have a text box in an Access form for entering the cost of products purchased. The form's source is a query that draws its information from two tables. One of the tables lists the products with a cost for some of them (those that will seldom change). The prices of the other products will change regularly. I want the form to automatically enter the costs that are available from the list, but at the same time allow me to enter costs manually for those that don't have costs entered in the table.
I wonder if someone could help me. I am fairly experienced at Access up to macros, but not so good at VBA.

 

[TheAceMan1]

How are ya rockieme . . .

[blue] I want the form to automatically [purple]enter the costs that are available from the list[/purple] . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Whats the story on the List? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[hondaman2003]

Can i ask why you have two tables for products? I know you said one is for frequent price changes and the other is for rare price changes, however it seems to me products that you purchase are still products no matter how often the price changes.

The reason I say that is if you have two tables, for example a table for employees and a table for supervisors and you have a "relationship" setup in in a query you will not be able to modify the data from the query in anyway. But if you run the same query with only one of the tables you will be able to modify the data in the query.

Does this make sense?

 

[rockieme]

Thanks for helping out. I actually have three linked tables in a query. One table is for actual Product Purchases. Another table is for Product Markups (markups for resale)This basically serves as a junction table connecting the two other tables. The third table lists all the products and the costs where those are known in advance (these are the costs that are fairly constant). I want the form to automatically pick the cost from the product listing table (if there is a cost recorded) when I select the Product in the Product Purchases table. Where there is no cost in the Product listing table I would enter the actual cost directly.
I hope this makes sense.

 

[hondaman2003]

Let me start by saying this is do-able but will be a little complicated. I have a few questions below to clarify exactly what we are doing so I bring you in the correct direction. I also need to clarify the tables you are using to I understand how they fit into the puzzle.

Product purchases table is products that have been purchased and their wholesale price you paid?

Product markups table is the "retail" price you are selling the product at?

Product listing table is products available that you can purchase and the price?

Does the query do anything to "filter" the data or simply link all the table and display all the data?

What is the end purpose of this form? Are we ording? Are we simply updating our records?

 

[rockieme]

You have grasped the scenario very well.
The Product purchases table is for products that we purchase at wholesale price.
Product markups table shows the markups to arrive at the price we sell at.
Product listing table lists the products available that we can purchase and the price of those products that have a price that will generally not change.
The query just links the tables together and displays the data I want to use in the form.
The purpose of the form is to enter our orders rather than updating the records.
Hope this helps. And thanks again.

 

[hondaman2003]

I'm sorry a few more questions to fully clarify. Lets assume you are able to see all the records you want to see on the one form. After you have changed the prices, what happens next? I am trying to figure out after you change a price or enter a new one, how do you know what you are going to order? Or do you order everything everytime?

 

[ProgramError]

I suggest you combine all tables into one, with the folowing fields

Product key
product
wholesaleprice(or your purchase price)
markuppercentage
possibly a products available field

the selling price can be calculated by purchase price + markup

queries can be made to show products available, specific products.

A list of product to be ordered can be created using an append query to an order table(with or without costs) . Once an order is created, the user could just fill in the empty cost places.

This should get you one step closes to normalising your tables and it will be much easier to run queries on them.
Hope this helps.




Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[rockieme]

The database is for a business that designs and builds kitchens and bathrooms. Each order is a project for which items and components are individually ordered every time as each project is unique. Therefore everything for a project is ordered specifically for each project when it is commissioned.
One more thing: the product markups table prodMarkup Primary Key field and a date field so that when markup percentages are changed new projects will be linked to the latest markups.
I really appreciate your willingness to think this throught with me.
Re: Program Error. I am not sure that combining into a separate table will solve my problem, but I'll have a look at it.