VBA code works most of the time

[debbiedoobie10]

When I run the code below in Access 2013 it works. When I run the code below in Access 2016 it works sometimes. When I step into the code and stop the code when the data is wrong. I went into the table and removed the wrong data then went back to the step into code and continue it works. Most of the time it works, there are only a few records that have wrong data, but I have not found a pattern. Does anyone have any suggestions?

CodeTPCash()
Function CodeTPCash()

Dim intDayCtr As Integer
Dim rstbltp64g As DAO.Recordset
Dim rstbltp66g As DAO.Recordset

Set MyDB = CurrentDb
Set rstbltp64g = MyDB.OpenRecordset("tblTP64G", dbOpenForwardOnly)
Set rstbltp66g = MyDB.OpenRecordset("tblTP66G", dbOpenForwardOnly)


With rstbltp66g
Do While Not .EOF
For intDayCtr = 0 To DateDiff("d", ![tBegbalDate], ![tDate]) - 1
DoCmd.OpenQuery "qryTP82G", , acReadOnly
qryTP81G
Field: mDate TotalCash
Table: TblTP64G TblTP64G
Total: Min group By
Criteria: is null

qryTP82G
Field: trxdate runningtotal Minofmdate
Table: tblTP31G tbltp31G qryTP81G
Total: Group By Sum Group by
Criteria: <=[minofmdate]



DoCmd.OpenQuery "qryTP84G", , acReadOnly
qryTP84G
Field: TotalCash
Table: TblTP64G
Update to: [tblTP83G]![sumofrunningtotal]


Next
.MoveNext
Loop
End With
DoCmd.OpenQuery "qryTP86G", acViewNormal, acEdit
rstbltp64g.Close
rstbltp66g.Close
Set rstbltp64g = Nothing
Set rstbltp66g = Nothing
End Function

 

[MarkK]

What does a block like this mean in the middle of a procedure???

qryTP81G
Field: mDate TotalCash
Table: TblTP64G TblTP64G
Total: Min group By
Criteria: is null

There is no way that code will run since it is syntactically incorrect in numerous places.

 

[Cronk]

You'd have to assume that commenting has been removed.

there are only a few records that have wrong data, but I have not found a pattern

It's your data and if invalid data, with or without a pattern, is raising errors in your code, then it seems blindingly obvious that you cleanse the data first.

 

[The_Doc_Man]

Without knowing more about your symptoms, it will be nearly impossible to diagnose this. Imagine going to a doctor and saying "I feel bad." And then shutting up completely. If you have good insurance, you will be poked and prodded and X-rayed and other stuff. But if you said, "I feel bad, I have a splinter in my toe" you would be immediately treated for a specific symptom and would probably be correctly treated in record-quick time.

When I run the code below in Access 2016 it works sometimes. When I step into the code and stop the code when the data is wron

How do you know it is wrong? What is it about the data that MAKES it wrong? Give us something to work with.

 

[static]

Function CodeTPCash()

	Dim intDayCtr As Integer

	With CurrentDb.OpenRecordset("tblTP66G", dbOpenForwardOnly)
		Do While Not .EOF
			For intDayCtr = 0 To DateDiff("d", ![tBegbalDate], ![tDate]) - 1
				DoCmd.OpenQuery "qryTP82G", , acReadOnly
				DoCmd.OpenQuery "qryTP84G", , acReadOnly '? update query ?
			Next
			.MoveNext
		Loop
		.close
	End With

	DoCmd.OpenQuery "qryTP86G", acViewNormal, acEdit

End Function

Code appears to just open/run queries multiple times without passing any parameters.

Please provide the SQL for the queries inside the loop.

 

[debbiedoobie10]

CodeTPCash()
Function CodeTPCash()

Dim intDayCtr As Integer
Dim rstbltp64g As DAO.Recordset
Dim rstbltp66g As DAO.Recordset

Set MyDB = CurrentDb
Set rstbltp64g = MyDB.OpenRecordset("tblTP64G", dbOpenForwardOnly)
Set rstbltp66g = MyDB.OpenRecordset("tblTP66G", dbOpenForwardOnly)


With rstbltp66g
Do While Not .EOF
For intDayCtr = 0 To DateDiff("d", ![tBegbalDate], ![tDate]) - 1
DoCmd.OpenQuery "qryTP82G", , acReadOnly
DoCmd.OpenQuery "qryTP84G", , acReadOnly
Next
.MoveNext
Loop
End With
DoCmd.OpenQuery "qryTP86G", acViewNormal, acEdit
rstbltp64g.Close
rstbltp66g.Close
Set rstbltp64g = Nothing
Set rstbltp66g = Nothing
End Function


Query SQL

qryTP81G
SELECT Min(tblTP64G.mDate) AS MinOfmDate
FROM tblTP64G
GROUP BY tblTP64G.TotalCash
HAVING (((tblTP64G.TotalCash) Is Null));

qryTP82G
SELECT tblTP31G.[trx Date], Sum(tblTP31G.Runningtotal) AS SumOfRunningtotal, qryTP81G.MinOfmDate INTO tblTP83G
FROM tblTP31G, qryTP81G
GROUP BY tblTP31G.[trx Date], qryTP81G.MinOfmDate
HAVING (((tblTP31G.[trx Date])<=[minofmdate]));

qryTP84G
UPDATE tblTP64G INNER JOIN tblTP83G ON tblTP64G.mDate = tblTP83G.MinOfmDate SET tblTP64G.TotalCash = [tblTP83G]![SumOfRunningtotal];

I have the code looping until the totalcash field in table tblTP64 is not null. It gets the data from table tblTP83G. Table tblTP83G is by date with amounts in sumofrunningtotal amounts. The query is to update the totalcash field with the correct amount with the same date. Most of the time it works, How I test the code is to step into the code and watch the amounts and when it was the wrong amount I would remove the incorrect amount from the table tblTP64 and rerun the code and it will input the correct amount.

Thank you for all your help

 

[The_Doc_Man]

How I test the code is to step into the code and watch the amounts and when it was the wrong amount I would remove the incorrect amount from the table tblTP64 and rerun the code and it will input the correct amount.

This says there is something wrong (like an embedded null as 1st guess) in the running sum. Since you are in essence single-stepping that Do While loop, it would pay you to note and closely examine the record or group that triggers the anomaly. This is the group that contains a value that gives a bad running sum. And my bet on having a NULL somewhere in that group is because math and nulls are the easiest (though not the only) way to screw up running sums. But I see that you are looking for something to not be a null. It just isn't clear here as to which field(s) you are testing for nullity.

I also note that either you have made a typo or omitted one of the entities here. We have no clue on qryTP86G. (Follows your "End With" in the code snippet.)