Query Filtered by Form not Pulling data - error 3061 (1 Viewer)

BTD

New member
Local time
Today, 04:49
Joined
Jun 26, 2021
Messages
9
Hello All,
Getting stumped by some vba and query work that I've nearly finished.

I've got a form that shows records from a table. This form behaves like a checklist and only shows "open orders." When those orders are complete the worker chooses a yes or a no option from a combo box, and that choice determines where an email is sent and what QUERY to run.
TRUE = Email is sent to in house users
FALSE = Email is sent to users outside the 'house, and the list of users will vary based on where they are. I have a table of contacts responsible for different areas and the form will autofill that area based on the material number I'm working with; i only want to send emails to the relevant users and not ALL of the out of house users.

my problem is this: Each record has a different set of out of house users associated with it and when i run the "PlantSLQuery" un filtered it populates the recipient column with the emails related to EVERY record in the table and not the one listed in the form. Also, when I add a filter to the query to only show things that are relevant to the form, the query shows the records correctly, but doesn't allow the VBA to function properly. I get a 3061 error whenever I run this VBA with a form filter on the PlantSLQuery, even though I can open the query itself and see the information.
Any help would be AWESOME. Bolded text is the line that throughs the error out.

Also worth noting, I have a second IF with identical code, but a different query running in bold. That query has a filter based on text criteria on it, and works perfectly fine.

Code snippet below:


If Me.TrueMispickCombo = "False" Then

Dim rs As Object
Dim I As Integer
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim strBody As String
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("PlantSLQuery")


'Email body message
Do While Not rs.EOF
Emailto = rs!

rs.MoveNext

.Recipients.Add Emailto
Loop

.Display
strBody = "Inventory investigation results for mispick involving " & Me.MatRecBox & " and " & Me.MatNeedBox & " are as follows:" & "<br>" & _
"<br><b>" & "Received: " & "</b>" & Me.MatRecBox & " - System: " & Me.NSystem & " Physical: " & Me.NActual & " Difference: " & Me.NDiff & _
"<br><b>" & "Needed: " & "</b>" & Me.MatNeedBox & " - System: " & Me.RSYS & " Physical: " & Me.RActual & " Difference: " & Me.RDIFF & _
"<br><b>" & "Counted By: " & "</b>" & Me.CountedBy & _
"<br><b>" & "Comments: " & "</b>" & Me.CommentsBox & "<b>" & _
"<br>" & strTableBody & "<br>" & .HTMLBody


Set rs = Nothing
Set rs = CurrentDb.OpenRecordset("MispickQueryEmail")


.Subject = "Mispick Investigation Results " & Me.MatRecBox & " and " & Me.MatNeedBox
.HTMLBody = strBody

End With
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
Try wrapping the form reference in the query in the Eval() function, like:

WHERE FieldName = Eval("Forms!FormName.TextboxName")
 

BTD

New member
Local time
Today, 04:49
Joined
Jun 26, 2021
Messages
9
This would be on the SQL view and not in the design view, correct?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
Correct. In design view:

Eval("Forms!FormName.TextboxName")
 

BTD

New member
Local time
Today, 04:49
Joined
Jun 26, 2021
Messages
9
Same Error.
Query works fine by itself
Put the Code in criteria:
1624733984979.png
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
You missed the quotes around the form reference.
 

BTD

New member
Local time
Today, 04:49
Joined
Jun 26, 2021
Messages
9
HOLY.... **sigh** I'm both annoyed that it was so simple, and EXTREMELY grateful it worked.

Thanks pbaldy!!


1624734246315.png
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:49
Joined
Aug 30, 2003
Messages
36,118
Happy to help and welcome to the site! There are other solutions but to me this is simplest.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:49
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

Speaking of other solutions, you might also keep this one in your back pocket.

 

BTD

New member
Local time
Today, 04:49
Joined
Jun 26, 2021
Messages
9
Thanks guys! Glad to be here! I'll definitely put these in the back of my mind!
 

Users who are viewing this thread

Top Bottom