Is there a way to simplify this code and drive it from a table

[PNGBill]

I am on Main Menu. Do you want SumOfPrice to work at stage ? and Quote Date ?
Click on ID (a specific Command Button with caption "click to ..." would be more user friendly) and Estiamte form appears.

Please run through what you want to happen from here.

Sorry if I am a bit dumb but Industry Knowledge is lacking at this end

 

[Joshlindsay]

I am on Main Menu. Do you want SumOfPrice to work at stage ? and Quote Date ?
Click on ID (a specific Command Button with caption "click to ..." would be more user friendly) and Estiamte form appears.

Is that like the hyperlink examples they have on the Northwind and Services templates? I was trying to figure out how they worked.

Please run through what you want to happen from here.
Sorry if I am a bit dumb but Industry Knowledge is lacking at this end

An estimate can be made up of purely mail components (these are easily selected from a table) or a print component (which needs to be calculated based upon a few variables)

Once you're in the Estimate Screen click on the add printing button.
This is where a print price is calculated based upon the paper selected, trim size, number of pages and quantity.

If you look into the VBA script for that form you'll see in frmEstPrintingSpecs Private Sub Command49_Click() which houses the calculations for the price.

If you scroll down to the rates section you'll see I've manually set them.
Below that is the calculation which rolls through the quantities.
Basically I'd like that to work solely on a table without needing to recode in a section in the calculation for each quantity range.
Now that I think about it it may be a long shot.

I'm sorry if my description is a bit crap.
It's always hard to put in writing.

I really appreciate your help Bill.

 

[PNGBill]

Is that like the hyperlink examples they have on the Northwind and Services templates? I was trying to figure out how they worked.

Does the same job as you have setup to click on the ID text box control.
A Command Button is just more like a normal human action.
If you want to trun on your TV you push the onoff button rather then touch the corner of the screen.
You have Command Buttons for Exit.

I can add a Command Button for you.

Have to go out again... will try and get something sorted later today.

 

[PNGBill]

I know this is outside of the original post question but...

You have two ways to open the Estimate Form.

One is an existing form and the other, is a new form.

When opening a new record form, you can have some feature not Visible.

The point of a new record is you first must assign a customer, or if you are already on a customer, then have the new record open with that customer showing.

As you add data to the record, then relevant buttons become visible.
This looks "smart" but also prevents the operator clicking a button that should not be used yet.

I can do an example of this for you to replicate if you wish.

Did you say what version of access you use ?

 

[Joshlindsay]

That sounds excellent. Thanks.
We're using Access 2010.

 

[Joshlindsay]

Great news!
The clever chap I work with has written a loop for our average price calculation.

If rs.RecordCount <> 0 Then
        rs.MoveFirst
        Do Until Quantity <= rs!MaximumQuantity
            Total = (rs!MaximumQuantity - rs!MinimumQuantity) * rs!ClickPrice
            Subtotal = Subtotal + TotalPrice
            rs.MoveNext
        Loop
        TotalPrice = (Quantity - rs!MinimumQuantity) * rs!ClickPrice
        Subtotal = Subtotal + TotalPrice
        AvgPrice = Subtotal / Quantity
        MsgBox (Subtotal)
        rs.Close
    End If

 

[PNGBill]

Import the form MainMenu from this database. Save a copy of your database first, just in case the wheels fall off.

I have changed the form to be form view, continuous.
Added a command Button to open frmEstimate.
Changed the text box names and labels. Access defaults by naming form controls the same as the field name for the control source. This only causes problems later. Best to name them different from the beginning.

You have two forms for Estimates frmNewEstimate and frmEstimate.
This is good situation where one form can do the same job, save you work and keep the file smaller. Refer my earlier post re making controls visible or hidden.

Option Explicit should be a given on your modules and you should Compile your code when ever making any changes - there is an error in one of your forms.

 

[Joshlindsay]

Import the form MainMenu from this database. Save a copy of your database first, just in case the wheels fall off.

I have changed the form to be form view, continuous.
Added a command Button to open frmEstimate.
Changed the text box names and labels. Access defaults by naming form controls the same as the field name for the control source. This only causes problems later. Best to name them different from the beginning.

You have two forms for Estimates frmNewEstimate and frmEstimate.
This is good situation where one form can do the same job, save you work and keep the file smaller. Refer my earlier post re making controls visible or hidden.

Option Explicit should be a given on your modules and you should Compile your code when ever making any changes - there is an error in one of your forms.

Thanks Bill.
I've just tried importing this but even after deleting my own main menu it's exactly the same. Weird.

Once of those estimate forms is the old design we we're going for but it didn't work out.

I'll add Option Explicit to all my modules. Thanks.

 

[PNGBill]

Do you see the "Click to view estimate" button ?

 

[PNGBill]

:banghead: You need the subform as well - sorry.
Try this one.

 

[Joshlindsay]

:banghead: You need the subform as well - sorry.
Try this one.

Perfect! Thanks Bill.

 

[PNGBill]

frmEstPrintingSpecs.
Quantity is restricted to three options. How should this work ?

You could allow the operator to enter a number and the system will evaluate the entry and change it to a reasonable number ie 2,950 will become 3,000

 

[Joshlindsay]

frmEstPrintingSpecs.
Quantity is restricted to three options. How should this work ?

You could allow the operator to enter a number and the system will evaluate the entry and change it to a reasonable number ie 2,950 will become 3,000

Hi Bill,

We're still playing around with the quantities. This is a kind of a learn as you go project.
Trying to decide if we want unlimited per estimate of just limit it to 3.
Unlimited would be a case of wrapping a for each around my calculation code (I think)

We're also thinking maybe we'll move the frmEstPrintingSpecs inside the frmEstAddProducts and build a "Product Group" up rather have individual products.

 

[PNGBill]

I have just had another look at your issue.

Your code should get the quantity price issue resolved using tblEstPrintingClickPrice - correct ?
Assuming above is correct, the table data includes ClickType, Description & PrintTypeID.

Which controls on form holds this data ?
ClickType looks like it is either Colour or Mono - your form has mulitply controls to hold this data ??

Description ? where is this defined ?

re ColourPages / MonoPages - consider allowing a selection of one or the other (if that is how it is done).
As the form is now, the operator can enter in both options. Is this how it would be done ?
You could have Option Buttons so only one can be clicked.

PrintTypeID ?

I will be away for the next 6 days but are happy to keep on this issue, if you wish.

Sorry I have not resolved your original post question.
It is quiet easy to do except, you really need to get the underlying data and form correct or the code will not work as you make improvements.
eg code to count the pupils in a classroom but.. we have not sorted out the how to define a classroom and what age they are etc.

Your Forms and Tables etc appear well done, if a little muddled (Forms) so don't dispear. It will all come together soon.

If you have made updates, post a new sample database, if you wish.