VB Select Statment Help needed. (1 Viewer)

[gold007eye]

Can someone tell me what I am missing or doing wrong here? I have been racking my brain trying to get this working.

When I try to run the code I get "Run Time Error '3061': Too few parameters. Expected 1."

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])=[AR Group]))")

 

[Alc]

If you paste this part directly into a query, does it run?

Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])=[AR Group]))

 

[pbaldy]

Is [AR Group] a field, or are you intending to prompt the user?

 

[gold007eye]

If you paste this part directly into a query, does it run?

Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])=[AR Group]))

Yes it does. I have to pass the "AR Group" parameter but then it works fine.

 

[gold007eye]

Is [AR Group] a field, or are you intending to prompt the user?

[AR Group] is a text field on the form that is auto populated with a value based on what the user selects in the [AR Code] combo box.

 

[Alc]

Would this work?

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])= [B]'" & [AR Group] & "'"[/B]))")

As the [AR group] value is a string, I added ' ' around it.

 

[gold007eye]

Would this work?

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])= [B]'" & [AR Group] & "'"[/B]))")

As the [AR group] value is a string, I added ' ' around it.

I tried that, but the "'" part right after the [AR Group] & was showing up in red so I tried this:

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])= '" & [AR Group] & "))")

I am now getting the attached (see image) error. On the good note the "7" you see in the error message is the correct value that was populated in the [AR Group] field.

Not sure what syntax is wrong. Is it something to do with using the () in VB?

 

[Alc]

You definitely need a ' on both sides of the value, so that's probably the sytax error it's referring to. Why it was being highlighted in red, I'm not sure?

I hate the extra parenthesis Access adds into queries. They always confuse me.

How about changing the 'where' clause part to the following, therby removing the parenthesis?

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] [B]WHERE [/B][B][Email List].To = True AND [Email List].[AR Code Group] = '" & [AR Group] & "'"[/B]

 

[gold007eye]

Tried that.

Run Time Error '13':

Type Mismatch

Maybe I can do this another way...

How could I do something like:

Set rsTo = CurrentDb.OpenRecordset([Query Name])

 

[Alc]

Run Time Error '13':

Type Mismatch

Is [Ar Group] a number or a string?
If it's not a string, try just

[B]AND [Email List].[AR Code Group] = " & [AR Group][/B]

 

[gold007eye]

Is [Ar Group] a number or a string?
If it's not a string, try just

[B]AND [Email List].[AR Code Group] = " & [AR Group][/B]

It depends [AR Group] is a "text box" and stores either numbers or letters.

Examples:

1
2
3
4
AO

No such luck with the new method either

 

[Alc]

It will treat the entered value as a string.

Immediately before the problem line of code, add

Msgbox [AR Group]

We may as well see if it's the value itself that's problematic.

You could then try hardcoding a value into the expression and seeing if it runs e.g.

AND [Email List].[AR Code Group] = 'ABC'

(assuming ABC is a valid entry)

 

[gold007eye]

When I did the MsgBox "7" was what came up which was correct.

I also was trying to hard code a value in before you had mentioned it, but keep getting Data Type Mismatch.

Is this anything to do with trying to pull data from 2 different tables?

I wish that access wasn't so finnicky

I created a query "Email Test" and set the criteria to what I am trying to get the VB code to do and it works fine. Here is the SQL code it generates.

SELECT [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group]
FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID]
WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])=[Forms]![Request Form]![AR Group]));

Why is it so much harder to get that to work in VB? lol

Can a recorset be set to look at a query instead of the method I have been trying the the VB code?

So something like?:

Set rs = CurrentDb.OpenRecordset("Query Name")

 

[Alc]

Yes. the sytax is along the lines of
Set RS = Db.OpenRecordset("qry_1", dbOpenSnapshot)

 

[pbaldy]

That will only work if the query does not contain a parameter, which yours does. You will hit this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

Alc's suggestion of concatenating the form reference into the SQL is what I would do.

 

[Alc]

So maybe I'm not as dumb as I look

 

[gold007eye]

Yes. the sytax is along the lines of
Set RS = Db.OpenRecordset("qry_1", dbOpenSnapshot)

Tried that, but am getting the parameter error "expected 1".

Arrggg

Looks like the other way is going to be the way I need to go since I have criteria.

Is it anything to do with trying to pull data from 2 tables? the E-mail Address is in 1 table and the "AR Group Code" is in another table.

