Calculating & Recalculating

[Waldin]

Good Morning...

I have a sub form named Quotation Parts, When parts are added into the fields a query runs and automatically the next 3 subforms are filled in-in the respective fields, however when i remove the part from the quotation Parts Subform i would like the it to reverse what was added into the other 3 Subforms as well.
It doesnt do this, please advise me accordingly:banghead:
Thanks in advance.

 

[pr2-eugin]

What is the code that you have currently placed?

 

[Waldin]

HI Eugin, below is my current code

Private Sub Product_AfterUpdate()
Dim subform1
DoCmd.SetWarnings False
Me.Descriptions = Me.Product.Column(1)
Me.PartsPrice = Me.Product.Column(2)
If IsNull(Me.PartsPrice) Or Me.PartsPrice = "" Then
Me.PartsPrice = 0
End If
DoCmd.OpenQuery "QryQuotationAppendRR"
DoCmd.OpenQuery "QryQuotationAppendRR2"
DoCmd.OpenQuery "QryQuotationAppendPaint"
DoCmd.OpenQuery "QryQuotationAppendPaint2"
DoCmd.OpenQuery "QryQuotationAppendLabour"
DoCmd.OpenQuery "QryQuotationAppendLabour2"
DoCmd.OpenQuery "QryQuotationAppendOutwork"
Forms![Quotation]![Child334].SetFocus
DoCmd.Requery
Forms![Quotation]![Labour].SetFocus
DoCmd.Requery
Forms![Quotation]![QuotationPaint].SetFocus
DoCmd.Requery
Forms![Quotation]![Child335].SetFocus
DoCmd.Requery
Forms![Quotation]![QuotationDetails].SetFocus
DoCmd.SetWarnings True
End Sub

 

[RainLover]

The Object of a Data Base is to eliminate repeating Data.
You appear to be wanting to to place the same data into three different table.
This is not proper normalisation.

Suggest a Google search to find some normalisation basics.

If you run into problems please come back as Paul Eugin is quite good with normalisation practices.

 

[Waldin]

hi RainLover

the idea is not to repeat data, all parts has different R&R (which is populated in the R&R SubForm), all parts has different Paintwork, Some doesnt need Paintwork, this data is populated into the Paint SubForm.

Now when a part is selected on the Parts SubForm, the relevant paintwork,R&R,Labour and outwork that goes along with the selected part is then automatically entered into the SubForms via a query.

do i make sense?

 

[RainLover]

Waldin

You asked about why when you remove the part form the subform it does not remove it from the other places.

However you have no code to do this. The only code you posted had 7 Append Queries and no Delete queries. So it is not a case of it not working it is a case of there is nothing there to work.

I still believe that there is a problem with your normalisation. If this was done right there would most likely no need to either append or delete records in other tables. This requires to have the correct relationships set up in the first place.

This may be difficult to do but there are pleanty of people here willing to help. This is of course that my assumption the your design is incorrect.

It would be nice to have a copy of your databse or at least a pic of your relationships.

 

[Waldin]

ok, looking at my code above could you help me with the delete queries, my employer would like to see that today, then i could redesighn 1nce he has seen his delete queries.

this would be great.

how do you want me to send you my db.

 

[RainLover]

Waldin

There are some problems with your code. I assume you got a little bit from various places and put it all together.

<<Dim sunform1>> You declared this as a Variable but did not give it a type. e.g. Long, String, Object. It does not matter much because you don't use it. So delete it.

You Turn off Warnings. If there is sometihing that causes you code to crash then Warnings will stay Off. Rather than use DoCmd.OpenQuery you should use Currentdb.Execute. Google it up for a proper example.

Why do you keep changing Focus and then Requery. You only have to use the last one. The others do nothing because they are over ridden by the next SeFocus command.

You should also introduce some error trapping.

Me.PartsPrice = Me.Product.Column(2)
If IsNull(Me.PartsPrice) Or Me.PartsPrice = "" Then
Me.PartsPrice = 0
End If

This could all be replaced by

Me.PartsPrice = Nz(Me.Product.Column(2),0)

Google Nz for a better understanding.

Hope I have been of some help.

 

[RainLover]

Waldin

If you look at a Query in design mode you can change it to a Delete query. The same way you changed it into an append query.

PS

I will be going offline in an hour or so.

To attach a DB go to advanced and click on the paper clip.

zip the file and make sure it is in 2003. I like many do not use 2007 or 2010

 

[Waldin]

you've definatly been of great help, if i uplaod my db to late you can have a look at it 2mrw, thanks again

 

[RainLover]

I will have a look if you promise to use English not things like 3mrw.

So do it now.

 

[Waldin]

due to an unlocated token it cannot be uploaded.

Thanks for being willing though.

 

[RainLover]

I have no idea what that means.

Sing out when you need help.

 

[Waldin]

thanks.will do.

 

[boblarson]

due to an unlocated token it cannot be uploaded.

Thanks for being willing though.

Clear your browser cookies while your browser is closed and log in again.

 

[Waldin]

Good Morning

is there a code to apply alternative row shading to a Report and its SubReports

 

[RainLover]

Waldin

Yes there is. Have a look at this.
http://office.microsoft.com/en-us/access-help/shading-alternating-rows-in-reports-HA001219146.aspx

This as easilly found through Google. I have never had the need to use it.

Give it a try and if you run into problems then we will see if I can help.

 

[Waldin]

thanks Rainman

it works like a shoe that fits perfectly.

 

[Waldin]

RainMan

I Have a SubForm And Its Data Is Based On A Query, Is It possible For Me To Copy From This SubForm, Because currently I Get The Error Message: Command Copy Is Not Available.

 

[RainLover]

What is it you want to copy and where to you want to copy it to.

Are you talking about the Sub form itself or a Record that is displayed on the Sub form.