How to do this.

[richardplr]

Hi, I have a product menu and I want to keep track the record that when the price changes.

I have prepare a subform, I wish to made the sub-form invisible if there are no data in the table (which mean that the are no record show price changes).

I am stuck here and don;t know what to do next, can someone help me (I have attahed my demo program for your understanding.

Thank you

 

[goldenvision]

I have done something similar to this making individual text boxes visible/invisible dependant on the contents of other fields.

Could you not try something along the lines of :

If [field in the subform] = null then
[subform name].visible = false
else
[subform name].visible = true
end if

HTH

 

[richardplr]

I have done something similar to this making individual text boxes visible/invisible dependant on the contents of other fields.

Could you not try something along the lines of :

If [field in the subform] = null then
[subform name].visible = false
else
[subform name].visible = true
end if

HTH

Thank you for your help. I will try it. but what about keeping track of record if the price changes.

How should I do it. Anyone can help.

Thank you

 

[goldenvision]

Not sure what you mean

 

[richardplr]

Not sure what you mean

Look at my attached programme. I have a product program and I have unit price field. I want to capture the date and also the last price before it changes to a new price. example. if the current price is $5.00 and now increase to S$5.20. The sub-form will capture the date effect the change and also the price of the before the change (that is $5.00).

Hope you understand now

Thank you

 

[daveUK]

You're going to have to hold the price change data in a table.

Easiest way to do this is to have a cmd button on your form called 'Price Change' or something similar. When the user clicks this button, a form will open with the date of the price change (today's date???) and the new price. When the user presses another cmd button 'Submit Price Change(???)' This will run an append query which will enter the new price and the date change into the new table. It will also update the form so that the new price is displayed.

The new table will also need to hold the primary key field of the of the form so that you can run a report(if you want to) to see as and when the price has changed.

 

[goldenvision]

I understand what you mean now.

I have amended your database to incorporate a datestamp in the amendment date field so that when the existing price text box is changed the current date appears in the amendment date field

 

[richardplr]

I understand what you mean now.

I have amended your database to incorporate a datestamp in the amendment date field so that when the existing price text box is changed the current date appears in the amendment date field

Hi.

Really thank you for your help. I have tried your attached program but it doesn't work. Why is it so.

I want the old price also appear at the subform, do you is possible????

Please advise. Thank you

 

[richardplr]

You're going to have to hold the price change data in a table.

Easiest way to do this is to have a cmd button on your form called 'Price Change' or something similar. When the user clicks this button, a form will open with the date of the price change (today's date???) and the new price. When the user presses another cmd button 'Submit Price Change(???)' This will run an append query which will enter the new price and the date change into the new table. It will also update the form so that the new price is displayed.

The new table will also need to hold the primary key field of the of the form so that you can run a report(if you want to) to see as and when the price has changed.

Hello Dave

Do you think, you can help me on my demo programme. I really stuck here.

Thankyou

 

[goldenvision]

I'm doing it as we speak

 

[richardplr]

I have tried and couldn't do it right. someone can help me????

Thank you

 

[daveUK]

Richard

I'll have a look at it when I get home - I'm at work at the moment

 

[daveUK]

The reason you are having trouble is that the database needs to be re-designed.

You should have one table for Suppliers and this should be linked with a one-many relationship with another table listing the products. The Suppliers will then be the main form with the Products table making up the subform. This will mean each supplier can have more than one product. You'll also probably need another table to hold the old prices, which will also be a subform. Any possibly one for the new prices as these will also be changing.

If you're still having probs with setting it up, I'll have a go tomorrow lunchtime as I've got a couple of hours spare then.

 

[richardplr]

Hi Dave

I have followed your advise to prepare my database. Please see attached programme and I think there are a few more stage to complete before the programme can work.

I have used goldenvision method to set the old price (Me.Date_of_Amendment) but this can only work if I make a change at the old price field but not the unit price field.

I have tried and my problem is I set the same method at the unit price but when I wanted to use Me.date_of_Amentment but I couldn't because it is not belong to the same subform.

Please help.

 

[daveUK]

Richard

I can't attach the database as it is over 100kb even after I've compacted it and zipped it. PM me with your email details and I'll mail it to you.

I've re-rehashed it slightly and deleted most of the reocrds you had before (sorry). Quick explanation:

Tables:
Currency - as before
TblOldPrice - holds the old price
TblUnitPrice - holds the current price
TblSupplier - holds the suppliers
TblProductInventory - hold the products

Forms:
FrmSupplier - Main form with 3 subforms
FrmProductInventorySub - main subform on FrmSupplier, contains the product code, name
FrmOldPrice - subform on FrmProductInventorySub
FrmUnitPrice - subform on FrmProductInventorySub
FrmUpdatePrice - This is where the new price is entered, opens from a cmd button on FrmProductInventorySub

How it works:

Open FrmSupplier to view all of the product info etc. Press cmd button 'Update Price', which opens FrmUpdatePrice. The current price is shown. Enter the new price in the relevant txt box and press 'OK'. 4 queries are then run, 2 which delete the price info from TblOldPrice and TblUnitPrice and 2 which append the updated old price and the updated unit price. FrmUnitPrice and FrmOldPrice are then requeried to show the new price details.

 

[richardplr]

Richard

I can't attach the database as it is over 100kb even after I've compacted it and zipped it. PM me with your email details and I'll mail it to you.

I've re-rehashed it slightly and deleted most of the reocrds you had before (sorry). Quick explanation:

Tables:
Currency - as before
TblOldPrice - holds the old price
TblUnitPrice - holds the current price
TblSupplier - holds the suppliers
TblProductInventory - hold the products

Forms:
FrmSupplier - Main form with 3 subforms
FrmProductInventorySub - main subform on FrmSupplier, contains the product code, name
FrmOldPrice - subform on FrmProductInventorySub
FrmUnitPrice - subform on FrmProductInventorySub
FrmUpdatePrice - This is where the new price is entered, opens from a cmd button on FrmProductInventorySub

How it works:

Open FrmSupplier to view all of the product info etc. Press cmd button 'Update Price', which opens FrmUpdatePrice. The current price is shown. Enter the new price in the relevant txt box and press 'OK'. 4 queries are then run, 2 which delete the price info from TblOldPrice and TblUnitPrice and 2 which append the updated old price and the updated unit price. FrmUnitPrice and FrmOldPrice are then requeried to show the new price details.

Hi Dave

I have send my email in your PM. Pls check.

Tks

 

[daveUK]

I've emailed the database to you.

 

[richardplr]

I've emailed the database to you.

Hi Dave

Thank you very much for your help. It look beautiful.

I will modified a bit to suit my database.

Once again. I like to say thank you to you