I have a function that takes 4 inputs and creates a record in a table (for accounting). I wanted to process a batch of records at once, based on the results in a query. So I made the query to give me the records I wanted to make accounting entries. I then made a second query that has a field with the function in it, that feeds the inputs off the first query. Everything seems to be normal,the query returns 5 records, the function runs 5 times and creates the records. Problem is, it creates SIX records. The first one is duplicated for some reason. There are 5 results from the query, but 6 records created. Here is the code of the function:
Here's the SQL of my first query (creates the initial recordset):
SELECT tblChecks.*, tblChecks.IDNo AS IDNo2
FROM tblChecks
WHERE (((tblChecks.SentToDMV)=0) AND ((tblChecks.TagStatus)="2"));
SQL for second query (runs the function)
SELECT AddRecord("tblARDMV",qryDMV2!IDNo2,tblchecks!checkamounttag,Date(),"Stage 2: etc") AS Expr1, qryDMV2.IDNo, tblChecks.CheckAmountTag
FROM tblChecks INNER JOIN qryDMV2 ON tblChecks.IDNo = qryDMV2.IDNo;
What is going on here? Why is it creating an extra record in 'tblARDMV'?
Thanks a lot!
Code:
Public Function AddRecord(tableopen As String, IDNum As Long, amnt As Currency, entdate As Date, descrip As String)
Dim dbnm As Database
Dim rstAccount As Recordset
Set dbnm = OpenDatabase("returnedchecks.mdb")
Set rstAccount = dbnm.OpenRecordset(tableopen)
With rstAccount
.AddNew
!IDNo = IDNum
!entrydate = entdate
!amount = amnt
!description = descrip
.Update
End With
AddRecord = "Yes"
End FunctionHere's the SQL of my first query (creates the initial recordset):
SELECT tblChecks.*, tblChecks.IDNo AS IDNo2
FROM tblChecks
WHERE (((tblChecks.SentToDMV)=0) AND ((tblChecks.TagStatus)="2"));
SQL for second query (runs the function)
SELECT AddRecord("tblARDMV",qryDMV2!IDNo2,tblchecks!checkamounttag,Date(),"Stage 2: etc") AS Expr1, qryDMV2.IDNo, tblChecks.CheckAmountTag
FROM tblChecks INNER JOIN qryDMV2 ON tblChecks.IDNo = qryDMV2.IDNo;
What is going on here? Why is it creating an extra record in 'tblARDMV'?
Thanks a lot!
Have to admit though, that 6 record thing was pretty weird...
