1

I have a button that should take the "address" field from the current record, and add it to the TAddressCorrectionExclusions table, which consists of just ID (autonumber) and Address. However when I run the below code, it prompts for a value for tempaddress. How do I store the current record's 'address' field as tempaddress?

Dim SQL As String
Dim TempAddress As String
TempAddress = Me.Address ' This is the part that's not working
SQL = "INSERT INTO TAddressCorrectionExclusions (Address) VALUES (TempAddress)"
DoCmd.RunSQL SQL
Robert Patrician
  • 302
  • 3
  • 13
  • 1
    Concatenate variable input. `INSERT INTO TAddressCorrectionExclusions(Address) VALUES('" & TempAddress & "')"`. Review https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 Sep 15 '20 at 18:42
  • That's it, thank you! Now I just need to figure out why that works. – Robert Patrician Sep 15 '20 at 18:51

1 Answers1

2

Because TempAddress is not a named field in any referenced context, the query engine prompts you for such a value (which is really an error if not using MS Access GUI). Notice how you use TempAddress like other identifiers: TAddressCorrectionExclusions and Address.

Remember SQL is a separate process from VBA and does not know anything about its application layer. This includes SQL with other application layers: Java, Python, PHP, C#, etc.

INSERT INTO TAddressCorrectionExclusions (Address) VALUES (TempAddress)

What you really intended to do is pass the underlying value of VBA variable, TempAddress, into SQL query. Therefore, the usual answer is to concatenate the VBA variables into SQL query, wrapping string values in quotes.

SQL = "INSERT INTO TAddressCorrectionExclusions (Address) VALUES ('" & TempAddress & "')"

However, an even better solution is to use the form control directly in a stored query that recognizes the value as a parameter if form is opened. This effectively separates SQL from VBA for readability and maintainability.

SQL (save as a stored Access query; adjust 'myform' name)

INSERT INTO TAddressCorrectionExclusions (Address) VALUES (Forms!myform!Address)

VBA (single one-line command; action queries do not need to be closed)

DoCmd.OpenQuery "mySavedAppendQuery"
Parfait
  • 104,375
  • 17
  • 94
  • 125