Seeking advise (1 Viewer)

[radubalan]


Hello everyone,
Please advise or help me to achieve the following:
I made a simple quotation system in Microsoft Access with multiple tables that feed data in a central form like an invoice. So, I have products (picked from drop down list), quantity, product price, subtotal for each line. I want to be able to change right in this form product price (I want to bypass the price that came with the product with a higher or lower price) but in a manner that saves in the current quotation table this price but not updating the price from products table.
In other words, I want to be able to overwrite the price of product (higher or lower) in each quotation without updating price in products table.
In Excel I made this by having another column where if the value was different then zero it was taken like the price for calculation.
See attached pictures.
Thank you for your help.


 

[Gasman]

I'd probably make that field unbound and populate it with the price of the product in the AfterUpdate event of the Product Combo.?
You could also add whether the item attracts tax or not, discount etc.?

 

[arnelgp]

add a Price field to your Quotation table and bind that field to your quotation form.
your product combo should have another column for the price.

add code to the combo's AfterUpdate Event, to put the price to the price column of the quotation

private sub combo_afterupdate()
me.price = combo.column(1)
end sub

 

[radubalan]

add a Price field to your Quotation table and bind that field to your quotation form.
your product combo should have another column for the price.

add code to the combo's AfterUpdate Event, to put the price to the price column of the quotation

private sub combo_afterupdate()
me.price = combo.column(1)
end sub

thank you for your fast answer
Can you explain a little more....I'm not an expert in Access and not sure exactly where to start:

this price field in the quotation table is different than my existent price field?

 

[radubalan]

I'd probably make that field unbound and populate it with the price of the product in the AfterUpdate event of the Product Combo.?
You could also add whether the item attracts tax or not, discount etc.?

thank you for your reply...can you explain a little in more detail how to make this?

 

[arnelgp]

see this sample, especially the product combo.
view it in design view.

 

[radubalan]

see this sample, especially the product combo.
view it in design view.

thank you...I will take a look in the afternoon.

 

[radubalan]

see this sample, especially the product combo.
view it in design view.

I wonder if I think this correctly....because I can't see relationship betwwen our tables in the sample database (it seems that are no relationship between your tables but then again, I am not sure this is posible):

so the field price in the query is the field price from the product table?
and combobox is based on this field from the query, but if I modify the price in combo it's taken into calculation? How, cause I see the calculation is made into query?

I saw the vba code....

I assume this is for preventing user to provide data through detail form:

• Private Sub Form_BeforeInsert(Cancel As Integer)
• Cancel = (Nz(Me.Parent!QuoteID, 0) = 0)
• If Cancel Then
• MsgBox "You need to Fill-up the Main form first."
• End If
• End Sub

I am not sure what is this for:

• Private Sub Form_Open(Cancel As Integer)
• Me.ID.ColumnHidden = True
• Me.QuoteID.ColumnHidden = True
• End Sub

And this......... if product name updates Price = Me.ProductID.Column(2) ???? I don't understand

Private Sub ProductID_AfterUpdate()
Me.Price = Me.ProductID.Column(2)
End Sub


Sorry, as I said before I am not a big knower about MS Access.

 

[arnelgp]

first code, you are correct it prevent Adding record to subform when the main form has no record.
becoz the two are Related. cannot have a child when no parent.

second code, the subform is datasheet, it hides those two fields. they are not relevant for data entry.

third, see the Rowsource of prouctID (combobox) of subform. the 3rd column (column(2)) has price on it.
we just assign the price to the price on the subform.

Edit: i don't have relationship, yet, but it will work.
the subform has Master/Child link fields.


Edit2: I forgot to put some records to the Client table. close the quotationform first and put some dummy data to this table
then open the form again.

 

[Uncle Gizmo]

And this......... if product name updates Price = Me.ProductID.Column(2) ???? I don't understand

See video number 2 on Nifty Access:-

Combo Box - Nifty Access

Combo Boxes Combo Boxes are the most fascinating and useful "Controls" you can have on your Form. They have Many uses. Combo Boxes can be manipulated in all sorts of ways without using VBA. Once you've mastered the combo-box, and once you have mastered Queries, the building of queries in the...

www.niftyaccess.com

Should explain ArnelGP's code...

 

[gemma-the-husky]

Note that if you don't store the price actually quoted on the quotation, then if you change your list price, and then reprint the quotation it will most likely pick up the new price.

You might gradually drift into maintaining a price change history. Also, when you manual over-ride a price, you might want to store who and when entered the non standard price, and even record a note about the change.

You might need to change your table design to allow you to do some of this.

 

[radubalan]

Note that if you don't store the price actually quoted on the quotation, then if you change your list price, and then reprint the quotation it will most likely pick up the new price.

You might gradually drift into maintaining a price change history. Also, when you manual over-ride a price, you might want to store who and when entered the non standard price, and even record a note about the change.

You might need to change your table design to allow you to do some of this.

I know what you say....I thinked about that also...but I don't how to make this database.
Right now, I use a excel file linked to another excel called products and it works, but we are now 5 people making quotations and I would like to make a database for this.

The story behind this quatotion is:

PArt 1 - client, employe that makes quotation, date, title of quotation
PArt 2 - products - products from products table
Part 3 - operations - operations from operation table
PArt 4 - labour, discounts, others

This quotation goes to a boss and he wants to make changes in values like he wants (he wants to modify the prices of products, prices of operations - but without reflecting this into products table) ...of course I do need to have the same prices of products and operation when I want later to print the same quotation.

Do you have any ideea on how to improve my database?

 

[Pat Hartman]

Use the method suggested by arnel.
Change the RowSource query of the product combo to include price. Change the column count and set the width to 0 since the drop down doesn't need to show it. Let's assume that the price is the third column in the RowSource query. In the AfterUpdate event of the combo, copy the price to the new bound price control

Me.Price = Me.cboProduct.Column(2)

The columns of the RowSource are a zero-based array. Therefore, the third column is referenced as .Column(2)

Since the price is stored in the Order, you can overwrite it as necessary. I agree with logging who manually overwrote the price.