VBA message box

[Homegrownandy]

I’m using an append query on my form which only works when the same "name" exists in two tables.


It’s for a linked table to the outlook calendar and an appointments table used to manage these appointments.

The query looks for a match in the name, and runs a macro for whatever matches.
If something has been changed on the calendar then these names won’t match so I want some VBA to check if the name does exist and then pop up a message box when it doesn’t.

In the database the tabes and fields are:

Calendar - subject
tblappointment - appt

a screenshot of the first query in the macro:



I'm not sure how to reference query results in VBA so if someone could help me that would be great.

[orange]

I do not use macros, and I do not use/have Outlook on my PC.

To reference query results in vba use a recordset.

In overview:

Dim a recordset, and a string to represent the query name or SQL
Open the recordset
Set up a loop to process all records returned by the query

sample code----------------------------------

'---------------------------------------------------------------------------------------
' Procedure : sampleRSWithQuery
' Author    : mellon
' Date      : 29/03/2016
' Purpose   : sample vba to show processing a select result as a recordset
' and print some output to the immediate window.
'---------------------------------------------------------------------------------------
'
Sub sampleRSWithQuery()

    Dim rs As DAO.Recordset   'recordset
    Dim db As DAO.Database   'database
    Dim sSQL As String            'query sql
    Dim iCnt As Integer               'display a number to identify a line of print

    'this is the sql of query52
10  On Error GoTo sampleRSWithQuery_Error

20  sSQL = "SELECT Animal.AName, AnimalLocs.SightingDate, AnimalLocs.GPSLat, AnimalLocs.GPSLong " _
           & " FROM Animal INNER JOIN AnimalLocs ON Animal.AnimalId = AnimalLocs.AnimalId;"

30  Set db = CurrentDb  '
40  Set rs = db.OpenRecordset(sSQL)          'establish the recordset based on the query SQL
50  Do While Not rs.EOF                      'set up a loop to process all records in the recordset
60      iCnt = iCnt + 1                      'increment the line counter
70      Debug.Print iCnt & "  " & rs!AName & "   " & rs!SightingDate & "  " & rs!GPSLat   'do something ith the record
                                                                       ' --print a few fields to the immediate window
80      rs.MoveNext                               'move to the next record in the recordset
90  Loop
100 rs.Close                                  'close the recordset
110 Set rs = Nothing                          ' release the recordset

120 On Error GoTo 0
130 Exit Sub

sampleRSWithQuery_Error:

140 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure sampleRSWithQuery"
End Sub

using these tables and query
table Animal

AnimalId	AName
1	Spot
2	Jim
3	Sam
4	David
5	BlueEyes
6	Capitan
7	Johnny

table

animalLocs

SightingDate	GPSLong	GPSLat	CaptureDateX	AnimalId
27/03/2009	77.9	47.3	21/02/2008	1
28/03/2009	77.34	46.8	21/02/2008	1
02/12/2009	78.44	45.32	21/02/2008	1
13/03/2009	76.33	48.9	27/02/2008	2
29/03/2009	77.45	48.8	27/02/2008	2
21/04/2010	78.53	47.54	27/02/2008	2
24/08/2009	76.2	49.4	03/12/2008	3
21/04/2010	78.23	47.52	29/05/2008	4
23/04/2010	78.666	47.66	29/05/2008	4
14/09/2010	77.7	49.3	04/02/2008	5
17/09/2010	77.334456	48.9	04/02/2008	5

query:

sql

SELECT Animal.AName, AnimalLocs.SightingDate, AnimalLocs.GPSLat, AnimalLocs.GPSLong
FROM Animal INNER JOIN AnimalLocs ON Animal.AnimalId = AnimalLocs.AnimalId;

Data:

AName	SightingDate	GPSLat	GPSLong
Spot	27/03/2009	47.3	77.9
Spot	28/03/2009	46.8	77.34
Spot	02/12/2009	45.32	78.44
Jim	13/03/2009	48.9	76.33
Jim	29/03/2009	48.8	77.45
Jim	21/04/2010	47.54	78.53
Sam	24/08/2009	49.4	76.2
David	21/04/2010	47.52	78.23
David	23/04/2010	47.66	78.666
BlueEyes	14/09/2010	49.3	77.7
BlueEyes	17/09/2010	48.9	77.334456

[Homegrownandy]

Thanks for that, It will take me some time to get working but it looks like I can use that.