Append query to display result in popup message

[lookforsmt]

Hi!

I have 2 tables "26_Master" and "tbl Date_Entry" and 1 query "qry4"

After I run a query "qry4" which adds date in a new table "tbl_Date_Entry" from table "26_Master" there is a popup message which displays the number of items appended but it does not give the date value appended.

i am getting below popup message:

" 2 - 12:00:00 AM date added"

Below is what i am looking for"

"2 date added"
05/10/2017
06/10/2017

My code is as follows:

Private Sub cmdAddDate_Click()
  On Err GoTo Park
    
    Dim qdef As DAO.QueryDef
    Dim Entry_Date As Date

    Set qdef = CurrentDb.QueryDefs("qry4")
    qdef.Execute
    MsgBox qdef.RecordsAffected & " - " & Entry_Date & " " & "date added"

    Set qdef = Nothing

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

        If Me.Dirty Then
            Me.Dirty = False ' save the record
        End If
End Sub

any suggestions how do i get the date value in the popup message
Thanks

 

[arnelgp]

your query (qry4) has SQL property,
check this if you can get the dates
there:

dim strSQL As String

Set qdef = CurrentDB.Querydefs("qry4")
strSQL = qdef.SQL
Debug.Print strSQL
...
...

 

[lookforsmt]

i amended the code as mentioned there is no change. It gives exactly the same result: " 2 - 12:00:00 AM date added"

 

[Mark_]

Several things...
First, where would the dates "05/10/2017" and "06/10/2017" come from? In your code you define a date but you never assign a value to it.

Second, are you trying to show one date for EACH record you change? If so, msgbox is not going to work if you have more than a very small number of records because you would have to build up a display string appending line feeds.

Third, I would suggest adopting a good naming convention. "26_Master" and "qry4" leave much to be desired for understanding what they are and what they are used for.

 

[lookforsmt]

Thanks for the reply. my response as below
1) The dates would come from table "26_Master" when i run the append query "qry4" it would add the dates in new table "tbl_Entry_Date"
2) There would be 1 date at the max 2 dates added on daily basis to "tbl_Entry_Date" How would i build the display strings
3) table "26_Master is data with one off the field as Entry_Date".
query "qry4" is append query to add the Entry_Date to table "tbl_Entry_Date" which also has date field "Entry_Date"

 

[lookforsmt]

i guess i have to loop through to get the date but i am not sure how to write that code any help

 

[Gasman]

If you were to have a field in tbl_Entry_date, and populate it with the date they were appended in your query qry4, you could then retrieve the records with those dates with a recordset and build the string for the message box.?

 

[lookforsmt]

Thanks Gasman
but i have no clue how to write one. perhaps if you can guide me.

 

[Gasman]

Thanks Gasman
but i have no clue how to write one. perhaps if you can guide me.

Here is an extract from some code I use for emails. I get the data in the recordset and then populate the email properties with the recordset contents

You should be able to adapt it and build the MSGBox string

Please note I am only showing how it might be done, not recommending it should be done, but only you know your requirements.
You could just run a query after your qry4 to show what had been added. See pic. That tells you immediately what you appear to want to know?

HTH

    Dim db As Database
    Dim rs As DAO.Recordset, rsCW As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * from Lookups WHERE DataType = 'Email'")


    Do While Not rs.EOF
        ' Set flag and field to check
        blnSameClientType = True
        strClientType = rs!Client & rs!TranType
        strType = rs!TranType
    loop

 

[Mark_]

Thanks for the reply. my response as below
1) The dates would come from table "26_Master" when i run the append query "qry4" it would add the dates in new table "tbl_Entry_Date"

If the date is already in "26_Master", why copy the data into another table? Normally you would store the ID for your record in "26_Master" so you will always be pointing to the correct date.

I am guessing you are recording more than just a date then OR that you will be changing the date in "26_Master" as some point? Those are the only two reasons I can see to have the date stored elsewhere without referencing the original record.

 

[lookforsmt]

thanks Mark,
No i am only recording the date and i am not changing the date in 26_Master.

i just want to store the date as unique records in a separate table as dates in 26 Master are repeated.

 

[lookforsmt]

Hi! all

I am trying to get the below popup message

"2 date added"
05/10/2017
06/10/2017

i have tried all the suggestions in this thread and also searched through this forum for any possible solution. I am sure there will be one which i am unable to find. Hope anyone can help me through to resolve my problem.
Thanks

 

[gemma-the-husky]

why do you need these dates listing?

you could do a unique values query to identify the dates in the file, and display that, instead of trying to fill a list box

docmd.openquery "datesloaded"

