DLookup in ODBC table (1 Viewer)

[Mirihika]

I set up an Access 2003 database where this code works OK:

Private Sub Street_Exit(Cancel As Integer)

Me.Suburb = DLookup("Suburb", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.StreetID = DLookup("ID", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Postcode = DLookup("Postcode", "Streets", "StreetName = Forms![Add A New Member]!Street")
Me.Town = DLookup("Town", "Streets", "StreetName = Forms![Add A New Member]!Street")

End Sub

I've now migrated the tables to a back-end in Azure using ODBC to connect. The linked Streets table in my list displays as dbo_Streets but when I change the table name in my code I get an error.

What am I missing please? Maybe a declaration?

 

[Galaxiom]

What happens if you concatenate it like this?

Me.Suburb = DLookup("Suburb", "dbo_Streets", "StreetName ='" & Forms![Add A New Member]!Street & "'")

 

[Galaxiom]

BTW

It can be easier to rename the linked table in Access rather then chase all the code changes required to match the new linked table name.

Beware in renaming tables though. If NameAutoCorrect is turned on (it is by default) the objects where they are referenced will also change to the new name.

Consequently don't rename the originals as a "backup". Delete the original table and then rename the newly linked table.

 

[Mirihika]

Thank you, that works well and I'd forgotten about spaces! I don't understand single quotes in DLookup criteria well and I've read conflicting advice about them in forums.

I decided to rename everything individually as the database will be multi-user and most won't understand Access settings so changing table names might cause more work than it saves.

A quick do's-and-don'ts on those single quotes would be appreciated. Am I right thinking they're only required if there are spaces in the enclosed string?

 

[Galaxiom]

The quotes are required for all strings, whether they have spaces or not.

Numbers are used without quotes. Dates require hash delimiters.

When you refer to an Access object (such as a control on a form) in the domain function argument, Access will translate the value into a string value with the correct delimiters.

The programmer must do handle this task when directly concatenating values into the string.