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.

question from:https://stackoverflow.com/questions/65882634/microsoft-access-vba-code-with-select-sql-string-and-where-clause

Answer 1

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)