It actually seems like it is the "WHERE" clause that is causing the problems.

 

[Alc]

I'd go back to trying to hardcode in a value.
Until you can get it to work with a value you know to be correct, there's little chance of fixing whatever the syntax problem is.

This throws up an error?

Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE [Email List].To = True AND [Email List].[AR Code Group] = '7'"

 

[gold007eye]

Yeah. I am having no luck with this at all

I have actually commented out all other code other than the following and no matter what I do I get Data Mismatch type errrors

Is it to do with table relationships?

Here is the exact code for this actual On Click Event:

Private Sub Email_Test_Click()
'---=== Send Mail Code - START ===---
'Dim mail
'Set mail = Nothing
' Send by connecting to port 25 of the SMTP server.
'Dim iMsg
'Dim iConf

    Dim strTo As String, rsTo As Recordset
'Dim strCC As String, rsCC As Recordset
'Dim strBCC As String, rsBCC As Recordset

'Dim Flds
'Dim strHTML

    Set rsTo = CurrentDb.OpenRecordset("Select * FROM [PERD Security Access] WHERE [EDS Net ID] ='zzpzdc'")

'Const cdoSendUsingPort = 2

'Set iMsg = CreateObject("CDO.Message")
'Set iConf = CreateObject("CDO.Configuration")

'Set rsTo = CurrentDb.OpenRecordset("Select [PERD Security Access].[Email Address], [Email List].To, [Email List].[AR Code Group] FROM [PERD Security Access] INNER JOIN [Email List] ON [PERD Security Access].[EDS Net ID] = [Email List].[EDS Net ID] WHERE ((([Email List].To)=True) AND (([Email List].[AR Code Group])= '" & [AR Group] & "))")

'---=== Handle rs strings & recordsets - START ===---
'With rsTo
'        .MoveFirst
'Do
'    strTo = strTo & ![Email Address] & ";"
'        .MoveNext
'Loop Until .EOF
'    strTo = Left(strTo, Len(strTo) - 1)
'        .Close
'End With
'---=== Handle rs strings & recordsets - END ===---

'Set Flds = iConf.Fields
' Set the CDOSYS configuration fields to use port 25 on the SMTP server.
'With Flds
'    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
    'ToDo: Enter name or IP address of remote SMTP server.
'    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "usahm204.amer.corp.eds.com"
'    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
'    .Update
'End With
' Build HTML for message body.
'strHTML = "<HTML>"
'strHTML = strHTML & "<HEAD>"
'strHTML = strHTML & "<BODY>"
'strHTML = strHTML & "This is an automated e-mail to let you know that <b><font color=#FF0000>" & [Name of Requestor] & "</b></font> from <b><font color=#FF0000>" & [Department of Requestor] & "</b></font> has submitted a new <b><font color=#FF0000>A/R " & [A/R Code] & "</b></font> request in PERD."
'strHTML = strHTML & "</BODY>"
'strHTML = strHTML & "</HTML>"

'==== A/R Code Group - 1 - START ====
'If [AR Group] = 1 Then
' Apply the settings to the message.
'With iMsg
'    Set .Configuration = iConf
'    .To = strTo 'ToDo: Enter a valid email address. "<Diane.Nastasia@examhub.exch.eds.com>;<M.Derivois@examhub.exch.eds.com>"
    '.Cc = "<Deborah.Davis@examhub.exch.eds.com>"
    '.Bcc = "<Jason.Boney@examhub.exch.eds.com>" '<Carlene.Vitello@examhub.exch.eds.com>,
'    .From = "PERD Request<Jason.Boney@eds.com>" 'ToDo: Enter a valid email address.
'    .Subject = "New A/R " & [A/R Code] & " Request - TEST"
'    .HTMLBody = strHTML
'    .Send
'End With
'==== A/R Code Group - 1 - END ====
'End If

' Clean up variables.
'Set iMsg = Nothing
'Set iConf = Nothing

Set rsTo = Nothing

'Set Flds = Nothing

'---=== Send Mail Code - END ===---

'MsgBox "Your request has been submitted!" & vbCrLf & vbCrLf & "Your request will be completed shortly" & vbCrLf & vbCrLf & "-Database Admin", vbInformation, "Request Submitted"
MsgBox "Test Complete"
End Sub

 

[pbaldy]

Can you post a sample db?