Using text in an excel cell drop down list to open Microsoft access to a specific record using VB.

[cyburner]

Moderator Notice:
Copied by me to here
Re https://excelfox.com/forum/showthrea...ecord-using-VB
Alan

---

---

Hello,
I'm trying to get a daily menu spreadsheet synergizing with a recipe card database in MS access. (office 16)

The issue:
I have a dropdown menu in a cell in Excel. That dropdown list in Excel is generated from a table pulled in from a MS Access database "Recipe" table so I can click and change the daily menu item. When I click that dropdown cell with the current chosen context eg:"Alphabet Chilli" I want VB to use the text in that cell, to open the recipe card I've selected in MS Access form view. I'm trying to capture the text in that cell ("b8") ( also defined it as name "MondayMenu") as the filter criteria for MS Access using this line of code:

oApp.DoCmd.OpenForm "Recipes", , , "RecipeName=" & Range("b8")

That action generates this error:


Microsoft Visual Basic

Run-time error '3075':

Syntax error (missing operator) in query expression
'RecipieName=Alphabet Chilli',

If I use this line of code and manually tell it the text I want to query using:

oApp.DoCmd.OpenForm "Recipes", , , "RecipeName='Alphabet Chilli'"

Everything works. The issue seems to be related to how it's parsing the ' characters, or lack there of.

Suggestions? Please be kind , I'm new to this. XD

[cyburner]

I figured out the syntax error. The correct format of the line of code should read:

oApp.DoCmd.OpenForm "Recipes", , , "RecipeName='" & Range("b8") & "'"

The quotations and apostrophes weren't quite right.

Thank you!

[DocAElstein]

Thanks for lettings us know and sharing your answer.
I don't know anything about Access, but getting the quotations and apostrophes right in VBA coding generally is something I frequently spend a lot of my life doing, unfortunately ....
Alan