Solved Query to count records added to a table via append query (with warning messages turned off) (1 Viewer)

[Number11]

Is it possible to have confirmation of records added to a table from another table using Append query ( I have the warning messages turned off so dont get any messages about records added?)

 

[Minty]

You could use a dcount before running the query and afterwards to get the total record numbers and subtract one from the other to give you the number added?

 

[arnelgp]

if msgbox("Append record to table1?", vbQuestion+vbYesNo+vbDefaultButton2) = vbYes
docmd.OpenQuery "appendQuery"
end if

 

[Number11]

if msgbox("Append record to table1?", vbQuestion+vbYesNo+vbDefaultButton2) = vbYes
docmd.OpenQuery "appendQuery"
end if

Ok thanks good for the message prompts but no record count of how many records were transfered?

 

[Number11]

You could use a dcount before running the query and afterwards to get the total record numbers and subtract one from the other to give you the number added?

Ok so then i would run a query to count records and the a second query after importing but how would i then get the records and the 1st query is then lost?

 

[arnelgp]

if you are inserting record from another table, eg:

insert into table1 (field1) select field1 from table2 where <condition>; (yourAppendQuery)

create a Select query exactly same on the first query:

select field1 from table2 where <condition>; (name it qryFromTable2)

then you can dcount("1", "qryFromTable2") first and show how many will
be inserted:

lngCount = DCount("1", "qryFromTable2")

if msgbox("This will append " & lngCount & " record(s) from Table2 to table1." & vbcrlf & "Continue?", vbQuestion+vbYesNo+vbDefaultButton2) = vbYes
docmd.OpenQuery "yourAppendQuery"
end if

 

[Number11]

if you are inserting record from another table, eg:

insert into table1 (field1) select field1 from table2 where <condition>; (yourAppendQuery)

create a Select query exactly same on the first query:

select field1 from table2 where <condition>; (name it qryFromTable2)

then you can dcount("1", "qryFromTable2") first and show how many will
be inserted:

lngCount = DCount("1", "qryFromTable2")

if msgbox("This will append " & lngCount & " record(s) from Table2 to table1." & vbcrlf & "Continue?", vbQuestion+vbYesNo+vbDefaultButton2) = vbYes
docmd.OpenQuery "yourAppendQuery"
end if

ok thanks and then if say some records are not copied for formatting issues will it say then xx not copied?

 

[Minty]

Simply store the count values in a variable?

Dim lBeforeCount as Long
Dim lAfterCount  as Long


lBeforeCount = Dcount("1","YourTableYouAreGoingtoInsertInto")

DoCmd.OpenQuery "YourInsertQuery"

lAfterCount = Dcount("1","YourTableYouAreGoingtoInsertInto")

MsgBox "You Have Just Added " & lAfterCount - lBeforeCount & " Records"

 

[isladogs]

There is another approach.
Use the DAO Database.RecordsAffected property: Database.RecordsAffected property (DAO) | Microsoft Docs

For example, using a saved query:

Sub AppendRecords()
Dim db As DAO.Database

'Return reference to current database - this is required - CurrentDb won't work here
Set db = CurrentDb

'run the append query
db.Execute "qryYourAppendQuery", dbFailOnError
MsgBox db.RecordsAffected & " records have been appended to your destination table", vbInformation, "Append Query completed"
Set db = Nothing
End Sub

OR using a SQL statement:

Sub AppendRecordsSQL()
Dim db As DAO.Database, strSQL As String

'Return reference to current database - this is required - CurrentDb won't work here
Set db = CurrentDb

'define the SQL
strSQL = "INSERT INTO tblData ( StartDate, EndDate, NCheck )" & _
    " SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck" & _
    " FROM tblImport;"

'run the append statement
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & " records have been appended to table tblData", vbInformation, "Append Query completed"
Set db = Nothing
End Sub

NOTE:
1. This code won't work with DoCmd.RunSQL. You MUST use db.Execute. Warnings aren't shown (unless an error occurs)
2. This works with any action query (append/update/delete) or equivalent SQL statement.

 

[Number11]

Simply store the count values in a variable?

Dim lBeforeCount as Long
Dim lAfterCount  as Long


lBeforeCount = Dcount("1","YourTableYouAreGoingtoInsertInto")

DoCmd.OpenQuery "YourInsertQuery"

lAfterCount = Dcount("1","YourTableYouAreGoingtoInsertInto")

MsgBox "You Have Just Added " & lAfterCount - lBeforeCount & " Records"

thanks love this and its works.

 

[arnelgp]

ok thanks and then if say some records are not copied for formatting issues will it say then xx not copied?

you already received suggestions (on another thread on using RecordsAffected on querydef object?