How to Pass a parameter into the microsoft access query

Question

hello i have a query in ms access called AddNewProject that will insert a new row in a table is simply says :

insert into Projects ([ProjectName]) values (@projectName)

now my problem is : I need to execute this query and pass the parameter when I am executing it , I have tried this code : select AddNewProject as @projectName='FirstProject but that did not work. can you developers please help me with this

If you want this in a query object, Parameter must be either a user response to a popup input prompt or reference to a control on a form. Fairly certain `@projectName` means nothing in Access. Review https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/parameters-declaration-microsoft-access-sql and https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access — June7, Aug 28 '22 at 04:32
When you say 'need to execute this query', do you mean when you double-click or open it, or call it from VBA, or use it as a datasource in a form or report; please elaborate. @projectname does not indicate a parameter in MsAccess; unless you are using ADODB library and run this as a query text (not MsAccess saved query). — tinazmu, Aug 29 '22 at 04:16

score 1 · Answer 1 · answered Sep 10 '22 at 20:37

Salam Ali,

Let's assume:

You call it from VBA.
There is a table named "Projects" and it has field "ProjectName".

You can put this code on particular form module or on standard module. Here i put this code on standard module.

Public Function addNewProject(strProjectName As String) As Boolean
    Dim cSQL As String, cParam As String
    Dim tableName As String
    Dim db As DAO.Database
    Dim cQDf As DAO.QueryDef
    
    tableName = "Projects"
    cParam = "PARAMETERS [par_projectName] Text(255); "
    cSQL = "INSERT INTO " & tableName & "(ProjectName) " & _
           "VALUES (par_projectName);"
           
    On Error GoTo commit_failed
    Set db = CurrentDb
    Set cQDf = db.CreateQueryDef("", cSQL)
    
    With cQDf
    .Parameters("par_projectName") = strProjectName
    .Execute dbFailOnError
    .Close
    End With
    addNewProject = True: Exit Function
    
commit_failed:
    addNewProject = False
    
End Function

I make this function returns boolean value (True/False) just as confirmation if committing transaction into the db is success or fail.

Then you can call this function from any module (form/standard module):

Private Sub cmdSaveInput_Click()
    If addNewProject(txtProjectName) = True Then
        MsgBox "Data saved."
    Else
        MsgBox "Saving data failed."
    End If
    
End Sub

or from Immediate window (Ctrl+G):

?addnewproject("Watsan Facility")

Hope it helps.

How to Pass a parameter into the microsoft access query

1 Answers1