How to use DoCmd.RunSQL

Question

I'm sure this a simple fix, but I am brand new to VBA coding.

I am trying to build a MS Access database with a simple purpose. My goal is to have the user click the "Query _______" button, (where the blank part is either part number or stock number) then have them input their information in the boxes and select the aircraft platform. I have that part figured out, but I am having trouble code the actual query.

I have two tables named "MV-22" and "CH-53E". I have a main form where the user select the query they want, either by part number or stock number. Once they select one, it opens another form for either part number or stock number respectively. They then type the information they want to query and select the table (aircraft platform) from a drop down menu. From there, I'm trying to get that to cross to whatever table they selected, and pull the user input.

Hopefully that makes sense.. Here is the code I have to run the query. It has been saying "Compile error: Expected: List separator or )." Like i said im sure its a simple fix, I'm just new to all of this.. Thank you for all of the help!

Private Sub btnView_Click()

Dim PN As String
Dim TMS As String

PN = [frmSheetsPN]![txtbxPN]![Text]
TMS = [frmSheetsPN]![dropTMS]![Text]

DoCmd.RunSQL ("SELECT * FROM" '&TMS&' "WHERE PartNo LIKE" '&PN&')

End Sub

What is `frmSheetsPN`? If name of form, follow proper identifiers. See this [tutorial](http://access.mvps.org/access/forms/frm0031.htm). — Parfait, Nov 03 '20 at 17:56
Aside, not only are these ("MV-22" and "CH-53E") not recommended table names in a database due to special character of hyphen, they possibly should not be separate tables as they denote specific parts. Consider using a *single* parts table with indicator column containing "MV-22", "CH-53E", etc. values. — Parfait, Nov 03 '20 at 18:00
Hello, So I have consolidated everything into one table with an additional column to identify the aircraft. How do I need to tweak my SQL query now having only one table? Here is what I have right now: ```PN = frmSheetsPN.txtbxPN.Value TMS = frmSheetsPN.dropTMS.Value DoCmd.OpenQuery "SELECT * FROM [" & TMS & "] WHERE PartNo LIKE '%" & PN & "%'"``` — LoganThePan, Nov 03 '20 at 18:43

Parfait · Accepted Answer · 2020-11-03T23:28:24.933

Several issues emerge with your attempt:

Do not use DoCmd.RunSQL for queries that return a resultset. This method is reserved for action queries (INSERT, UPDATE, DELETE) that do not return any data. Instead, use a saved query and then call DoCmd.OpenQuery to open query to screen.
As commented, avoid separate tables for each data element with names carrying special characters like hyphens that require bracket or backtick escaping. Instead, store all related data into a single table with indicator columns.
For LIKE expressions, MS Access GUI (.EXE) uses asterisks wildcard *, not %. However, you can use ALIKE with %.
Take advantage of parameterization and avoid concatenating values. In MS Access GUI, SQL queries can directly reference form controls on opened forms.
Avoid SELECT * FROM and control number and order presentation of columns.

Altogether, consider below setup:

SQL (save as an Access saved query object)

SELECT Col1, Col2, Col3, ...
FROM myAircraftsTable
WHERE aircraft_type = Forms![frmSheetsPN]![dropTMS]
  AND PartNo ALIKE '%' & Forms![frmSheetsPN]![txtbxPN] & '%'

VBA (reference above saved query)

Private Sub btnView_Click()
   DoCmd.OpenQuery "mySavedQuery"
End Sub

How to use DoCmd.RunSQL

1 Answers1