I have a "isopen()" function to wait for the user to close an object, so

docmd.openquery "datesloaded"
while isopen("datesloaded")
    doevents
wend

Note that when running the append query you have two option, which are slightly different.

docmd.openquery "AppendQuery" will allow you to run the append, ignoring any failures
(if you setwarnings false, then you will not be notified of any failures. On the other hand, if you don't set it false, a user could cancel the append)

currentdb.execute "appendQuery" will allow you to cancel the append in the event of any errors.

Its' all a matter of taste/business rules as to which option works best.

 

[lookforsmt]

Thanks for the suggestion. I want to save dates in separate table since in future when the data get huge in table 26_Master, i did not want it to run slow and besides i would also know what date data i have downloaded in table 26_Master.

i was not trying to fill the list box. I was trying to create a message popup box of he date downloaded. Currently on the message popup it only shows the number of data downloaded in table 26_Master.

Can the function provided by you help me to get this

 

[lookforsmt]

HI! i have tried to do this using the vlookup and only getting the 1st date in the popup message. i have appended 2 new dates at one time so i should get 2 dates in the message box. I guess i need to use loop code. Any suggestions.

Below is what i get in the popup message
"2 date added"
05/10/2017

Below is the code:

Private Sub cmdAddDate5_Click()
  On Err GoTo Park
       
    DoCmd.SetWarnings False
    Dim qdef As DAO.QueryDef
    Dim Entry_Date As Date
    Dim strCopied As String
    Dim ErrDesc As Variant
    
    Entry_Date = format(date, "dd/mm/yyyy")
    strCopied = Entry_Date
    strCopied = vbNewLine & strCopied

    Set qdef = CurrentDb.QueryDefs("qry4")
    qdef.Execute

    ErrDesc = DLookup("Entry_Date", "tbl_Entry_Date")
    If Not IsNull(ErrDesc) Then
    MsgBox qdef.RecordsAffected & " - " & " " & "date added to tbl_Date" & vbNewLine & ErrDesc
    
    ErrDesc = DLookup("Entry_Date", "tbl_Entry_Date")
    
    Set qdef = Nothing

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

        If Me.Dirty Then
            Me.Dirty = False ' save the record
    End If
    End If
    
    Set qdef = Nothing
    DoCmd.SetWarnings True

End Sub

Thanks anyone can advice me how i can get both the dates. Is loop the right way and how can i do that?

 

[lookforsmt]

kindly read vlookup as dlookup

 

[Gasman]

I've already shown you a mechanism for getting those dates.

You would need to get each one and build your msgbox string.
So create the first line, and for each date you read, add it to that string with CR & LF and keep doing it until EOF

 

[lookforsmt]

hI Gasman
i have tried to do this in different way to get the query.def result in the message box. but i am getting the 1st date in the table Entry_Date

Private Sub cmdAddDate5_Click()
  On Err GoTo Park
       
    DoCmd.SetWarnings False
    Dim qdef As Dao.QueryDef
    Dim Entry_Date As Date
    Dim strCopied As String
    Dim ErrDesc As Variant
    
    Entry_Date = Format(Date, "dd/mm/yyyy")
    strCopied = Entry_Date
    strCopied = vbNewLine & strCopied

    Set qdef = CurrentDb.QueryDefs("a_qry_addDate")
    qdef.Execute

    ErrDesc = DLookup("Entry_Date", "tbl_Entry_Date")
    If Not IsNull(ErrDesc) Then
    MsgBox qdef.RecordsAffected & " - " & " " & "date added to tbl_Date" & vbNewLine & ErrDesc

    ErrDesc = DLookup("Entry_Date", "tbl_Entry_Date")

    Set qdef = Nothing

Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

        If Me.Dirty Then
            Me.Dirty = False ' save the record
    End If
    End If
    
    Set qdef = Nothing
    DoCmd.SetWarnings True

End Sub

if you can amend my code to display only the dates added to this table
many thanks

 

[arnelgp]

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

' if both the above are true, you can
' first save the Autonumber values
' to a recordset.

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

''
' 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)

''
' next compare them


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

''
' if strDates<> "" then we have the dates captured
If strDates<>"" Then strDates=Left$(strDates,LEN(strDates)-Len(vbCrLf))

' now you can do anything with strDates
' Msgbox strDates

 

[Gasman]

I do not think you can do it with DLookup as that just finds the first matching field.
You would need to store the ID of the first one you find and use that as criteria for the next DLookup, and that is relying on the fact that the IDs are in order.

If you insist on doing it this way with the MSGBOX then use the recordset method I pasted earlier.