Append query to display result in popup message (1 Viewer)

lookforsmt · Oct 14, 2017

HI! arnelgp

Thanks for the detailed code and explanation.
I am trying to understand the code and replying to answer your questions.

' are you adding records to tbl_Entry_Date?
' if the above is true, does it has an
' AutoNumber field?

Yes i am adding only date field from 26_Master to tbl_Entry_Date.
There is AutoNumber field as DateId

Dim rsPrevIDs As DAO.Recordset
set rsPrevIDs = DbEngine(0)(0).OpenRecordset("SELECT ID FROM tbl_Entry_Date", dbOpenSnapshot)

Here i am using append query to add field "Entry_Date" from table "26_Master" to table "tbl_Entry_Date" mentioned as below.

Code:

INSERT INTO tbl_Entry_Date ( Entry_Date )
SELECT DISTINCTROW [26_Master].Entry_Date
FROM 26_Master;

''
' after executing your Query, create another
' recordset to compare the PreviousIDs:

Dim rsNewIDs As DAO.Recordset
set rsNewIDs = DbEngine(0)(0).OpenRecordset("SELECT ID, Entry_Date FROM tbl_Entry_Date", dbOpenSnapshot)

Here do i need to create this in append query as well or use the above query.

arnelgp · Oct 14, 2017

here is the complete test code:

Code:

Private Sub cmdAddDate5_Click()
On Error GoTo Park
	
	Dim qdef As DAO.QueryDef
	Dim strDates As String
	Dim rsPrevIDs As DAO.RecordSet
	Dim rsNewIDs As DAO.Recordset
	Dim strRecordsAffected As String

	If Me.Dirty Then Me.Dirty = False

	' save ID's before the update
	Set rsPrevIDs = DbEngine(0)(0).OpenRecordset("SELECT dateID FROM tbl_Entry_Date", dbOpenSnapshot)

	Set qdef = CurrentDb.QueryDefs("a_qry_addDate")
	qdef.Execute
	strRecordsAffected = Trim(qdef.RecordsAffected & "")

	Set qdef = Nothing

	' get new IDs after the update
	set rsNewIDs = DbEngine(0)(0).OpenRecordset("SELECT dateID, Entry_Date FROM tbl_Entry_Date", dbOpenSnapshot)

	With rsNewIDs
		.MoveFirst
		While Not .EOF()
			rsPrevIDs.Find "dateID=" & !dateID
			if rsPrevIDs.NoMatch Then
				strDates = strDates & !Entry_Date & vbCrLf
			End If
			.MoveNext
		Wend
		.Close
	End With
	rsPrevIDs.Close
	set rsPrevIDs=Nothing
	set rsNewIDs=Nothing

	strRecordsAffected = strRecordsAffected & " - date(s) added to tbl_Date"
	If strDates <> "" Then 
		strDates = Left(strDates, Len(strDates)-Len(vbCrLf))
		strRecordsAffected = strRecordsAffected & vbCrLf & vbCrLf & strDates
	End If

	Msgbox strRecordsAffected
	Exit Sub
Park:
	Msgbox Err.Number & ": " & Err.Description
	Set qdef = Nothing
End Sub

lookforsmt · Oct 14, 2017

Thank you so much for the code

i am getting an error on the below line code

rsPrevIDs.Find "dateID=" & !dateid

"Compile error" Member or data member not found

lookforsmt · Oct 14, 2017

if i exclude the code i am getting the pop message as " count as (2) - dates added to tbl_date" without the actual date in the popup message up

arnelgp · Oct 14, 2017

Replace the Find with FindFirst.

lookforsmt · Oct 14, 2017

yes, it has accepted the change but it still gives me below result, but without the actual date(s) in the popup message.

"(2) - dates added to tbl_date"

What i am looking is something like below:

(2) - dates added to tbl_date
13/10/2017
14/10/2017

Sorry for being a pain. I want to thank you very much for the help that you are giving.

arnelgp · Oct 14, 2017

What is DateId, a date fueld ir a number?

lookforsmt · Oct 14, 2017

table name: tbl_Entry_Date

Field1 DateId: AutoNumber
Filed2 Entry_Date: Date/Time (No duplicates)

table name: 26_Master

Field1 SrNos: AutoNumber
Field2 Entry_Date: Date/Time (Yes Duplicates ok)

arnelgp · Oct 14, 2017

Can you try this

Code:

