CODEWITHSUNDEEP
vbams-access
Lwazi Mace
Lwazi Mace

Reputation: 15

Microsoft Access VBA code with Select SQL String and Where clause

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.

Upvotes: 0

Views: 2750

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

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)

Upvotes: 0

braX
braX

Reputation: 11755

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.

Upvotes: 1

Related Questions