Help needed with Price Change

  • Thread starter Thread starter NNlogistics
  • Start date Start date
N

NNlogistics

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
 
If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
 
Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
 
That would involve reading through the subform's recordset and changing the
value in VBA.
 
Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe
 
There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


Klatuu said:
That would involve reading through the subform's recordset and changing the
value in VBA.
Click to expand...
Click to expand...
 
Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


Klatuu said:
There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


Klatuu said:
That would involve reading through the subform's recordset and changing the
value in VBA.
Click to expand...
:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


Klatuu said:
There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
Dave
Unfortunetly, I am still having a problem after including the rst.edit

error is "Method or data member not found" at rst.edit?

My coding Now looks like

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If

--

Dave thanks again and, if possible, when you get a chance is fine. I also
have an issue with an event, but I'll take that up in another post
Thanks for any assistance


Klatuu said:
Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


Klatuu said:
There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, it just dawned on my you are using ADO. ADO doesn't require the .Edit
method, in fact it doesn't have one. That is for DAO.
Back to the orginal problem of opening the recordset. Is it still not
working?

--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave
Unfortunetly, I am still having a problem after including the rst.edit

error is "Method or data member not found" at rst.edit?

My coding Now looks like

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If

--

Dave thanks again and, if possible, when you get a chance is fine. I also
have an issue with an event, but I'll take that up in another post
Thanks for any assistance


Klatuu said:
Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


:

There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
No, if I remove the 'rst.edit', I'm back to my origianal error." I am
getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update

As always, thanks
--
Thanks for any assistance


Klatuu said:
Sorry, it just dawned on my you are using ADO. ADO doesn't require the .Edit
method, in fact it doesn't have one. That is for DAO.
Back to the orginal problem of opening the recordset. Is it still not
working?

--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave
Unfortunetly, I am still having a problem after including the rst.edit

error is "Method or data member not found" at rst.edit?

My coding Now looks like

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If

--

Dave thanks again and, if possible, when you get a chance is fine. I also
have an issue with an event, but I'll take that up in another post
Thanks for any assistance


Klatuu said:
Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


:

There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
ADO is not my strong suit, but it appears you are mixing open an recordset
and a connection.
Look in VBA Help for the OPEN method for ADO. It shows examples for both.
Hopefully that will help.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
No, if I remove the 'rst.edit', I'm back to my origianal error." I am
getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update

As always, thanks
--
Thanks for any assistance


Klatuu said:
Sorry, it just dawned on my you are using ADO. ADO doesn't require the .Edit
method, in fact it doesn't have one. That is for DAO.
Back to the orginal problem of opening the recordset. Is it still not
working?

--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
Dave
Unfortunetly, I am still having a problem after including the rst.edit

error is "Method or data member not found" at rst.edit?

My coding Now looks like

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If

--

Dave thanks again and, if possible, when you get a chance is fine. I also
have an issue with an event, but I'll take that up in another post
Thanks for any assistance


:

Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


:

There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
Dave

Sorry I didnt get back to you sooner. It turns out that all my problems
with coding was not a coding issue at all. It turns out the problem was with
the query and the criteria I used. I eliminated the criteria and used "if"
statements to offset the criteria. All is well.

Thanks for all your help!!!
--
Thanks for any assistance


Klatuu said:
ADO is not my strong suit, but it appears you are mixing open an recordset
and a connection.
Look in VBA Help for the OPEN method for ADO. It shows examples for both.
Hopefully that will help.
--
Dave Hargis, Microsoft Access MVP


NNlogistics said:
No, if I remove the 'rst.edit', I'm back to my origianal error." I am
getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update

As always, thanks
--
Thanks for any assistance


Klatuu said:
Sorry, it just dawned on my you are using ADO. ADO doesn't require the .Edit
method, in fact it doesn't have one. That is for DAO.
Back to the orginal problem of opening the recordset. Is it still not
working?

--
Dave Hargis, Microsoft Access MVP


:

Dave
Unfortunetly, I am still having a problem after including the rst.edit

