Reference control in subform

[Guest]

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 - (Me.[Discount])) *
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored' (bound)
controls to enter a value in their respective table fields. The reason that I
need to do this (rather than just using a simple query calculation or a
standard calculated control) is because these values are referred to in other
forms and queries and I can then use the fields for other calculations.

 

[Guest]

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent commands
within the loop, this method would allow you to continue working while the
changes are being made in the background (if you want).

HTH
Dale

 

[Guest]

Thanks Dale,
Yes, that is exactly what happens, only the currently selected record updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update field in
the respective tables?

Thanks,

Graeme

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent commands
within the loop, this method would allow you to continue working while the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 - (Me.[Discount])) *
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored' (bound)
controls to enter a value in their respective table fields. The reason that I
need to do this (rather than just using a simple query calculation or a
standard calculated control) is because these values are referred to in other
forms and queries and I can then use the fields for other calculations.

 

[Douglas J. Steele]

What line is causing the compilation error? If it's Dim rs As dao.Recordset,
with any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Dale,
Yes, that is exactly what happens, only the currently selected record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update field
in
the respective tables?

Thanks,

Graeme

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 - (Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored' (bound)
controls to enter a value in their respective table fields. The reason
that I
need to do this (rather than just using a simple query calculation or a
standard calculated control) is because these values are referred to in
other
forms and queries and I can then use the fields for other calculations.

 

[Guest]

Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

What line is causing the compilation error? If it's Dim rs As dao.Recordset,
with any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Dale,
Yes, that is exactly what happens, only the currently selected record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update field
in
the respective tables?

Thanks,

Graeme

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 - (Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored' (bound)
controls to enter a value in their respective table fields. The reason
that I
need to do this (rather than just using a simple query calculation or a
standard calculated control) is because these values are referred to in
other
forms and queries and I can then use the fields for other calculations.

 

[Dale Fye]

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating your
subform? If it is linked to your main form, on which fields? You should be
able to write a SQL Update query to update the values in your table and then
requery the sub form to display the new values.

Dale

Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Dale,
Yes, that is exactly what happens, only the currently selected record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query calculation
or a
standard calculated control) is because these values are referred to
in
other
forms and queries and I can then use the fields for other
calculations.

 

[Guest]

Hi Dale,
that does get past the error, but then I have another error:
run time error 3020: 'Update or CancelUpdate without AddNew or Edit'
In VB this code is highlighted after the error message:
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)

The SQL for the subform is:
SELECT Product.ProductID AS Product_ProductID, Product.[Type code],
Product.Product_Type, Product.Description, Product.Size, Pricing.PricingID,
Pricing.ProductID AS Pricing_ProductID, Pricing.SupplierID, Pricing.STDCost,
Pricing.DateOfPrice, Pricing.[Discount Price], Pricing.NetSupplierPrice
FROM Product INNER JOIN Pricing ON Product.ProductID = Pricing.ProductID;

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating your
subform? If it is linked to your main form, on which fields? You should be
able to write a SQL Update query to update the values in your table and then
requery the sub form to display the new values.

Dale

Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thanks Dale,
Yes, that is exactly what happens, only the currently selected record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query calculation
or a
standard calculated control) is because these values are referred to
in
other
forms and queries and I can then use the fields for other
calculations.

 

[Guest]

Dale,
sorry - forgot to mention that the subform is linked to the main form via
SupplierID.
I wrote up an update query - but am not sure how to "insert" it onto my form
or subform.
It looks like this;
UPDATE Supplier INNER JOIN (Product INNER JOIN Pricing ON Product.ProductID
= Pricing.ProductID) ON Supplier.SupplierID = Pricing.SupplierID SET
Pricing.[Discount Price] = [STDCost]*(1-[Discount]), Pricing.NetSupplierPrice
= IIf([VAT Registered]=True,[Discount Price]*1.14,[Discount Price]);

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating your
subform? If it is linked to your main form, on which fields? You should be
able to write a SQL Update query to update the values in your table and then
requery the sub form to display the new values.

