CODEWITHSUNDEEP
sqlvbams-access
Fabian Julian
Fabian Julian

Reputation: 17

Input variable in Where clause for SQL "Select Statement" - Access, VBA

Need help for the below listed issues.

  1. I am getting "YearN" as user input to the select statement to Run a Query in VBA for Access Database. The statement works when a number directly entered in where clause say "2027". Not sure how to reference a input variable/object. Please help.
  2. Need help to refresh the record as I am getting runtime error whenever the code trying to execute line "A.open strconnection" saying "The database has been placed in a state by user 'Admin' on machine that prevents it from being opened or locked". Please advise
Dim YearNumber As Long
DoCmd.RefreshRecord
YearN = InputBox("Enter the Record Year to delete:")
    
If YearN = "" Then
MsgBox "Year not entered. Query exit"
Else
Dim A As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Set A = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\OneDrive - fab\Access_Db\file.Accdb"
strSql = "DELETE FROM APAC_tbl_test WHERE Year= YearN;"
DoCmd.SetWarnings False
A.Open strConnection
Set rs = A.Execute(strSql)
DoCmd.SetWarnings True
Set rs = Nothing
A.Close
Set A = Nothing
End If
End Sub```

Upvotes: 0

Views: 397

Answers (1)

Purple_BTS
Purple_BTS

Reputation: 1

If you are trying to have a "pop-up" box where the user inputs a value for the WHERE clause within Access, you don't necessarily need VBA code. Simply write your SQL statement and in the WHERE clause you'll do something like below:

DELETE FROM APAC_tbl_test WHERE Year = [Enter Year:]

Once you run the above code it should prompt user for the year with "pop-up" box that is labelled "Enter Year".

So basically,

WHERE [column name] = [custom prompt message:]

Important to keep the brackets [ ] and the semicolon :.

More information can be found at this link - https://support.microsoft.com/en-us/office/use-parameters-to-ask-for-input-when-running-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

Upvotes: 0

Related Questions