Run-time error 3075 with DLookup when row value contains an apostrophe

[rhewitt]

Title says it all. Anytime there is an apostrophe in the cell it throws error 3075.

Me.tboxScientific = DLookup("[ScientificName]", "Species", "[CommonName] = '" & Me.cboCommonName.Text & "'")

Should I include logic to add an escape character when the code encounters an apostrophe? I'm new to VBA, but I understand the concept in other languages.

I tried changing the code to the following (wrapped the final expression in quotes instead of apostrophes [' " '] instead of [" ' "], which works in Python but not in this VBA case I guess -- syntax error):

Me.tboxScientific = DLookup("[ScientificName]", "Species", '[CommonName] = "' & Me.cboCommonName.Text & '"')

[JoeM]

I would do this a little differently.
It looks like you have some table named "Species" that list the Common Name and Scientific Name fields.
On your Form, it looks like you have a Combo Box named "cboCommonName". I am guessing that the values from this Combo Box come from your Species table.
If that is the case, there is no need to use DLOOKUP. Simply set up your Combo Box to show both fields (Common Name and Scientific Name), and then use the AfterUpdate Event on the Combo Box to populate tboxScientific with the value from the second column of your Combo Box selection, i.e.

Private Sub cboCommonName_AfterUpdate()
    Me.tboxScientific = Me.cboCommonName.Column(1)
End Sub

Edit: The first column in the Combo Box is given an oridinal of 0, then next 1, etc.

[rhewitt]

This is excellent.

All of my database experience is in GIS and spatial databases, not in access. I've been trying to hack together a solution to synchronize two separate combo boxes, which is where the text boxes came from in the first place.

How do I get the combo box to show both fields? Is it a concatenation in the combobox's RowSource SQL?

[JoeM]

If you use the Combo Box Wizard, you can select both fields when going through the Wizard.
Or, if updating it manually, update the SQL code in the "Row Source" property. Just be sure to also update the "Column Count" property to 2.

[rhewitt]

Thanks, this is exactly what I was looking for.

In order to access the values i just use the .Column() method?

[Missinglinq]

Yes, and the Columns are Zero-based, meaning the first Column is 0, the second Column is 1, etc.

I agree with Joe's idea about the Combobox, but FYI, to handle the Apostrophe in the DLookup Function (and elsewhere) you'd use something like this, using extra Quotation Marks:

Me.tboxScientific = DLookup("[ScientificName]", "Species", '[CommonName] = """ & Me.cboCommonName & """")

Notice that I also dropped the .Text from your code. There's no need for it, because the Default Property, which is .Value, will work, and being the Default Property, doesn't even need to be written out.

Linq ;0)>