-1

I am almost there:

The variable strCons contains a number and I would like to add this to the SQL. When I do this like below, I get this run-time error:

3061 Too few parameters. Expected 1.

When I replace strCons with a number it works. Tried to find out why with no luck.

Set rs = CurrentDb.OpenRecordset("SELECT Member.HomeEmail FROM Member INNER JOIN MemberRole ON Member.MemberID = MemberRole.MemberID WHERE (((MemberRole.AreaID)= strCons) AND ((MemberRole.RoleID)=1) AND ((MemberRole.FinishDate) Is Null)) ORDER BY Member.Surname")

strEmailTD = rs.Fields(0).Value rs.Close

Erik A
  • 31,639
  • 12
  • 42
  • 67
Rain Check
  • 35
  • 1
  • 8
  • 1
    You need to substitute the value of `strCons` into the query, not just its name; right now, it is just text that does not mean anything to the database. But then just substituting it is a bad idea because it will leave you vulnerable to SQL injection. So, you should use a parameterised query. – underscore_d Jul 12 '17 at 16:06
  • Possible duplicate of [Is it possible to pass parameters programmatically in a Microsoft Access update query?](https://stackoverflow.com/questions/16568461/is-it-possible-to-pass-parameters-programmatically-in-a-microsoft-access-update) – underscore_d Jul 12 '17 at 16:08

1 Answers1

0

You can use:

Set rs = CurrentDb.OpenRecordset("SELECT Member.HomeEmail FROM Member INNER JOIN MemberRole ON Member.MemberID = MemberRole.MemberID WHERE (((MemberRole.AreaID)=" & strCons & ") AND ((MemberRole.RoleID)=1) AND ((MemberRole.FinishDate) Is Null)) ORDER BY Member.Surname")
Gustav
  • 53,498
  • 7
  • 29
  • 55