I need to change CRITERIA in a Query when the program is running.

[CarlettoFed]

You must modify the Sql predicate of the DoctorAndPodiatristQ query, without the need for any parameter declaration, as follows

SELECT ResidentsName, AppointmentTime, DoctorOrPodiatrist FROM DoctorAndPodiatristTB WHERE DoctorOrPodiatrist = [DoctorOrPodiatristParameter]

and leave the line of code for sending the email unchanged

DoCmd.SendObject acSendQuery, "DoctorAndPodiatristQ", acFormatRTF, "rwshea220540@outlook.com", , , "Test", "Sending an email from VBA"

[Rico]

I have given up on trying to alter a query's parameter in code, so I will do it the long way with the code below.

''My DocAndPodTB contains 6 rows. ID, AppointmentDate, DorP, BNumbAndGender, AppointmentTime,Selected.
''My DocAndPodEmailQ contains 4 rows. SurnameSpaceGiven(from RintheVTB), AppointmentTime(Ascending), DorP, AppointmentDate

DoCmd.SetWarnings False
DoCmd.OpenQuery "TempDocAndPodEmailDeleteQ", acNormal, acEdit
DoCmd.SetWarnings True

Set InRS = mydb.OpenRecordset("Select * from DocAndPodEmailQ where DorP = '" & DocAndPodCurrentDorP & "' and AppointmentDate = '" & DocAndPodCurrentDate & "'")

Do Until InRS.EOF
Set OutRS = mydb.OpenRecordset("SELECT * FROM TempDocAndPodEmailTB")
OutRS.AddNew
OutRS!SurnameSpaceGiven = InRS!SurnameSpaceGiven
OutRS!AppointmentDate = InRS!AppointmentDate
OutRS!AppointmentTime = InRS!AppointmentTime
OutRS.Update
OutRS.Close
Set OutRS = Nothing
InRS.MoveNext
Loop
InRS.Close
Set InRS = Nothing
DoCmd.SendObject acSendTable, "TempDocAndPodEmailTB", acFormatRTF, "rwshea220540@outlook.com", , , "Appointment times list from RAAFA.", "Appointment names and times for " & CurrentDateOfEvent

Thanks for all the assistance I got from the members, when I get the time i will revisit this problem. Rico

[Welshgasman]

Wow, setting two tempvars would have been so much simpler.

[ssanfu]

Just a few of comments:

From Post #17

I have given up on trying to alter a query's parameter in code, so I will do it the long way with the code below.

''My DocAndPodTB contains 6 rows. ID, AppointmentDate, DorP, BNumbAndGender, AppointmentTime,Selected.
''My DocAndPodEmailQ contains 4 rows. SurnameSpaceGiven(from RintheVTB), AppointmentTime(Ascending), DorP, AppointmentDate

No, they are FIELDS.

I have given up on trying to alter a query's parameter in code, so I will do it the long way with the code below.

You don't need to edit the query parameters. All you need is is to have the query reference two unbound text boxes on a form. (or 2 unbound combo boxes)

DoCmd.SetWarnings False
DoCmd.OpenQuery "TempDocAndPodEmailDeleteQ", acNormal, acEdit
DoCmd.SetWarnings True

The above 3 lines can be replaced with one line of code.

mydb.Execute "TempDocAndPodEmailDeleteQ", dbFailOnError

Also, you didn't close OutRS, didn't set it to Nothing and didn't set mydb to Nothing.




If you posted your dB, it would be easy to show you how to make the changes. Change sensitive info (names and SSN) - need just a few records .... 5 to 10.

[Rico]

ssanfu, I will start using the: mydb.Execute "TempDocAndPodEmailDeleteQ", dbFailOnError in future.
I DID close OutRS, you must have missed it, and I didn't know I had to set mydb to Nothing.

carlettofed, welshgasman, Don't let anyone kid you that when you retire you have plenty of time on your hands, I will get back to you on your suggestion later.
Rico

[ssanfu]

@Rico,

Can't believe that I missed it.

In re-reading the code, I noticed that you open and close the OutRS record set for every appointment. If there are 5 doctors and 5 podiatrists, each with 8 appointments, you will be opening and closing the record set 40 times.
I rewrote the code the way I would structure it. Since you did not post the full sub, I had to guess at a couple of things.

Sub Test_Rico()
   Dim mydb As DAO.Database
   Dim InRS As DAO.Recordset
   Dim OutRS As DAO.Recordset
   Dim sSQL As String
   
    'Clear the Temp table
   mydb.Execute "TempDocAndPodEmailDeleteQ", dbFailOnError

   sSQL = "SELECT *"
   sSQL = sSQL & " FROM DocAndPodEmailQ"
   sSQL = sSQL & " WHERE DorP = '" & DocAndPodCurrentDorP & "' AND AppointmentDate = '" & DocAndPodCurrentDate & "'"    ' *** see below
   'Debug.Print sSQL

   'open record sets
   Set InRS = mydb.OpenRecordset(sSQL)

  'check for records
   If InRS.BOF And InRS.EOF Then
    MsgBox "No appointments found for " & DocAndPodCurrentDate
      InRS.Close
      Set InRS = Nothing
      Set mydb = Nothing
      Exit sub
   End If

   InRS.MoveLast
   InRS.MoveFirst

   Set OutRS = mydb.OpenRecordset("SELECT * FROM TempDocAndPodEmailTB")

   'populate temp table
   With OutRS
      Do Until InRS.EOF
         .AddNew
         !SurnameSpaceGiven = InRS!SurnameSpaceGiven
         !AppointmentDate = InRS!AppointmentDate
         !AppointmentTime = InRS!AppointmentTime
         .Update
         InRS.MoveNext
      Loop
   End With

   'send e-mail
   DoCmd.SendObject acSendTable, "TempDocAndPodEmailTB", acFormatRTF, "rwshea220540@outlook.com", , , "Appointment times list from RAAFA.", "Appointment names and times for " & CurrentDateOfEvent
   
 'close the record sets
   OutRS.Close
   InRS.Close

'destroy the variables
   Set OutRS = Nothing
   Set InRS = Nothing
   Set mydb = Nothing
End Sub

***
I am curious as to the data type of "DocAndPodCurrentDate". If it is a DateTime type, it should be delimited with hash marks (#).



Good luck with your project....

[Rico]

Thank ssanfu, there is some good stuff there, I will take on board what you have said.
Rico