Dale

Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thanks Dale,
Yes, that is exactly what happens, only the currently selected record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True, ([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet view.

Your code will only update the values on the subform for the currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query calculation
or a
standard calculated control) is because these values are referred to
in
other
forms and queries and I can then use the fields for other
calculations.

 

[Dale Fye]

Sorry I missed this Graeme, try this:

Do While rs.EOF = False
rs.Edit
rs("Discount Price") = ((1 - (Me.[Discount])) *
Me![NewPricingSubform].Form!STDCost)
rs("NetSupplierPrice") = IIf(Me.[VAT Registered] = True, _
[NewPricingSubform].Form![Discount
Price] * 1.14, _
[NewPricingSubform].Form![Discount
Price])
rs.Update
rs.MoveNext
Loop

You cannot edit a record using the technique above until you have actually
issued an Edit command, and the changes you made don't actually get saved
until you issue an Update command.

You will notice that I also removed the brackets from within the rs( ) on
the left side of the equal sign. The quotes around the field name in this
instance perform the same function as the brackets. You have to use
brackets when you refer directly to the field name, like you have on the
right side of the equal sign, but not inside the recordset reference, or if
you use DSUM("Field Name", "your table"), or any of the other domain
functions. The way to avoid having to use brackets is to never put a space
in a field or table name. Some databases (Oracle, maybe SQL Server) don't
even allow spaces if table or field names. I always use the underscore
wherever I want a space. The other time that you need to use brackets is
when you give a field a name that is an Access Reserved word. In this
latter case, Access will sometimes encounter problems and throw error
messages because it confuses the field name and the reserved word.

Dale

Hi Dale,
that does get past the error, but then I have another error:
run time error 3020: 'Update or CancelUpdate without AddNew or Edit'
In VB this code is highlighted after the error message:
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)

The SQL for the subform is:
SELECT Product.ProductID AS Product_ProductID, Product.[Type code],
Product.Product_Type, Product.Description, Product.Size,
Pricing.PricingID,
Pricing.ProductID AS Pricing_ProductID, Pricing.SupplierID,
Pricing.STDCost,
Pricing.DateOfPrice, Pricing.[Discount Price], Pricing.NetSupplierPrice
FROM Product INNER JOIN Pricing ON Product.ProductID = Pricing.ProductID;

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating
your
subform? If it is linked to your main form, on which fields? You should
be
able to write a SQL Update query to update the values in your table and
then
requery the sub form to display the new values.

Dale

Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

:

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu
bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thanks Dale,
Yes, that is exactly what happens, only the currently selected
record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True,
([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet
view.

Your code will only update the values on the subform for the
currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in
bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in
the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working
while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and
NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query
calculation
or a
standard calculated control) is because these values are referred
to
in
other
forms and queries and I can then use the fields for other
calculations.

 

[Guest]

Thanks Dale,
well the code now 'works'.
However I get the same result for every record on the current form but other
records are not updating on the other Supplier records.
I did make some small adjustments to your code to make it work, such as
changing [NewPricingSubform] to [NewPricing Subform]

Here is the VB code I have applied:
Private Sub Form_Current()
Dim rs As DAO.Recordset

Set rs = Me.[NewPricing Subform].Form.RecordsetClone

Do While rs.EOF = False
rs.Edit
rs("Discount Price") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("NetSupplierPrice") = IIf(Me.[VAT Registered] = True, [NewPricing
Subform].Form![Discount Price] * 1.14, [NewPricing Subform].Form![Discount
Price])

rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Form.Refresh
End Sub

Sorry I missed this Graeme, try this:

Do While rs.EOF = False
rs.Edit
rs("Discount Price") = ((1 - (Me.[Discount])) *
Me![NewPricingSubform].Form!STDCost)
rs("NetSupplierPrice") = IIf(Me.[VAT Registered] = True, _
[NewPricingSubform].Form![Discount
Price] * 1.14, _
[NewPricingSubform].Form![Discount
Price])
rs.Update
rs.MoveNext
Loop

You cannot edit a record using the technique above until you have actually
issued an Edit command, and the changes you made don't actually get saved
until you issue an Update command.

You will notice that I also removed the brackets from within the rs( ) on
the left side of the equal sign. The quotes around the field name in this
instance perform the same function as the brackets. You have to use
brackets when you refer directly to the field name, like you have on the
right side of the equal sign, but not inside the recordset reference, or if
you use DSUM("Field Name", "your table"), or any of the other domain
functions. The way to avoid having to use brackets is to never put a space
in a field or table name. Some databases (Oracle, maybe SQL Server) don't
even allow spaces if table or field names. I always use the underscore
wherever I want a space. The other time that you need to use brackets is
when you give a field a name that is an Access Reserved word. In this
latter case, Access will sometimes encounter problems and throw error
messages because it confuses the field name and the reserved word.

Dale

Hi Dale,
that does get past the error, but then I have another error:
run time error 3020: 'Update or CancelUpdate without AddNew or Edit'
In VB this code is highlighted after the error message:
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)