Private Sub cmdAddDate5_Click()
On Error GoTo Park
	
	Dim qdef As DAO.QueryDef
	Dim strDates As String
	Dim rsPrevIDs As DAO.RecordSet
	Dim rsNewIDs As DAO.Recordset
	Dim strRecordsAffected As String

	If Me.Dirty Then Me.Dirty = False

	' save ID's before the update
	Set rsPrevIDs = DbEngine(0)(0).OpenRecordset("SELECT Entry_Date FROM tbl_Entry_Date", dbOpenSnapshot)

	Set qdef = CurrentDb.QueryDefs("a_qry_addDate")
	qdef.Execute
	strRecordsAffected = Trim(qdef.RecordsAffected & "")

	Set qdef = Nothing

	' get new IDs after the update
	set rsNewIDs = DbEngine(0)(0).OpenRecordset("SELECT Entry_Date FROM tbl_Entry_Date", dbOpenSnapshot)

	With rsNewIDs
		.MoveFirst
		While Not .EOF()
			rsPrevIDs.FindFirst "Entry_Date=" & !Entry_Date
			if rsPrevIDs.NoMatch Then
				strDates = strDates & !Entry_Date & vbCrLf
			End If
			.MoveNext
		Wend
		.Close
	End With
	rsPrevIDs.Close
	set rsPrevIDs=Nothing
	set rsNewIDs=Nothing

	strRecordsAffected = strRecordsAffected & " - date(s) added to tbl_Date"
	If strDates <> "" Then 
		strDates = Left(strDates, Len(strDates)-Len(vbCrLf))
		strRecordsAffected = strRecordsAffected & vbCrLf & vbCrLf & strDates
	End If

	Msgbox strRecordsAffected
	Exit Sub
Park:
	Msgbox Err.Number & ": " & Err.Description
	Set qdef = Nothing
End Sub

lookforsmt · Oct 14, 2017

Yes, It now gives me the popup message with 2 dates added to table with all the dates available in the table, tbl_Entry_Date

It should give only the new added dates. Will this be possible?

arnelgp · Oct 14, 2017

Change FindFirst:

rsPrevIDs.FindFirst "Entry_Date=#" & Format(!Entry_Date, "mm/dd/ yyyy") & "#"

lookforsmt · Oct 14, 2017

i have done the change and it gives me the popup message without the dates

"(2) - dates added to tbl_date"

arnelgp · Oct 15, 2017

Show me your insert query and we'll start from there.

arnelgp · Oct 15, 2017

try to create and save this query (qryDatesInserted).
run the query.

P1 is Entry_Date from your 26_Master
P2 is Entry_Date from tbl_Entry_Date

If P2 is blank, then P1 is the date
that will be inserted in tbl_Entry_Date.

SELECT DISTINCT T1.Entry_Date AS P1, T2.Entry_Date AS P2 FROM (SELECT DISTINCTROW *
FROM 26_Master) AS T1 LEFT JOIN tbl_Entry_Date AS T2 ON T2.Endtry_Date=T1.Entry_Date WHERE (T2.Entry_Date IS NULL)

lookforsmt · Oct 15, 2017

Hi!
I was not near my laptop for a while, sorry for that.

as instructed i have created query, "qryDatesInserted" below is the sql

Code:

SELECT DISTINCT [26_Master].Entry_Date AS P1, tbl_Entry_Date.Entry_Date AS P2
FROM tbl_Entry_Date RIGHT JOIN 26_Master ON tbl_Entry_Date.Entry_Date = [26_Master].Entry_Date
WHERE (((tbl_Entry_Date.Entry_Date) Is Null));

and i am getting the below result

P1
13/10/2017
14/10/2017

P2
Nil

arnelgp · Oct 16, 2017

then you have a solution!

Code:

Private Sub cmdAddDate5_Click()
On Error Goto Park

	Dim qdef As DAO.Querydef
	Dim strDates As String
	Dim rsDatesInserted As DAO.Recordset
	Dim db As DAO.Database
	Dim strRecordsAffected As String

	Set db = CurrentDb
	IF Me.Dirty Then Me.Dirty = False

	set rsDatesInserted = db.QueryDefs("qryDatesInserted").OpenRecordSet(dbOpenSnapshot)
	With rsDatesInserted
		If Not (.BOF And .EOF) Then .MoveFirst
		While Not .EOF
			strRecordsAffected = strRecordsAffected & !P1 & vbCrLf
			.MoveNext
		Wend
		.Close
	End With
	Set rsDatesInserted =  Nothing
	Set qdef = db.QueryDefs("a_qry_addDate")
	qdef.Execute
	strRecordsAffected = "(" & Trim(qdef.RecordsAffected) & ") - date(s) added to tbl_Date" & _
		IIF(Len(strRecordsAffected) > 0, vbCrLf, "") & _
		strRecordsAffected
	Set qdef = Nothing
	Set db = Nothing
	MsgBox strRecordsAffected
	Exit Sub
Park:
	Set qdef = Nothing
	Set db = Nothing
	MsgBox Err.Number & ": " & Err.Description
End Sub

lookforsmt · Oct 16, 2017

Thank you very much Sir arnelgp. Its been so kind of you to help me get this solved. I am indebted to you.

I have attached the snap shot of the result which i wanted along with db. The dates added are displayed in the popup message.

It gives me so much of joy. I once again want to thank you from the bottom of my heart.

i will mark this as solved for everyone if they wish to have.

arnelgp · Oct 16, 2017

I am happy tha we finally did it. Have a nice day.

lookforsmt · Oct 16, 2017

Yes, more than we, its you who have done it for me. Thanks and god bless you
Thanks and have a nice day

Append query to display result in popup message (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread