Microsoft Access VBA code with Select SQL String and Where clause

Question

I'm using Microsoft Access to develop a database app. An important feature the user would need is to automatically send an email update to all relevant stakeholders.

The problem is that I'm getting

Run-time error '3075' Syntax error in query expression.

Here it is below:

Set rs = db.OpenRecordset("SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
" FROM StakeholderRegister " & _
" WHERE (((StakeholderRegister.[ProjectID]=[Forms]![ChangeLog]![cboProjectID.Value])) ;")

Funny thing is that I created a query table on Access to create the relevant recordset and the turned on SQL view to copy the exact sql string that's above. That query works however it opens an Input Parameter box, whereas this code should be using the value typed into a forms text box as a matching criteria.

Full detailed answer here: https://stackoverflow.com/a/49509616/78522 — iDevlop, Jan 25 '21 at 10:13

braX · Answer 1 · 2021-01-25T11:23:37.507

1

To use a variable as a parameter, do not include it within the quotes:

" WHERE StakeholderRegister.[ProjectID]=" & [Forms]![ChangeLog]![cboProjectID].[Value]

or just

" WHERE StakeholderRegister.ProjectID=" & Forms!ChangeLog!cboProjectID.Value

_{Note: You really only need the square brackets when there is something like a space in the name, which is not the best practice anyway.}

_{I also took the liberty to remove the parentheses, as they are not needed in such a simple WHERE clause, and can cause more trouble than they are worth.}

edited Jan 25 '21 at 11:23

answered Jan 25 '21 at 10:12

braX

11,506
5
20
33

syntaxically perfect, but open to injection, and will break in some cases. See the link in comment of OP – iDevlop Jan 25 '21 at 10:15
@PatrickHonorez: You can "inject" a _Null_ in a combobox configered properly, that's all. Also, the only way this can (and will) fail is if the form is closed. – Gustav Jan 25 '21 at 10:35

score 0 · Accepted Answer · answered Jan 25 '21 at 12:07

Try,

    Dim strSQL As String
    strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
    " FROM StakeholderRegister " & _
    " WHERE StakeholderRegister.[ProjectID]=" & [Forms]![ChangeLog]![cboProjectID].Value & " ;"
Set rs = Db.OpenRecordset(strSQL)

if [ProjectID] field type is text then

    Dim strSQL As String
    strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
    " FROM StakeholderRegister " & _
    " WHERE StakeholderRegister.[ProjectID]='" & [Forms]![ChangeLog]![cboProjectID].Value & "' ;"
Set rs = Db.OpenRecordset(strSQL)

BraX response was also helpful – Lwazi Mace Jan 25 '21 at 13:03 — Lwazi Mace, Jan 25 '21 at 13:03

Microsoft Access VBA code with Select SQL String and Where clause

2 Answers2