The SQL for the subform is:
SELECT Product.ProductID AS Product_ProductID, Product.[Type code],
Product.Product_Type, Product.Description, Product.Size,
Pricing.PricingID,
Pricing.ProductID AS Pricing_ProductID, Pricing.SupplierID,
Pricing.STDCost,
Pricing.DateOfPrice, Pricing.[Discount Price], Pricing.NetSupplierPrice
FROM Product INNER JOIN Pricing ON Product.ProductID = Pricing.ProductID;

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating
your
subform? If it is linked to your main form, on which fields? You should
be
able to write a SQL Update query to update the values in your table and
then
requery the sub form to display the new values.

Dale

message Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

:

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu
bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thanks Dale,
Yes, that is exactly what happens, only the currently selected
record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True,
([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet
view.

Your code will only update the values on the subform for the
currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in
bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in
the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working
while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and
NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query
calculation
or a
standard calculated control) is because these values are referred
to
in
other
forms and queries and I can then use the fields for other
calculations.

 

[Guest]

Hi Dale,
I'm starting to think the SQL query may be the better option!(?)
Earlier you wrote "If this doesn't work, what is the SQL of the query that
is populating your subform? If it is linked to your main form, on which
fields? You should be able to write a SQL Update query to update the values
in your table and then requery the sub form to display the new values. "

Well I wrote an update query that works - just need to figure out the last
bit there;
"...requery the sub form to display the new values"

My SQL query is:
UPDATE Supplier INNER JOIN (Product INNER JOIN Pricing ON Product.ProductID
= Pricing.ProductID) ON Supplier.SupplierID = Pricing.SupplierID SET
Pricing.[Discount Price] = [STDCost]*(1-[Discount]), Pricing.NetSupplierPrice
= IIf([VAT Registered]=True,[Discount Price]*1.14,[Discount Price]);

Sorry I missed this Graeme, try this:

Do While rs.EOF = False
rs.Edit
rs("Discount Price") = ((1 - (Me.[Discount])) *
Me![NewPricingSubform].Form!STDCost)
rs("NetSupplierPrice") = IIf(Me.[VAT Registered] = True, _
[NewPricingSubform].Form![Discount
Price] * 1.14, _
[NewPricingSubform].Form![Discount
Price])
rs.Update
rs.MoveNext
Loop

You cannot edit a record using the technique above until you have actually
issued an Edit command, and the changes you made don't actually get saved
until you issue an Update command.

You will notice that I also removed the brackets from within the rs( ) on
the left side of the equal sign. The quotes around the field name in this
instance perform the same function as the brackets. You have to use
brackets when you refer directly to the field name, like you have on the
right side of the equal sign, but not inside the recordset reference, or if
you use DSUM("Field Name", "your table"), or any of the other domain
functions. The way to avoid having to use brackets is to never put a space
in a field or table name. Some databases (Oracle, maybe SQL Server) don't
even allow spaces if table or field names. I always use the underscore
wherever I want a space. The other time that you need to use brackets is
when you give a field a name that is an Access Reserved word. In this
latter case, Access will sometimes encounter problems and throw error
messages because it confuses the field name and the reserved word.

Dale

Hi Dale,
that does get past the error, but then I have another error:
run time error 3020: 'Update or CancelUpdate without AddNew or Edit'
In VB this code is highlighted after the error message:
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)

