Increase Table Record With VB

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi. I have a table and a query. I'm using some values from
the query name to increase values in the table. My code
looks like this:

Dim db As DAO.Database
Dim rsISDPulmonary As DAO.Recordset
Dim rsShippedToPlan As DAO.Recordset

Set db = CurrentDb

'This part of the code finds territories that need to have
their plans increased. This part is fine.
Set rsISDPulmonary = db.OpenRecordset("SELECT * FROM
[tblShippedTerritoryTotalPulmonary]" _
& " WHERE [Territory] = 'IS*'")

If Not rsISDPulmonary.EOF Then
Do While Not rsISDPulmonary.EOF

'This part restricts the table that holds the plan data to
those territories that are going to get an increase.
Set rsShippedToPlan = db.OpenRecordset("SELECT * FROM
tblShippedToPlan " _
& "WHERE Territory = '" & rsISDPulmonary!Territory & "'")

rsShippedToPlan.Edit

'This is where I'm having trouble. I'm trying to add
rsISDPulmonary![LBDDollarsShipped] to what is already in
the table's rsShippedToPlan![LBDDollarsShipped] field, but
I think I'm setting it to zero by mistake or something.
Any thoughts on how to do this? Thanks for any
suggestions!!

rsShippedToPlan![LBDDollarsShipped] = rsShippedToPlan!
[LBDDollarsShipped] + rsISDPulmonary![LBDDollarsShipped]

rsShippedToPlan.Update

rsISDPulmonary.MoveNext

Loop

End If
 
Looks OK.......Try the following (untested

'@@@ = lines I added or change

'---------------------------------------------------
Dim db As DAO.Databas
Dim rsISDPulmonary As DAO.Recordse
Dim rsShippedToPlan As DAO.Recordse

Set db = CurrentD

Set rsISDPulmonary = db.OpenRecordset("SELECT * FROM [tblShippedTerritoryTotalPulmonary] WHERE [Territory] = 'IS*'"
If Not rsISDPulmonary.BOF AND Not rsISDPulmonary.EOF Then '@@
rsISDPulmonary.MoveFirst '@@
Do While Not rsISDPulmonary.EOF '@@

'This part restricts the table that holds the plan data to
'those territories that are going to get an increase

Set rsShippedToPlan = db.OpenRecordset("SELECT * FROM tblShippedToPlan WHERE Territory = '" & rsISDPulmonary!Territory & "'"

If Not rsShippedToPlan.BOF AND Not rsShippedToPlan.EOF Then '@@@
rsShippedToPlan.Edi

'This is where I'm having trouble. I'm trying to add rsISDPulmonary![LBDDollarsShipped] to what is already in
'the table's rsShippedToPlan![LBDDollarsShipped] field, but I think I'm setting it to zero by mistake or something.
'Any thoughts on how to do this? Thanks for any suggestions!

'Debugging - Remove or Comment out next 3 lines when update working OK '@@
MsgBox "Pulmonary$$ = " & NZ(rsISDPulmonary![LBDDollarsShipped],0) & vbcrlf '@@
& "ShippedToPlan$$ = " & NZ(rsShippedToPlan![LBDDollarsShipped],0) & vbcrlf & vbcrlf '@@
& "New Total$$ = " & NZ(rsShippedToPlan![LBDDollarsShipped],0) + NZ(rsISDPulmonary![LBDDollarsShipped],0) '@@

rsShippedToPlan![LBDDollarsShipped] = NZ(rsShippedToPlan![LBDDollarsShipped],0) + NZ(rsISDPulmonary![LBDDollarsShipped],0
rsShippedToPlan.Updat
rsISDPulmonary.MoveNex
End If '@@
rsShippedToPlan.Close '@@
Loo
rsISDPulmonary.Close '@@
End I
'---------------------------------------------

Stev
 
Steve, Thanks!! Worked on the first try. I'm not really
sure what the problem was. Was it that I wasn't moving to
the first record in rsISDPulmonary so there was nothing to
add to rsShippedToPlan?
-----Original Message-----
Looks OK.......Try the following (untested)

'@@@ = lines I added or changed

'----------------------------------------------------
Dim db As DAO.Database
Dim rsISDPulmonary As DAO.Recordset
Dim rsShippedToPlan As DAO.Recordset

Set db = CurrentDb

Set rsISDPulmonary = db.OpenRecordset("SELECT * FROM
Click to expand...
[tblShippedTerritoryTotalPulmonary] WHERE [Territory]
= 'IS*'")
If Not rsISDPulmonary.BOF AND Not rsISDPulmonary.EOF Then '@@@
Click to expand...
rsISDPulmonary.MoveFirst '@@@
Do While Not
Click to expand...
rsISDPulmonary.EOF '@@@
'This part restricts the table that holds the plan data to
'those territories that are going to get an increase.

Set rsShippedToPlan = db.OpenRecordset("SELECT *
Click to expand...
FROM tblShippedToPlan WHERE Territory = '" &
rsISDPulmonary!Territory & "'")
If Not rsShippedToPlan.BOF AND Not rsShippedToPlan.EOF Then '@@@
rsShippedToPlan.Edit

'This is where I'm having trouble. I'm trying to
Click to expand...
add rsISDPulmonary![LBDDollarsShipped] to what is already
in
'the table's rsShippedToPlan![LBDDollarsShipped]
Click to expand...
field, but I think I'm setting it to zero by mistake or
something.
'Any thoughts on how to do this? Thanks for any suggestions!!


'Debugging - Remove or Comment out next 3 lines when update working OK '@@@
MsgBox "Pulmonary$$ = " & NZ(rsISDPulmonary!
Click to expand...
[LBDDollarsShipped],0) & vbcrlf '@@@
& "ShippedToPlan$$ = " & NZ
Click to expand...
(rsShippedToPlan![LBDDollarsShipped],0) & vbcrlf &
vbcrlf '@@@
& "New Total$$ = " & NZ(rsShippedToPlan!
Click to expand...
[LBDDollarsShipped],0) + NZ(rsISDPulmonary!
[LBDDollarsShipped],0) '@@@
rsShippedToPlan![LBDDollarsShipped] = NZ
Click to expand...
(rsShippedToPlan![LBDDollarsShipped],0) + NZ
(rsISDPulmonary![LBDDollarsShipped],0)
 
Wonderful

Without being able to step thru the code and watch the variables, it is really hard to say. The first change I made was to use the NZ() function. There might be some records that [LBDDollarsShipped] is null (in either recordset). If you add a null to a value, the result is null; maybe this is why it appeared that you were setting the $$ to zero??

I added the .BOF because when you open a recordset, you don't know where the pointer is located. It is better to check for both .BOF and .EOF; also why you have to use .MoveLast to get a record count

I added "If Not rsShippedToPlan.BOF AND Not rsShippedToPlan.EOF Then " so that you tried to add the $$ only if there was actually a record (that Null thing again)....

Glad it's workin

Stev
 
Back
Top