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

[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.

The code looks like this in VBA:

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

Any help appreciated.

Thanks.

All is not as it seems!

 

[Crowley16]

the proble is with your code, dlookup works fine on an ADP.

check your criteria parameter, are you sure you want to find a quote_acc_no called "[client]![account]"?

--------------------
Procrastinate Now!

 

[drazeni]

Account" is a field in the "Client" table. I worded it incorrectly as "client_code" in my original post. It is actually "account".

I was told it should look something like:

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

Thanks for the response.

All is not as it seems!

 

[Crowley16]

dlookup will work, I've just tested it and it worked fine, so why not stick with DLookup?

--------------------
Procrastinate Now!

 

[drazeni]

Did you try it in a project file (.ADP)? I was getting the following error: "Run-time error 2757--There was a problem accessing a property or method of the OLE object"

All is not as it seems!

 

[Crowley16]

yep, adp access 2003, no problems whatsoever

--------------------
Procrastinate Now!

 

[drazeni]

I'm using access 2000 (couldn't find my Access XP developer CD), you think this is the problem?

All is not as it seems!

 

[Crowley16]

I doubt that would be the problem.

if you really can't get that to work, then why not write your own function to lookup records with ado?

e.g. something like:

dim rs as new adodb.recordset
rs.open "SqlString", "ConnectionStringToYourServer"
msgbox rs.fields("fldName")

--------------------
Procrastinate Now!

 

[ESquared]

I'm the one that told you about the CurrentProject.Connection.Execute. And that method DOES work.

And yes, I forgot that dlookup works, too. Here's the problem, as Crowley16 already alluded to:

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

The part in red means nothing to SQL Server. Remember, this is going to be run ON the server because it is inside the double quotes. So you have to evaluate this on the client by putting it outside of the double quotes.

Here's how you fix it:

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

although you had better double up single quotes or remove them entirely ala

replace(client!account, "'", "''")

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