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

[Rico]

My DoctorAndPodiatristTB contains 3 fields, ResidentsName, AppointmentTime and DoctorOrPodiatrist. My DoctorAndPodiatristQ contains the same 3 fields.
The Doctors records will have a "D" in the DoctorOrPodiatrist field and the Podiatrist records will have a "P" in the DoctorOrPodiatrist field.



When I send my email: DoCmd.SendObject acSendQuery, "DoctorAndPodiatristQ", acFormatRTF, "rwshea220540@outlook.com", , , "Test", "Sending an email from VBA"

If I am sending the email to the doctor, I need code to change the DoctorAndPodiatristQ to select only the records with "D" in the DoctorOrPodiatrist field before I send it.
If I am sending the email to the Podiatrist, I need code to change the DoctorAndPodiatristQ to select only the records with "P" in the DoctorOrPodiatrist field before I send it.

Any help would be greatly appreciated. Rico

[isladogs]

Use a parameter query where you enter the DoctorOrPodiatrist field value at runtime

See Use parameters to ask for input when running a query (microsoft.com)

[Welshgasman]

You could also use a tempvar as criteria and set it to the required value before sending?

[Rico]

Thanks isladogs, that's really interesting and I will store that info away for future use, but I am already in the part of my program where I know whether it is "D" or "P", so I don't need to ask the user.

Thanks Welshgasman, a tempvar seems the answer to my problems. could either of you give me some simple code I can use?

[isladogs]

In that case use an If...Else..End If block

If Me.DoctorOrPodiarist ="D" Then
   'run query filtered to doctors only
Else
    'run query filtered to podiatrists only
End If

[Welshgasman]

Mine would just be

Tempvars("ReportType")= Me.DoctorOrPodiarist
Run your report

[Rico]

Isladogs and Welshgasman: Not really what I wanted, I was hoping for some code something like:
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("DoctorAndPodiatristQ")
and then somehow code to get in to make the DoctorOrPodiatrist parameter D or P
I don't want to make multiple query's because I will also want to change AppointmentTime as well.

[Welshgasman]

You do not create multiple queries with my method?

[Rico]

Welshgasman: I can't read your last code. I can see - (Mine would just be - Code and then there is something in the box but it is unreadable.
I'm new to this forums so I could be not viewing it properly. I have been doing some work on it, am I on the right track?
''My DocAndPodTB contains 3 rows, ResidentsName, AppointmentTime and DorP. My DocAndPodEmailQ contains the same 3 rows.
Dim mydb As DAO.Database
Set mydb = CurrentDb()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("DocAndPodEmailQ")
With qdf
.Parameters("DorP") = "D"
.Execute
End With
qdf.Close
Set qdf = Nothing
DoCmd.SendObject acSendQuery, "DocAndPodEmailQ", , "rwshea220540@outlook.com", , , "Test", "Sending Doctors email (with table) from VBA"

[isladogs]

Nor would my approach need multiple queries.
In fact your code appears to use a parameter as I originally suggested

[Rico]

Well you blokes in the UK are up early, with the high temperatures we have been hearing about, you obviously don't have airconditioning in your bedrooms.
The code I am working with gives me - Item not found in collection, on the line .parameters("DorP") = "D"

[Welshgasman]

Have you declared the parameter in the query?

[Rico]

Welshgasman: sorry, but you are talking to a novice here, what do you mean by - Have you declared the parameter in the query?

[Welshgasman]

I am not too hot on Parameters.
I did get a query working with just using the one parameter and using the index number.

However there is a Paremeters button that allows you to specify not only the parameter, but it's type.

Then the sql looks like this

PARAMETERS CMS Long, CMSDATE DateTime;
SELECT Transactions.*, Transactions.Description, Transactions.TransactionDate
FROM Transactions
WHERE (((Transactions.Description) Like "*" & [CMS] & "*") AND ((Transactions.TransactionDate)=[CMSDATE]));

So without that statement, I am not sure Access would know it had parameters?

See if this helps at all https://docs.microsoft.com/en-us/off...collection-dao

As I mentioned before, I have just used TempVars as criteria. Much easier it appears now.

[ssanfu]

@Rico,
Maybe you would post a copy of your dB with around 3 Doctors and 3 Podiatrist in the table for analysis? Change the names to protect the guilty...

It sounds like you have 1 table and 2 queries? ("DocAndPodEmailQ" and "DoctorAndPodiatristQ")
Not sure how you are running the command "DoCmd.SendObject".