Using a checkbox / command button to change the record source

[marcfl]

Ok,

Im making a db for the company i work for and its my first time working with access, i also have very little knowledge of vba.

On the order form i have atm, there is a subform where my bosses would be selecting the products they want for the particular order where the data is retrieved from a price list that doesnt change. However we do get contracts where these prices will vary, so what i thought of is adding a check box which would bring up a new subform and has the same fields and all they would need to do is add the varied products and their prices. Am i thinking about doing this the right way or is there an easier way?

The reason i mention this is because, if i modify the price in the subform where the standard pricelist is the price seems to change for all the orders not just for that one specific order..

what are your thoughts?

 

[jordanking]

this is a database design issue. Database design is very important, because if you get deep into development and realize that your data needs are different, it can take an incredible amount of reworking that could have been avoided from the start.

It sounds like you have a very common data need. You need customers to be able to have "orders" based on a "product/service" list. But some customers have different prices for the same products.

There are a couple of ways to do this.
Make an orders table that tracks the customer information and general order detail like date, where to ship etc..

Then have a order detail table that tracks the specific product/service that are ordered. A line for each product with a ORDER ID NUMBER, PRODUCT ID, and quantity, piece count, unit price, total line amount, detail etc...

These products will be based on a price/product list. You
can have one or more default prices for each product (ie general, discount, preferred) that can be set based on the customer's profile or chosen by the user upon entry. Also, the pricing that is kept in the orders detail table can be custom set. This means that if you change your prices for a product in the product table, it does not throw off your historical data in the order details as it would if you just bound the product id alone in the order details.

One thing to note, when allowing a user to "look up" data in a sub form, like you have for the product sub form, try to make that a combo box or other list type control so that the user can not accidentally change the underlying value.

Hope this helps

JK


.....
I'd rather be surfing

 

[marcfl]

Thanks alot mate ill give this a go and get back to you