The SQL for the subform is:
SELECT Product.ProductID AS Product_ProductID, Product.[Type code],
Product.Product_Type, Product.Description, Product.Size,
Pricing.PricingID,
Pricing.ProductID AS Pricing_ProductID, Pricing.SupplierID,
Pricing.STDCost,
Pricing.DateOfPrice, Pricing.[Discount Price], Pricing.NetSupplierPrice
FROM Product INNER JOIN Pricing ON Product.ProductID = Pricing.ProductID;

Try:

Set rs = ME.[NewPricing Subform].Form.Recordsetclone

If this doesn't work, what is the SQL of the query that is populating
your
subform? If it is linked to your main form, on which fields? You should
be
able to write a SQL Update query to update the values in your table and
then
requery the sub form to display the new values.

Dale

message Thanks Doug,
Yes it was the Dim rs As dao.Recordset
I have made the required selection.

Now I get another error;
"Method or data member not found"
at ".RecordsetClone"
Is this a syntax issue?

Cheers,

Graeme

:

What line is causing the compilation error? If it's Dim rs As
dao.Recordset,
with any code module open, select Tools | References from the menu
bar,
scroll through the list of available references until you find the one
for
Microsoft DAO 3.6 Object Library, and select it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message Thanks Dale,
Yes, that is exactly what happens, only the currently selected
record
updates.

It was suggested I try the following loop:
Private Sub Form_Current()
Dim rs As dao.Recordset

Set rs = Me.[NewPricing Subform].RecordsetClone

Do While rs.EOF = False
rs("[Discount Price]") = ((1 - (Me.[Discount])) * Me![NewPricing
Subform].Form!STDCost)
rs("[NetSupplierPrice]") = IIf(Me.[VAT Registered] = True,
([NewPricing
Subform].Form![Discount Price] * 1.14), [NewPricing
Subform].Form![Discount
Price])
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Me.[NewPricing Subform].Refresh
End Sub

but with this I get a compile error: "User defined type not defined"

You suggested a query to update values - but will this still update
field
in
the respective tables?

Thanks,

Graeme

:

Graeme,

I assume that your subform is either continuous or in datasheet
view.

Your code will only update the values on the subform for the
currently
selected record, not all of the records. You will either have to:

1. Write a query that will update the values in the subform in
bulk
(preferred method). This will usually be quicker.

or

2. Write some code that will loop through each of the records in
the
subform and update each record individually. If you imbed DoEvent
commands
within the loop, this method would allow you to continue working
while
the
changes are being made in the background (if you want).

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

Hi,
I have a main form: NewSupplier
with a subform: NewPricing Subform

On the main form are two controlsiscount and VAT Registered

On the subform are two controls: DiscountPrice and
NewSupplierPrice
that I
want to reference so that the respective tables are updated.

I have the following as an event on the Discount control:
Private Sub Discount_BeforeUpdate(Cancel As Integer)
Me![NewPricing Subform].Form![Discount Price] = ((1 -
(Me.[Discount]))
*
Me![NewPricing Subform].Form!STDCost)
Me![NewPricing Subform].Form![NetSupplierPrice] = IIf(Me.[VAT
Registered] =
True, ([NewPricing Subform].Form![Discount Price] * 1.14),
[NewPricing
Subform].Form![Discount Price])
End Sub

What seems to happen is that only the first row of the subform is
updated
for each Supplier on the main form.
As always, any help is greatly appreciated!

PS:Ultimately what I want to achieve is to use these 'stored'
(bound)
controls to enter a value in their respective table fields. The
reason
that I
need to do this (rather than just using a simple query
calculation
or a
standard calculated control) is because these values are referred
to
in
other
forms and queries and I can then use the fields for other
calculations.