Dlookup Alternative on SQL Table in Unbound Field in MS-Access Form 1

[drazeni]

I have an MS-Access database project (.ADP) linked to SQL Server (MSDE). Before migrating to SQL server, I had a Front-end/Back-end setup of this database.

I had a drop-down field to lookup a client_code (row source) from "client" table, and the control source gets stored in a table called "quotes" (quotes_client_code). I used Dlookup to find the customer name from client table, based on the previous quote_client_code selection, which puts the value in an unbound text field. My form's recordsource is set on quotes table.

This method does not work since I have imported my tables into SQL server and link to that data source.

Fairly new to SQL server but I believe it's time to migrate.

Any help appreciated.

Thanks.



"All is not as it seems"

 

[AlexCuse]

Can you please post the dlookup code you are using in Access? DLookup basically just performs a select query for you, so it should be easy enough to simulate.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[drazeni]

Hi AlexCuse, thanks for reply. The VBA code in the form is as follows:

Private Sub quote_acc_no_AfterUpdate()
  Let client_name = DLookup("[name]", "client","[quote_acc_no]=[client]![account]")
End Sub

"All is not as it seems"

 

[ESquared]

YOu should really be asking this question in the ADP forum.

It will be something like

client_name = CurrentProject.Connection.Execute("select [name] from client where quote_acc_no=" & replace(client!account, "'", "")).Recordset.Fields(0)

I don't remember the exact object names for getting access to the current project's connection, but you should be able to find it. And I'm not 100% sure abou the execute method, but I AM 100% sure that you can do this if you find the right objects and methods.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

Oh, and it's good programming practice to toss a .Value on the end, ala .Fields(0).Value ... good practice for VBScript or ASP...

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]