error is "Method or data member not found" at rst.edit?

My coding Now looks like

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF
rst.edit
'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92
rst.Update
'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity
rst.Update




rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing


End If

--

Dave thanks again and, if possible, when you get a chance is fine. I also
have an issue with an event, but I'll take that up in another post
Thanks for any assistance


:

Please do. If you have more questions, post back.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Thankyou so much. I'll keep at it and, if you dont mind, i'll let you know
how I make out
--
Thanks for any assistance


:

There are a couple of problems. First, you have to use an .Edit statement
before you can change field values in a recordset. You need to add this line:
rst.Edit
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

These lines will do nothing:

Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

The new values you have added to the table are not yet in your form's
recordset, so they will not change. If the fields you are chaning are bound
to these controls, to get them to reflect the changed values, you have to
requery the form. Now, when you do, the form will go back to the first
record in the recordset. You can't keep it on the same record, but you can
immediately go back to it. To do that, you have to save the value of the
primary key field of your form recordset, then move back to it after the
requery using a FindFirst on the save primary key value.

But since you are looping through subform records, you need to wait until
all the records have been updated. You probably need to only requery the
subform, and if it is not a large number of records showing in the subform,
it may not be an issue for it to return to the current record.
--
Dave Hargis, Microsoft Access MVP


:

Dave

Dont laugh but here is my attempt

'Check if the Total sq ft of Products starting with 3 or 4 is over 100'
If Me.txtTotalSquareFeet >= 100 Then
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset

Conn.Open CurrentProject.Connection
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
'qryTotalSquareFeet is looking for "Order Number" that is being
referenced on frmOrder

rst.MoveFirst
Do While Not rst.EOF

'Make Seling Price per unit 1.92
rst!fldProductSellingPrice = rst!fldUnitsperPerProduct * 1.92

'Change Extended Price
rst!fldExtendedSellingPrice = rst!fldProductSellingPrice * rst!fldQuantity

'Requery txttax,txtxSubtotal,txtOrderTotal
Me![frmOrderDetail].Form!txtTax.Requery
Me![frmOrderDetail].Form!txtSubTotal.Requery
Me![frmOrderDetail].Form!txtOrderTotal.Requery

rst.Update

rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set Conn = Nothing
End If

I am getting a Run-time error(2147217900) Invalid SQL statement expected
delete,insert,procedure,select,update ???
I am getting it at
rst.Open "qryTotalSquareFeet", Conn, adOpenDynamic, adLockOptimistic
although I know by now that it may not be the statement actually causing
problem.

any thoughts on my approach and on error.

Thanks again

Joe

--
Thanks for any assistance


:

That would involve reading through the subform's recordset and changing the
value in VBA.
--
Dave Hargis, Microsoft Access MVP



:

Thanks, I'm using Dsum that gets kicked off after everyline. I quess I am
having trouble figuring out how to make the price changes in previously
entered lines with out doing it manually.
--
Thanks for any assistance


:

If you look at the sample Northwind database that ships with Access, there is
an order form that has a form/subform set up, as you probably do with order
header as the main form and order detail as a subform. It has an example of
how to keep a total of a field in the subform. Using that method, you could
total your square feet and check the total after every line entry and make
adjustments there. I am guessing it would probably be in the after upate
event of the control where you enter the square feet.
--
Dave Hargis, Microsoft Access MVP


:

I have an Order form with a subform for the Order Detail. In the Subform,
the Product ID, Quantity (Square Feet), etc is entered. Then the next item,
etc. Here's the clitch - When the total square feet is over 100, the pricing
changes for all the items(starting with 3 or 4), including the items entered
previously. The pricing, extended pricing has to be recalculated and I would
like it to happen automatically. I will be tracking the total square ft as it
is entered What I would like is some guidance in approaching this situation.
There are 2 primary table tblOrders, tblOrderDetail and tblProducts. Like I
said, I am not looking for specific coding help but help in the approach I
take.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top